[DB-SIG] Python database API 2.0/returning Column names with data

M.-A. Lemburg mal@lemburg.com
Fri, 02 Nov 2001 17:04:09 +0100

> Gary Maynard wrote:
> HI,
>   We are trying to implement a method whereby we can return data from
> the database in key value dictionary pairs.
> so for example we have a table something like this:
> Table: Address
> Name varchar(20),
> Street varchar(20),
> Town varchar(20),
> ccode char(2)
> and we want to return the data into a dictionary so that we also have
> access to the column title.  For example, let's say we query the
> database with something like this:
> result = cursor.fetchall("select * from Address")
> Then we'd like to put the data from result into another data object,
> call it sorted_result.  We'd like to include the column names in this
> object such that we could obtain any piece of data by giving it the
> row number and column name.  So, if we wanted to obtain the Street of
> the 6th row, we would say something like:
> sorted_result [6]['Street']
> And if we wanted to iterate over all the streets we can do:
> for x in result:
>    do stuff with result[x]['Street']
> There seems to be fairly standard ways of doing this in DBI
> (selectrow_hashref) and ODBC (Datahash), but I can't seem to make it
> work with this API.
> Any suggestions?

Please scan the DB-SIG archives for discussions and links on this topic:
there are a few tools out there to wrap the results of .fecthxxx() 
into dictionaries. Easiest to use is probably dtuple.py by Greg Stein.

We should really add a note about this to the DB API -- this 
question is becoming a FAQ.

Marc-Andre Lemburg
CEO eGenix.com Software GmbH
Consulting & Company:                           http://www.egenix.com/
Python Software:                        http://www.lemburg.com/python/