Fetching rows as dictionaries with MySQL Connector/Python (revised)

It is possible with MySQL Connector/Python to define your own cursor classes. A very good use case is to return rows as dictionary instead of tuples. This post shows how to do this using MySQL Connector/Python v1.0 and is an update for an older blog entry.

In the example below we are subclassing the MySQLCursor class to create a new class called MySQLCursorDict. We change the _row_to_python() method to return a dictionary instead of a tuple. The keys of the dictionary will be (unicode) column names.

from pprint import pprint
import mysql.connector

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

cnx = mysql.connector.connect(user='root', database='test')
cur = cnx.cursor(cursor_class=MySQLCursorDict)
cur.execute("SELECT c1, c2 FROM t1")
rows = cur.fetchall()
pprint(rows)
cur.close()
cnx.close()

The output of the above script would be (formatted):

[
 {u'c1': 1,
  u'c2': 10},
 {u'c1': 2,
  u'c2': 20}
]

Depending on your needs, you can subclass from any class found in the mysql.connector.cursor module, but note that you will need to change some other methods to make it work.

Comments

marco

A namedtuple could be an alternative:

[python] # in mysql.connector.cursor.MySQLCursor

    tuple_factory = tuple

    def _row_to_python(self, rowdata, desc=None):
        try:
            if not desc:
                desc = self.description
            to_python = self._connection.converter.to_python
            gen = (to_python(desc[i], v) for i, v in enumerate(rowdata))
            return self.tuple_factory(gen)
        except StandardError, e:
             raise errors.InterfaceError(
                 "Failed converting row to Python types; %s" % e)


cnx = mysql.connector.connect(user='root', database='test')
cur = cnx.cursor()
cur.execute(...)
cur.tuple_factory = namedtuple('Row', cur.column_names)._make
cur.fetchall()

[/python]

A namedtuple preserves the order of the columns but the same could be achieved with an OrderedDict.

(Edited by Geert: Python code syntax)

Geert Vanderkelen
@marco Thanks for the suggestion using namedtuple(). However, we can not use that in Python v2 code since only supported since v2.6. Making a custom cursor is, again, the way to go here.
Pete

Thank you very much for this. It’s very useful. I have had a couple of issues with (my incompetence and) getting it to work though.

I have added the class you gave above to the bottom of the cursor.py file to make it accessible without referencing it each time. When copied directly I get a

“NameError: name ‘mysql’ is not defined”

error presumably because it doesn’t need the full reference when in the same file. I then tried removing it to leave

“class MySQLCursorDict(MySQLCursor):”

as the first line and I now get “NameError: name ‘MySQLCursorDict’ is not defined” as an error from my file.

What am I missing to get this to work correctly? Or is it not possible to get it to work this way?

Geert Vanderkelen

@Pete Adding new cursors directly to mysql/connector/cursor.py is not a good idea: on the next update of Connector/Python you will lose your custom classes.

That said, you will need to import the new cursor when you want to use it: from mysql.connector.cursor import MySQLCursorDict

So, in the end, you better put it in your project/application/package since you have to import it anyway.

Forrest
In the old blog post, you show how to do this without a custom cursor. That code breaks now that I’ve upgraded to Connector v1.0.7 (AttributeError: ‘str’ object has no attribute ‘decode’), and I’d rather not create a whole custom cursor just for this. Is there a way to do this in Python 3 without all that fluff? I’m still a bit new to Python, so I’m not sure exactly what I need to do to achieve this.
Geert Vanderkelen
@Forrest Remove the ‘.decode(‘utf8’)’ and it should work.
Aijaz
May be I am missing something here. import MySQLdb dbConn = MySQL.connect(host=‘xyz’, user=‘xyz’, passwd=‘xyz’, db=‘xyz’) dictCursor = dbConn.cursor(MySQLdb.cursors.DictCursor) dictCursor.execute(“SELECT a,b,c FROM table_xyz”) resultSet = dictCursor.fetchall() for row in resultSet: print row[‘a’]
Geert Vanderkelen
@Aijaz Yes, you missed something: you are using MySQLdb, not MySQL Connector/Python.
Pythotic
Thank you for sharing !
Eddie Dunn

Hi Thanks for this. I am however struggling with attempting this when calling stored procedures. I am wondering if this is possible with mysql.connector? The only way I can seem to get my SP result using mysql.connector is to do something like the following: cursor.callproc(“get_annual_entries_by_area_code”,[area,year]) for result in cursor.stored_results(): results=result.fetchall()

however the method you describe seems to only work with execute() also tried the namedtuple as I am using python3 but still no dice.

I am far from an expert python programmer but would like to make use of some kind of associative field name to value in my code.

All these array indices are driving me crazy! :)

Eddie Dunn
OK well I figured out what is going on as well as I can understand at this point and a way around. It seems callproc() returns a MySQLCursorBuffered no matter what you pass to connection.cursor() in this case. The following achieved my desired effect. [sourcecode language=“python” wraplines=“false” collapse=“false”] cursor.callproc(“get_annual_entries_by_area_code”,(‘37001’,‘2012’)) result = [] for recordset in cursor.stored_results(): for row in recordset: result.append(dict(zip(recordset.column_names,row))) [/sourcecode]
Grant Miller
This is what I was missing, I followed the first tutorial which works great for fetchone but doesn’t work for fetchall, might be good to just replace the old one with this new one :)
Anurag Kesari
What is the ‘u’ character that appears everywhere in the dictionary output? How can you suppress it?