Geert JM Vanderkelen

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

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.

13 thoughts on “Fetching rows as dictionaries with MySQL Connector/Python (revised)

  1. marco

    A namedtuple could be an alternative:

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

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

    (Edited by Geert: Python code syntax)

  2. 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?

  3. Geert Vanderkelen Post author

    @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.

  4. 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.

  5. 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']

  6. 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! :)

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

    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)))
    
  8. 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 :)

  9. Anurag Kesari

    What is the ‘u’ character that appears everywhere in the dictionary output? How can you suppress it?

Leave a Reply

Your email address will not be published. Required fields are marked *

+ 54 = 58

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>