Cache Python MySQL Result using Redis
We’re often using MySQL so much, like read data from DB, calculate something, write data with index. And I bet You know, that process is also using your i/o machine. I prefer my CPU usage 100% than my i/o 50%.
Redis is live in memory, so it won’t do anything like read / write to disk that make your i/o process high (untill your max memory, if it’s not enough, redis create virtual memory, CMIIW). I plan to make my application faster and faster with caching using redis, and I will improve my news web crawler to going faster with redis as cache.
The code
#!/usr/bin/python # Author : Fajri Abdillah a.k.a clasense4 # Twitter : @clasense4 # mail : clasense4@gmail.com # import modules used here -- sys is a very standard one import sys import db_base_local # ==> Your MySQL Connection import redis # ==> Make sure to install this library using pip install redis from datetime import datetime import time import cPickle import hashlib # START TIME startTime = datetime.now() # CURSOR DB CURSOR = db_base_local.conn.cursor() # Redis Object R_SERVER = redis.Redis("localhost") sql = "select * from news_crawler_rss" # Or Any SQL script # Gather our code in a main() function def cache_redis(sql, TTL = 36): # INPUT 1 : SQL query # INPUT 2 : Time To Life # OUTPUT : Array of result # Create a hash key hash = hashlib.sha224(sql).hexdigest() key = "sql_cache:" + hash print "Created Key\t : %s" % key # Check if data is in cache. if (R_SERVER.get(key)): print "This was return from redis" return cPickle.loads(R_SERVER.get(key)) else: # Do MySQL query CURSOR.execute(sql) data = CURSOR.fetchall() # Put data into cache for 1 hour R_SERVER.set(key, cPickle.dumps(data) ) R_SERVER.expire(key, TTL); print "Set data redis and return the data" return cPickle.loads(R_SERVER.get(key)) # Standard boilerplate to call the main() function to begin # the program. if __name__ == '__main__': cache_redis(sql)
Explanation
- Check Redis Key, if exists, just return it
- If not exists, do MySQL query, and set the key, then return it
Why I use serialization with cPickle? because Redis just store the string with SET Command, so, our MySQL result must serialize to string, and cPickle is faster than pickle or marshal (cmiiw).
Conclusion
This simple post is just to show how to use cache python mysql result using redis. And in my opinion, redis just awesome 😀
Better code view
[…] I will show you a little trick using croniter, in the future, it will be combined with Redis Caching and News Web […]
What if your query has now() function or if there is an insert during your TTL ?
Your solution is not an acceptable query cache.
On my case, that acceptable, because I could tell when the new data will be inserted into the table. So it won’t give me invalidate data.
Thank you sharing this sample code it is really helpful..
One quick question what happen when data is new inserted or updated into DB, because we are fetching the data from redis cache but that cache is holding the old data..
[…] Cache Python MySQL Result using Redis […]
[…] Cache Python MySQL Result using Redis […]