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.
Facebook
LinkedIn
Twitter
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.
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
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 Noneis 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.
@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/
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
@biz
Yes, but that uses MySQLdb, and that’s not MySQL Connector/Python.
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!
@Thomas.encode(‘utf8′) on the result.
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