MySQL and Dictionaries

Dev Shed Forums Sponsor:

March 12th, 2004, 06:32 AM

brain_fear

Registered User

Join Date: Jan 2004

Location: Melbourne, Australia

Posts: 6

Time spent in forums: < 1 sec
Reputation Power: 0

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, 05:50 PM

DevCoach

Contributing User

Dev Shed Intermediate (1500 - 1999 posts)

Join Date: Feb 2004

Location: London, England

Posts: 1,585

Time spent in forums: 2 Weeks 4 Days 2 h 58 m 23 sec
Reputation Power: 1372

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:

Quote:

Question:

How can I construct a dictionary out of the tuples returned by
.fetchxxx():

Answer:

There are several existing tools available which provide
helpers for this task. Most of them use the approach of using
the column names defined in the cursor attribute .description
as basis for the keys in the row dictionary.

Note that the reason for not extending the DB API specification
to also support dictionary return values for the .fetchxxx()
methods is that this approach has several drawbacks:

* Some databases don't support case-sensitive column names or
auto-convert them to all lowercase or all uppercase
characters.

* Columns in the result set which are generated by the query
(e.g. using SQL functions) don't map to table column names
and databases usually generate names for these columns in a
very database specific way.

As a result, accessing the columns through dictionary keys
varies between databases and makes writing portable code
impossible.

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)

Code:

cursor = connection.cursor()
cursor.execute(..whatever..)
results = dict(cursor)

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.

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.

Code:

results = dict([key, (val1, val2) for key, val1, val2 in cursor])

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.

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, 08:21 PM

netytan

Hello World :)

Join Date: Mar 2003

Location: Hull, UK

Posts: 2,537

Time spent in forums: 1 Week 2 Days 18 h 17 m 47 sec
Reputation Power: 68

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

Code:

import MySQLdb

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:
    print value['key']

Note: this shows the basic idea but i havn't tested it so if it doesnt work let me know

Hope this helps,

Mark.

__________________
programming language development: www.netytan.com – Hula

Last edited by netytan : March 12th, 2004 at 08:23 PM.

March 13th, 2004, 07:23 AM

netytan

Hello World :)

Join Date: Mar 2003

Location: Hull, UK

Posts: 2,537

Time spent in forums: 1 Week 2 Days 18 h 17 m 47 sec
Reputation Power: 68

Ooops, i ment to attach my mySQL wrapper class (which uses DictCursor), musta forgot

. Anyway second time lucky i guess, here it is

Mark.

Attached Files

mysql.txt (729 Bytes, 547 views)

March 13th, 2004, 08:50 AM

brain_fear

Registered User

Join Date: Jan 2004

Location: Melbourne, Australia

Posts: 6

Time spent in forums: < 1 sec
Reputation Power: 0

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, 09:12 AM

netytan

Hello World :)

Join Date: Mar 2003

Location: Hull, UK

Posts: 2,537

Time spent in forums: 1 Week 2 Days 18 h 17 m 47 sec
Reputation Power: 68

If you know the number of rows thats will be returned then you can do it manually but it seems pretty pointless i.e.

Code:

...
result = cursor.fetchall()[0]
etc
...

This simply assigns the value (from the tuple) to a variable. however this is a lil limiting.

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

.

Mark.