Lessons in Tornado and MySQL
Tornado is python web framework. If you don’t know about it, you should check it out. It’s one of the easier frameworks to use, if you know what you are doing.
Tornado, as the documentation says, is great for applications that require long lived connections to the user like long polling. Tornado becomes great for these applications by using non blocking I/O.
The main thread in Tornado accepts connections so we need to keep that thread free as much as we can for Tornado to scale as it’s meant to be. This is great when the logic in your application is minimal or you extensively depend upon network based call. In case of I/O, you can initial the I/O and register a callback. This would be what people call event driven programming.
All this is great but where tornado is not so great is libraries to support event driven programming for basic technologies like MySQL and memcache. That makes it very important to look at what your application will do now or in future before you choose Tornado as your web framework.
In case you do choose Tornado and MySQL is your bottleneck. There are things you can do using python’s asynchronous programming and latest versions of Tornado even help you write asynchronous code looking like it’s synchronous. But if you are stuck to version of python like 2.6 or 2.7, here is my shot at allowing asynchronous MySQL logic. It uses a pool of workers to whom you send a query and a callback, which is called once the query returns.
The app looks something like this
import adb import tornado.ioloop import tornado.web import sys from tornado import gen from adisp import process import json class Connect(object): adb = None @classmethod def _connect_mysql(cls, conn_type='ro'): prefix = '%s_' % conn_type if cls.adb is None: cls.adb = adb.Database(driver="MySQLdb", database="test_db", user="user", password="password", host="localhost") @classmethod def _get_adb(cls, conn_type='ro'): if cls.adb is not None: return cls.adb else: cls._connect_mysql(conn_type) return cls.adb @process def fetch(sql, params, retry=False, conn_type='ro', callback=None): adb = Connect._get_adb(conn_type) data = yield adb.runQuery(sql) if callback is not None: callback(data) class MainHandler(tornado.web.RequestHandler): @tornado.web.asynchronous def get(self): fetch("select count(*) from test_table", {}, callback = self.callback) def callback(self, results): self.write(json.dumps(results)) self.finish() application = tornado.web.Application([ (r"/", MainHandler), ]) if __name__ == "__main__": application.listen(8888) tornado.ioloop.IOLoop.instance().start()