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()
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.
PlanetMySQL Voting: Vote UP / Vote DOWN
