Fetching rows as dictionaries with MySQL Connector/Python

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.

Solution

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

The above results in an output like this:

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

Comments

Shlomi Noach

Keep them coming! Any chance of also doing it a-la MySQLDb? Which is, specify per connection (or per statement execution) the type of result set you want to get: e.g. cur.execute(“SELECT c1, c2 FROM t1”, result_set_as_dict=True) cur.execute(“SELECT c1, c2 FROM t1”, result_set_as_dict=False) (the above is not like MySQLDb does)

And so have the cursor return a list ([]) or a dict({}) accordingly?

Following the steps you’ve describes leads to an endlessly repeating pattern of always doing the same thing, something a simple driver implementation could very much ease.

geert

Hello Shlomi,

I like the way it is right now: “Need new functionality? Subclass and overload”, you know, the OO way. Adding all these arguments to execute() would make it complicated. What I will do is adding ‘cursor_class’ as connecting argument, I just forgot about that :)

Cheers, Geert

PBLN RAO

Greet,

ur class

import mysql.connector

[code]

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

[/code]

is returning all records without column names, where as other is working fine. I tried changing ur definition from

fetchone

to

fetchall

and it returned the result with column names as desired but only 1st row in the table is returned.

Geert Vanderkelen
@PBLN RAO Thanks for reporting this problem! I have written a new blog entry describing how to do it with MySQL Connector/Python v1.0: http://geert.vanderkelen.org/connectorpython-custom-cursors/
biz

Thanks for sharing this. It helped me with a problem that I was having converting a postgresql database interface into mysql using python. Just as a side note, you don’t have to create a custom cursor to have MySQLdb return a dictionary. There is an existing cursor object that will do it for you.

cur = db.cursor(MySQLdb.cursors.DictCursor) cur.fetchone() # or cur.fetchmany()

returns a dictionary object {column: value, …}

http://forums.devshed.com/showpost.php?p=1838520&postcount=2

Geert Vanderkelen
@biz Yes, but that uses MySQLdb, and that’s not MySQL Connector/Python.
Thomas

Hi Geert,

I’m searching quite a while to get rid of a certain problem, maybe you could help me…

You wrote: 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’}]

Now you’re output is in unicode and I like it to be just printed to the screen as normal text. My question to you is if you know how to convert it or to get rid of the unicode characters??

Thanks!

Geert Vanderkelen
@Thomas Unicode is important, so better start getting used to it. In Python v3 strings are Unicode. That said, in Python v2, simply use ‘use_unicode=False’ as connection argument, or use .encode(‘utf8’) on the result.
Helen Neely

Thanks Geert, for this post. It saved me hours of headache on getting column names for my select statement.

Nice blog BTW :)

Grant Miller
Thanks a ton for posting this, exactly what I needed. This module is a great alternative to the compile problems we have with MySQLdb.