Geert JM Vanderkelen

Fetching rows as dictionaries with MySQL Connector/Python

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.

10 thoughts on “Fetching rows as dictionaries with MySQL Connector/Python

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

    1. geert Post author

      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

  2. PBLN RAO

    Greet,

    ur class

    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
    
    

    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.

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

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

  5. Geert Vanderkelen Post author

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

Leave a Reply

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

2 + 6 =

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>