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.

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.

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.