When To Use MySQL Cursor Classes In Python

I have been writing a lot of code that has been interacting with MySQL lately. Sometimes I find it easier to work the result set in a dictionary form and other times it is easier with an array. But in order to not break all your code, it is necessary to set a default cursor class that keeps your code consistent. More often than not, I find using using a arrays is easier since I just want quick access to all the retrieved data. I also end up making my SELECT calls while specifying the columns and order of the columns I want returned.

The reason that using cursor classes is handy is because Python doesn’t come with a mysql_fetch_assoc like PHP or selectrow_hashref like Perl’s DBI interface. Python uses cursor dictionaries to bridge this gap. Ultimately your result is the same. But as with Perl and PHP, defaulting to cursor dictionaries isn’t a good idea for larger datasets because of the extra processing time and memory required to convert the data.

import MySQLdb

conn = MySQLdb.Connect(
    host='localhost', user='user',
    passwd='secret', db='test')
cursor = conn.cursor()
cursor.execute("SELECT this,that FROM foobar")
rows = cursor.fetchall()
cursor.close()
conn.close()

This then allows me to do just call the DictCursor (dictionary based cursor) on a single cursor object for a particular query and leave all the rest of the queries as array based results.

import MySQLdb
import MySQLdb.cursors

conn = MySQLdb.Connect(
    host='localhost', user='user',
    passwd='secret', db='test')
cursor = conn.cursor(cursorclass=MySQLdb.cursors.DictCursor)
cursor.execute("SELECT this,that FROM foobar")
rows = cursor.fetchall()
for row in rows:
    print row['this'], row['that']
cursor.close()
conn.close()

Note: Doing something like changing the connection string to use a default cursor class of dictionary cursor in an existing code base is not a good idea. It will make your results come back as dictionaries when your current code expects arrays. Yes, I did this and had to track it down the hard way. Hopefully this saves you some trouble.

conn = MySQLdb.Connect(
    host='localhost', user='user',
    passwd='secret', db='test',
    cursorclass=MySQLdb.cursors.DictCursor)