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.
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.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.
host='localhost', user='user',
passwd='secret', db='test',
cursorclass=MySQLdb.cursors.DictCursor)
Related posts:
