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) |
