python mysql.connector DictCursor?

Question

In Python mysqldb I could declare a cursor as a dictionary cursor like this:

cursor = db.cursor(MySQLdb.cursors.DictCursor) 

This would enable me to reference columns in the cursor loop by name like this:

for row in cursor:   # Using the cursor as iterator 
    city = row["city"]
    state = row["state"]

Is it possible to create a dictionary cursor using this MySQL connector? http://dev.mysql.com/doc/connector-python/en/connector-python-example-cursor-select.html

Their example only returns a tuple.

I imagine the creators of MySQL would eventually do this for us?

Answer 1

A possible solution involves subclassing the MySQLCursor class like this:

class MySQLCursorDict(mysql.connector.cursor.MySQLCursor):
    def _row_to_python(self, rowdata, desc=None):
        row = super(MySQLCursorDict, self)._row_to_python(rowdata, desc)
        if row:
            return dict(zip(self.column_names, row))
        return None

db = mysql.connector.connect(user='root', database='test')

cursor = db.cursor(cursor_class=MySQLCursorDict)

Now the _row_to_python() method returns a dictionary instead of a tuple.

I found this on the mysql forum, and I believe it was posted by the mysql developers themselves. I hope they add it to the mysql connector package some day.

I tested this and it does work.

UPDATE: As mentioned below by Karl M.W... this subclass is no longer needed in v2 of the mysql.connector. The mysql.connector has been updated and now you can use the following option to enable a dictionary cursor.

cursor = db.cursor(dictionary=True)