March 12th, 2004, 07:32 AM
MySQL and Dictionaries
Does anyone know howto get the MySQLdb module to return db results as a dictionary rather then a tuple?
March 12th, 2004, 06:50 PM
AFAIK The MySQLdb API conforms to Python's DB-API, which is defined at http://www.python.org/peps/pep-0249.html. The API does not support returning dictionaries, and in fact the document has a FAQ on the subject. I quote:
However if you know that the query is going to return tuples of (key, value) pairs then you could convert the results on the fly, e.g. (assuming Python 2.2 or later)
This should work since from 2.2 the DB-API cursor object conforms to the iterator prototype, and the dict type has a constructor that takes a sequence of (key, value) pairs and converts them to a dictionary.
cursor = connection.cursor()
results = dict(cursor)
If the query returns more than (key, value), e.g. (key, value1, value2...) then you will need to do a little more work to get it in the right format, such as wrap it in a generator or use a list substitution. e.g.
N.B. This would be very inefficient for large data sets, since it would have to create an intermediate list of results - it would be better to use a generator.
results = dict([key, (val1, val2) for key, val1, val2 in cursor])
Please note that all of the above is only theoretical - I have not tried it with MySQLdb.
If you only want dictionary-like behaviour then it may be worthwhile looking at the bsddb library, which can act just like a disk-based dictionary and for some applications can be more efficient than a relational DB. On many systems it is included as part of the standard Python installation, and is also available from sourceforge. There are other db libraries that work in a similar way (dumbdb, anydb, dbm etc), but I think the bsddb library is more robust and flexible.
Hope this helps.
Dave - The Developers' Coach
March 12th, 2004, 09:21 PM
You're looking for 'MySQLdb.cursors.DictCursor' me thinks; this basically lets you use the cursor in a similar way to a dictionary... Something like this
Note: this shows the basic idea but i havn't tested it so if it doesnt work let me know
connection = MySQLdb.connect('localhost', 'username', 'password', 'database')
cursor = connection.cursor(MySQLdb.cursors.DictCursor)
cursor.execute('SELECT key FROM table')
result = cursor.fetchall()
for value in result:
Hope this helps,
Last edited by netytan; March 12th, 2004 at 09:23 PM.
March 13th, 2004, 08:23 AM
Ooops, i ment to attach my mySQL wrapper class (which uses DictCursor), musta forgot . Anyway second time lucky i guess, here it is
March 13th, 2004, 09:50 AM
With that however the dict is return inside a tuple. Any idea's how to get it out? Without a for loop.
March 13th, 2004, 10:12 AM
If you know the number of rows thats will be returned then you can do it manually but it seems pretty pointless i.e.
This simply assigns the value (from the tuple) to a variable. however this is a lil limiting.
result = cursor.fetchall()
Whats wrong with the rows inside a tuple and iterating over the results? How do you want the results to be returned?
Maybe try 'cursor.fetchone()' like in my mysql wrapper; i cant remember if this returns values in a tuple but i dont think it does .
March 13th, 2004, 06:58 PM
Thanks, that really helped.
March 13th, 2004, 08:27 PM
No problem at all fear , very happy to help. If ya need anything else dont hesitate to ask