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
Dev Shed Forums Sponsor:
March 12th, 2004, 06:32 AM
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
