FAQ
Hallo,
Is there any good solution to cache the results of SQL queries? I found the
great cache manager in dmtools (http://csl.anu.edu.au/ml/dm/), but I wonder
if there is any alternative. I will start with MySQL database, but it would
be great to be able to plug it into other RDBMS's. I am interested in both -
file and memory caching. I also know ZSQL Method in Zope.

Pawel Lewicki

Search Discussions

  • Gerhard Häring at Jun 19, 2002 at 7:13 am

    * Pawel Lewicki [2002-06-18 20:25 +0200]:
    Hallo,
    Is there any good solution to cache the results of SQL queries?
    Your database has one. It should save at least the query execution plan.
    I found the great cache manager in dmtools (http://csl.anu.edu.au/ml/dm/), but I wonder
    if there is any alternative. I will start with MySQL database, but it would
    be great to be able to plug it into other RDBMS's. I am interested in both -
    file and memory caching. I also know ZSQL Method in Zope.
    I'm sceptical. How does this help? How does the cache manager get
    notified of changes in the database? How is it different from just
    storing the queries I'm interested in away on disk if they deliver large
    resultsets?

    Gerhard
    --
    This sig powered by Python!
    Au?entemperatur in M?nchen: 25.4 ?C Wind: 2.9 m/s
  • Van Gale at Jun 19, 2002 at 8:25 am
    "Gerhard H?ring" <gerhard at bigfoot.de> wrote in message
    news:mailman.1024470864.21143.python-list at python.org...
    * Pawel Lewicki [2002-06-18 20:25 +0200]:
    Hallo,
    Is there any good solution to cache the results of SQL queries?
    Your database has one. It should save at least the query execution plan.
    Check out SQL Relay at http://www.firstworks.com/sqlrelay.html

    It might do what you are looking for and has Python bindings, but I'm in the
    same skeptical camp as Gerhard :)

    Van
  • Pawel Lewicki at Jun 19, 2002 at 2:11 pm

    * Pawel Lewicki [2002-06-18 20:25 +0200]:
    Hallo,
    Is there any good solution to cache the results of SQL queries?
    Your database has one. It should save at least the query execution plan.
    I found the great cache manager in dmtools
    (http://csl.anu.edu.au/ml/dm/), but I wonder
    if there is any alternative. I will start with MySQL database, but it
    would
    be great to be able to plug it into other RDBMS's. I am interested in
    both -
    file and memory caching. I also know ZSQL Method in Zope.
    I'm sceptical. How does this help? How does the cache manager get
    notified of changes in the database? How is it different from just
    storing the queries I'm interested in away on disk if they deliver large
    resultsets?
    Probably not much. :) But dmtools give the out-of-the-box solution. It takes
    the function (sql query execution in that case), arguments (query) and
    checks given dependency files, which in case of MySQL are table files. It
    deals perfectly with my needs and I wondered if there was any other option.

    Pawel Lewicki
  • Steve Holden at Jun 19, 2002 at 7:02 pm
    "Pawel Lewicki" <lewy0lewy at poczta.onet.pl> wrote in message
    news:aentug$m8d$1 at news.onet.pl...
    Hallo,
    Is there any good solution to cache the results of SQL queries? I found the
    great cache manager in dmtools (http://csl.anu.edu.au/ml/dm/), but I wonder
    if there is any alternative. I will start with MySQL database, but it would
    be great to be able to plug it into other RDBMS's. I am interested in both -
    file and memory caching. I also know ZSQL Method in Zope.
    I developed this (primitive) solution for "Python Web PRogramming". It might
    be a starting point for you.

    regards
    Steve

    import mx.DateTime
    import dtuple

    #################################################################
    # $Revision: 7 $
    # $Date: 10/19/01 1:37p $
    #################################################################

    class CacheQuery:

    """Defines a database query that caches database row sets.

    This object is initialized with

    tbl table name in the database
    colnames list of field names retrieved
    keynames list of keys used for retrieval
    conn database connection
    refresh caching refresh interval

    Individual results are read by calling the object with a
    tuple of key values as an argument. If the row set associated
    with this particular set of keys is not present, or was read
    longer than the refresh interval ago, it is re-read from the
    database and stored in the content table as a list of database
    tuples, which allow columns to be accessed by name.

    Otherwise the already-cached database tuple set is returned.

    Refinements might be added, such as registering with an
    observer that might clear down all cache entries periodically
    to force a global database refresh, and using a second SQL query
    on record modified timestamps to determine whether a refresh is
    really required (which may or may not be a win for a given set
    of columns).
    """

    def __init__(self, tbl, colnames, keynames, conn, refresh=0,
    ORDER=None):
    """Create a caching data set for the given table, columns and
    keys."""
    self._flush()
    self.tbl = tbl
    self.keynames = keynames
    self.refresh = refresh
    self.cursor = conn.cursor()
    self.sql = "SELECT %s FROM %s" % (",".join(colnames), tbl)
    if keynames:
    condition = " AND ".join(["%s=?" % f for f in keynames])
    self.sql += " WHERE %s" % condition
    if ORDER:
    self.sql += " ORDER BY " + ", ".join(ORDER)
    self.desc = dtuple.TupleDescriptor([[n, ] for n in colnames])
    print "Descriptor:", self.desc
    print "SQL:", self.sql

    def _flush(self):
    """Remove all trace of previous caching."""
    self.recs = {}
    self.when = {}

    def __call__(self, keyvals=(), debug=0):
    """Return the data set associated with given key values."""
    assert len(keyvals) == len(self.keynames)
    now = mx.DateTime.now()
    if self.recs.has_key(keyvals) and self.refresh and (now -
    self.when[keyvals] < self.refresh):
    if debug: print "Interval:", now - self.when[keyvals]
    return self.recs[keyvals]
    else:
    self.cursor.execute(self.sql, keyvals)
    rows = self.cursor.fetchall()
    result = [dtuple.DatabaseTuple(self.desc, row) for row in rows]
    if self.refresh:
    if debug: print "Caching", self.tbl, keyvals, " at", now
    self.recs[keyvals] = result
    self.when[keyvals] = now
    return result

    def close(self):
    self.recs = None
    self.when = None
    self.cursor.close()


    if __name__ == "__main__":
    #
    # Sorry, you'll need your own database details in here
    #
    import mx.ODBC.Windows as odbc
    conn = odbc.connect("prom2000")
    s1 = CacheQuery("department", # table
    "DptName DptWelcome DptLnksTxt".split(), # columns
    ("DptCode",), # key
    columns
    conn, refresh=0) # other
    stuff
    while 1:
    dc = raw_input("Department Code: ")
    if not dc:
    break
    rows = s1((dc, ), debug=1)
    if len(rows) == 0:
    print "No such department"
    else:
    for row in rows:
    print """
    Department: %s Full Name: %s
    Welcome Text:
    %s
    Links Text:
    %s
    """ % (dc, row.DptName, row.DptWelcome, row.DptLnksTxt)
    s1.close()
    conn.close()



    --
    -----------------------------------------------------------------------
    Steve Holden http://www.holdenweb.com/
    Python Web Programming http://pydish.holdenweb.com/pwp/
    -----------------------------------------------------------------------

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppython-list @
categoriespython
postedJun 18, '02 at 6:25p
activeJun 19, '02 at 7:02p
posts5
users4
websitepython.org

People

Translate

site design / logo © 2022 Grokbase