Fetching rows as dictionaries with MySQL Connector/Python

Update 29 Aug 2012: Please check this new blog entry when using MySQL Connector/Python v1.0 and later. The information here under is considered to be obsolete.

This post describes how to make a custom cursor returning rows as dictionaries using MySQL Connctor/Python v0.2 (or later).

Problem: you want to fetch rows from the database and return them as a dictionary with keys being the column names.

First, lets check how you would do it without any custom cursor.

cnx = mysql.connector.connect(host='localhost',database='test')
cur = cnx.cursor()
cur.execute("SELECT c1, c2 FROM t1")
result = []
columns = tuple( [d[0].decode('utf8') for d in cur.description] )
for row in cur:
  result.append(dict(zip(columns, row)))    
pprint(result)
cur.close()
cnx.close()
1

The above results in an output like this:

1
[{u'c1': datetime.datetime(2010, 10, 13, 8, 55, 35), u'c2': u'ham'},
 {u'c1': datetime.datetime(2010, 10, 13, 8, 55, 38), u'c2': u'spam'}]

Alternatively, you can code your own cursor which will return a row as a dictionary. Here is an example on how to do this:

import mysql.connector

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

What we did is subclassing MySQLCursor and overload the fetchone()-method. To use the above, you can do following:

cnx = mysql.connector.connect(host='localhost',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 trick is to pass the cursor_class-argument when creating the cursor. (At the time of writing, I realize that the cursor_class should be part of the connection arguments to set a default for all requested cursors. We’ll change that.)

It’s a bit more elegant coding and possibilities are endless. Maybe you’d like a cursor which returns a row in XML for example.

MySQL Connector/Python 0.2-devel available

Next development release v0.2.0 of MySQL Connector/Python is available for download and testing. We still don’t recommend to use it in production: it is not beta or GA yet, but we are getting there.

Bug reports and feature requests are welcome through the Launchpad bug tracking tool.

Highlights:

  • .executemany() now optimizes INSERT statements using the MySQL
    multiple row syntax.
  • Setting sql_mode and time_zone when connecting as well as collation.
  • Raw Cursors can be used when you want to do the conversion yourself.
  • Unittests now bootstrap own MySQL server instance.
  • Tidying the source tree.

Full list of changes and bug fixes can be found online or in the ChangeLog-file, part of the distribution.

About MySQL Connector/Python: MySQL Connector/Python is implementing the MySQL Client/Server protocol completely in Python. No MySQL libraries are needed, and no compilation is necessary to run this Python DB API v2.0 compliant driver. It is compatible with Python v2.5 and later as well as Python v3.1 and later.