python - 缓存 Python 脚本返回的 mysql 查询

Question

我有这个 python '2.6.4' 脚本:

#!/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)

此查询返回数百万行，执行时间为 3 分钟，每天运行 20 次，每个用户都必须等待三分钟才能获取输出。

我正在考虑缓存它，但我不知道如何在 python 中做到这一点，那会是个好主意吗？如果可以，我该怎么做，使用上面的代码。

如果有更好的方法，请帮助我？

Answer 1

是否缓存完全由您决定。过时的数据会成为问题吗？

我能想到的最简单的缓存方案类似于以下使用 Python 的 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)

您需要手动初始化一次results.cache 文件。

---

编辑

with 语法是一个上下文管理器，是在 Python 2.5 中引入的。

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

可以重写为

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

---

EDIT2

经过长时间的聊天讨论，以下工作:

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