caching mysql query returned by Python script

Question

I have this python '2.6.4' script:

#!/usr/bin/python
import MySQLdb
import csv
import pprint

db = MySQLdb.connect(host="localhost", # The Host
                     user="username", # username
                      passwd="password", # password
                      db="dbname") # name of the data base

cursor = db.cursor() 

cursor.execute("SELECT name, id, city, storeid FROM Products;")

StudentsData = cursor.fetchall()
pprint.pprint(StudentsData)

this query return millions of rows, and it takes 3 minutes to execute, and it run 20 times a day, and everyuser has to wait three minutes to fetch the output.

I was thinking about caching it, but I don't know how to do that in python, will that be a good idea, if so how can I do it, using the obove code.

please help me if there's a better way to do this?

The decision of whether to cache is entirely up to you. Will stale data be a problem? — jedwards
– jedwards, Commented Apr 1, 2013 at 19:02

jedwards · Accepted Answer · 2013-04-01 21:04:32Z

6

The decision of whether to cache is entirely up to you. Will stale data be a problem?

The simplest caching scheme I can think of is something like the following using Python's pickle module:

import MySQLdb
import csv
import pprint
import time

MAX_CACHE_AGE = 60*20  # 20 Minutes
CACHE_FILENAME = 'results.cache'

with open(CACHE_FILENAME, 'r') as cache:
    cached = pickle.load(cache)

if(time.time() > cached['timestamp'] + MAX_CACHE_AGE):
    # Cache too old, run query
    db = MySQLdb.connect(host="localhost", # The Host
                     user="username", # username
                     passwd="password", # password
                     db="dbname") # name of the data base

    cursor = db.cursor() 
    cursor.execute("SELECT name, id, city, storeid FROM Products;")
    StudentsData = cursor.fetchall()

    # Update cache file
    data = {'results': StudentsData, 'timestamp':time.time()}
    with open(CACHE_FILENAME, 'w') as cache:
        pickle.dump(data, cache)

else:
    # Cached data is fresh enough, use that
    StudentsData = cached['results']

pprint.pprint(StudentsData)

You'll need to initialize the results.cache file once manually.

EDIT

The with syntax is a context manager and was introduced in Python 2.5.

with open(CACHE_FILENAME, 'r') as cache:
    cached = pickle.load(cache)

Can be rewritten as

cached = open(CACHE_FILENAME, 'r')
cached = pickle.load(cache)
cached.close()

EDIT2

After a long discussion in chat, the following works:

import MySQLdb
import csv
import pprint
import time
import pickle

MAX_CACHE_AGE = 60*20  # 20 Minutes
CACHE_FILENAME = 'results.cache'

regen = False
try:
    with open(CACHE_FILENAME, 'r') as cache:
        cached = pickle.load(cache)

    if(time.time() > cached['timestamp'] + MAX_CACHE_AGE):
        print("Cache too old: regenerating cache")
        regen = True
    else:
        print("Cached data is fresh enough: loading results from cache")

except IOError:
    print("Error opening %s: regenerating cache" % CACHE_FILENAME)
    regen = True

if(regen):
    # Cache too old, run query
    db = MySQLdb.connect(host="localhost", # The Host
                     user="username", # username
                     passwd="password", # password
                     db="dbname") # name of the data base

    cursor = db.cursor()
    cursor.execute("SELECT name, id, city, storeid FROM Products;")
    StudentsData = cursor.fetchall()
    cursor.close()

    # Update cache file
    data = {'results': StudentsData, 'timestamp':time.time()}
    with open(CACHE_FILENAME, 'w') as cache:
        pickle.dump(data, cache)

else:
    # Cached data is fresh enough, use that
    StudentsData = cached['results']


print StudentsData

edited Apr 1, 2013 at 21:04

answered Apr 1, 2013 at 19:12

jedwards

30.3k3 gold badges69 silver badges94 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

mongotop Over a year ago

Thank you very much for the great example!! I believe that this is exactly what I'm looking for, the only thing is that I get this error, with open(CACHE_FILENAME, 'r') as cache: ^ SyntaxError: invalid syntax should I install something for the script to work? Thanks!

jedwards Over a year ago

Did you miss the as in there?

mongotop Over a year ago

I got the error with and without as . I tried to remove it and see if that will solve the issue. but it didn't.

jedwards Over a year ago

What version of Python are you using? A version before context managers were supported? I edited in an alternative pattern in case you're using Python < 2.5

mongotop Over a year ago

sorry, I didn't post that. '2.6.4'

|

Fredrik Håård · Accepted Answer · 2013-04-01 19:01:45Z

2

In Python 3, and for a very simple case, you could look at functools.lru_cache: http://docs.python.org/3.2/library/functools.html#functools.lru_cache

For more sophisticated caching, Beaker is probably the way to go: http://beaker.readthedocs.org/en/latest/caching.html

answered Apr 1, 2013 at 19:01

Fredrik Håård

2,9351 gold badge26 silver badges32 bronze badges

1 Comment

hunter_tech Over a year ago

Any alternatives for python2??

Collectives™ on Stack Overflow

caching mysql query returned by Python script

2 Answers 2

10 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

10 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related