Python MySQLdb snippet

January 15th, 2008

So, for the past few weeks, I’ve been hacking around with Python’s MySQLdb DBAPI v2 driver. After getting so used to mysqli in PHP, the move to MySQLdb was a mixed bag - some things are awesome, others not so much.

First the awesome - it’s so much cleaner to do safe, simple queries with python’s dbapi, than with the PHP mysqli stuff. Just call cursor.execute(query, arguments) and pass in your SQL and it’s arguments, and all the escaping and safety stuff is taken care of behind the scenes. No ugly hacks to check to see if something is a digit or a string and to properly escape/format the final query for you. In addition to that, connecting and setting the character set and forcing the driver to use unicode is all just 2 little options passed in the connect() method.

Now, the not so awesome - I really liked mysqli_fetch_assoc() for returning data from the database in a easy to use form - an associate array (in case it wasn’t obvious!). DBAPI drivers for python do not provide such a feature out of the box - your data comes back as a tuple, rather than a dict. While this makes some sense, the lack of a way to get a dict back seems to be rather un-pythonic. It violates the ‘Explicit is better than implicit’ and ‘Beautiful is better than ugly’ Zen of Python aphorisms. It requires implicit accessing of your data, as you must use tuple slices/subscripts instead of keywords to the dict, and that requires ugly numbers dropped into the otherwise easily readable code.

In an effort to save myself some trouble, here’s a quick way to emulate the fetch_assoc functionality, built in to a generator function so it can be used easily in ‘for’ statements and the like:

#!/usr/bin/env python
# Requires a version of python that supports generator functions (2.3+)
import  MySQLdb

connection = MySQLdb.connect(host="localhost", user="username", passwd="passwd", db="myInstance", use_unicode=True, charset="utf8")
cursor = connection.cursor()
def query(query, args):
        numRows = cursor.execute(query, args)
        tries = 0
        dbKeys = cursor.description
        row = {}
        while tries < numRows:
                tries = tries + 1
                dbRow = cursor.fetchone()
                for item, key in zip(dbRow, dbKeys):
                        row[key[0]] = item
                yield row

sql = """SELECT foo, bar FROM baz WHERE id = %(id)s"""
args = {'id':'adam'}

for rows in query(sql, args):
        print rows

That outputs:

	{'foo': u'fooValue', 'bar':'barValue'}
	{'foo': u'fooValue2', 'bar':'barValue2'}

Entry Filed under: Tech, coding, python