Facebook Tornado's Excellent Python MySQLdb Wrapper

20 Feb 2010

tornado.database is part of the FriendFeed/Facebook Tornado webserver and web framework and is a wrapper around MySQLdb. It is a single file and has no other dependencies. If you are already using Tornado, jump down for gotchas section.

Use it. I've always found DBAPI2 to be in the sweet spot of simulatenously not being low level enough to take advantage of the native documentation and not being high level enough so I don't really now what the "right way" is (e.g. Do we really need 3 different cursor types? Isn't the entire concept of a 'cursor' a leftover from the 80s?). This wraps up that mess and solves some common MySQL quirks.

Connections

Connections are opened so that they are in UTF8 character encoding and UTC timezone. But you are doing this already, right? Yeah I thought so.

Bye Bye Cursors

To the end user, cursors are gone. They are now under the hood where they belong and properly managed so they don't leak.

Columns by Name

You can do row['foo'] instead of row[0]. While this does add some (minor) overhead, the benefit of developer time, self.documenting code, and the prevention of future bugs is well worth it.

Query Replacement: no more tuples

Gone!

# Native
conn.fetchone(astring, (arg1, arg2, arg3))

# Tornado
conn.get(astring, arg1, arg2, arg3)
# or if you have a tuple
conn.get(astring, *arglist)

Gotchas

It ain't perfect

Other connection parameters

There is no automatic way of adding to changing how a MySQL connection is created. Fortunately, python to the rescue. For instance, adding the autoreconnect feature you can do this:

conn = Connection(host, database)

# hack in new args, or replace them
conn._db_args['reconnect'] = 1

# close current connection and get a new one
conn.reconnect()

Hopefully a future release will allow a cleaner mechanism for adding other paramters

Row Woe

With a tornado query row, you can access the collumns using dict style row['foo'] or property style row.foo (see the Row object at the end of database.py).

Nifty, huh? But, accessing rows as row.foo comes with a hefty price:

#  Using attributes/property style, row.foo
3418150 function calls in 61.843 CPU seconds

# Using dict style, row['foo']
2681150 function calls in 48.673 CPU seconds

Using row.foo made my query run 25% slower! So don't use 'em.

Conclusion

If you have a new project, whether or not you are using the rest of Tornado, use this wrapper for MySQL.

pydoc tornado.database is your friend, but the code is so straighword and in one file, it should be a snap to read.