May 04, 2005
Returning Rows as Dicts in MySQLdb
Do you want to return a row of data from your database as a dict rather than a tuple? Using MySQLdb? Then here's how you do it. The traditional way to retrieve a row from a table is;
>>> import MySQLdb
>>> myDb = MySQLdb.connect(user='andy47', passwd='password', db='db_name')
>>> myCurs = myDb.cursor()
>>> myCurs.execute("SELECT columna, columnb FROM tablea")
>>> firstRow = myCurs.fetchone()
>>> firstRow
('first value', 'second value')
But using the DictCursor cursorclass we get;
>>> import MySQLdb
>>> import MySQLdb.cursors
>>> myDb = MySQLdb.connect(user='andy47', passwd='password', db='db_name', cursorclass=MySQLdb.cursors.DictCursor)
>>> myCurs = myDb.cursor()
>>> myCurs.execute("SELECT columna, columnb FROM tablea")
>>> firstRow = myCurs.fetchone()
{'columna':'first value', 'columnb':'second value'}
Note the extra import at the beginning, without it Python doesn't know about MySQLdb's cursors module. You should also be wary of guessing the order that the items in each row of the DictCursor are returned in. There is no guarantee, for instance, that "columna" will always be the first key and "columnb" the second. When using the DictCursor you should always access values directly by their key. When using the normal cursor class you can access the returned values in sequence (make sure never to use "SELECT *") as that's the only way you can associate a value with the column it has come from.
Posted by Andy Todd at May 04, 2005 10:45 AM
There's also a way to do it without using a special cursor so that it's portable across DB API databases:
>>> import MySQLdb
>>> db=MySQLdb.connect(read_default_file="~/.my.cnf", db="media")
>>> c=db.cursor()
>>> c.execute("SELECT * FROM track_list")
1L
>>> c.description
(('artist_uid', 8, 1, 20, 20, 0, 0), ('artist_name', 253, 6, 765, 765, 0, 0), ('album_uid', 8, 1, 20, 20, 0, 0), ('album_title', 253, 26, 765, 765, 0, 0), ('track_uid', 8, 1, 20, 20, 0, 0), ('track_title', 253, 22, 765, 765, 0, 1))
>>> def fetchoneDict(cursor):
... row = cursor.fetchone()
... if row is None: return None
... cols = [ d[0] for d in cursor.description ]
... return dict(zip(cols, row))
...
>>> d=fetchoneDict(c)
>>> d
{'track_title': 'Here Come the Bastards', 'artist_name': 'Primus', 'artist_uid': 5L, 'track_uid': 1L, 'album_title': 'Sailing the Seas of Cheese', 'album_uid': 1L}
>>>
(Formatting is bad but you get the idea.)
Posted by: Andy Dustman on May 4, 2005 04:08 PMinteresting, I am using SQLOBJECT->MYSQL for a webapp, but it's giving me headaches with regard to semi-complex queries.
if I cant resolve these easily, I might go back to MySQLdb and try this stuff out.
thanx
Posted by: Guyon Morée on May 4, 2005 09:05 PMThese are two other functions that I find useful while treating db rows as dicts
def updateRow(cursor, table, **data):
idName = table + "_id"
id = data[idName]
del data[idName]
sets = []
for key in data.keys():
sets.append("%s = %%s" % key)
set = ', '.join(sets)
qq = "UPDATE %s SET %s WHERE %s = %%s" % (table, set, idName,)
cursor.execute(qq, tuple(data.values()+[id]))
def insertRow(cursor, table, **data):
keys = ', '.join(data.keys())
vals = ', '.join(["%s"] * len(data.values()))
qq = "INSERT INTO %s (%s) VALUES (%s)" % (table, keys, vals)
cursor.execute(qq, tuple(data.values()))
return cursor.lastrowid
(that cursor.lastrowid might be sqlite specific, we really need dbapi 2.1 that adds some optional interfaces for these)
cursor.lastrowid is an optional DB-API/PEP-249 extension, and MySQLdb supports it as well.
http://www.python.org/peps/pep-0249.html
I would expect a lot of modules to support this, or at least ones that support a SERIAL or AUTO_INCREMENT column.
The other very useful extension is to make cursors iterators (cursor.next() roughly equivalent to cursor.fetchone()), and MySQLdb supports this as well, but I don't know about other modules.
I have an example of this in my recent MySQL User Conference presentation; click on my name below to see it.
Posted by: Andy Dustman on May 5, 2005 02:55 AMI have some code that treats MySqlDb rows as structs or dicts, and generates insert/update statements as needed: http://adamv.com/dev/python/superhash/
We're using it in a small-scale DB-backed website and it more or less works.
Posted by: Adam Vandenberg on May 5, 2005 08:26 AM