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


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.

python 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:

python [{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:

```python 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:

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