When To Use MySQL Cursor Classes In Python

By eric

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.

1
2
3
4
5
6
7
8
9
10
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.

1
2
3
4
5
6
7
8
9
10
11
12
13
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.

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

Follow My Travels

Buy My Book

Archives

  • 2014
  • 2013
  • 2012
  • 2011
  • 2010
  • 2009
  • 2008
  • 2007
  • 2006

writing