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

David Bolen db3l@fitlinxx.com
Fri, 2 Nov 2001 17:10:38 -0500


Gary Maynard [maynard@mpi-cbg.de] writes:

> 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']

Others have pointed out some of the externally available modules
(e.g., dtuple), but it's also pretty simple to just write your own
function to do it.  Here's a sample of a function that is given
a cursor with a command previously executed, and automatically
processes the fetchall() into a list of dictionaries.  Far less
generic than something like dtuple, but it does meet your above goals:

	  - - - - - - - - - - - - - - - - - - - - - - - - -

def QueryToDictList(cursor):
    """QueryToDict(cursor)

    Execute query on the supplied cursor and return the result as a list of
    dictionaries, using the cursor description as the keys in each dict"""

    result = []
    rows = cursor.fetchall()
    for currow in rows:
        rowdict = {}
        for curcol in range(0,len(cursor.description)):
            rowdict[cursor.description[curcol][0]] = currow[curcol]
        result.append(rowdict)

    return result

	  - - - - - - - - - - - - - - - - - - - - - - - - -

This is a specific implementation that uses precisely the same case
for the dictionary keys as that of the column names.

With this, you would do something like:

    sorted_result = QueryToDictList(cursor)

and then you could use sorted_result as desired, either:

    sorted_result[6]['Street']

or

    for x in sorted_result:
        do stuff with x['Street']


My understanding for why something like this isn't part of the DB API
is that there is or was debate about various implementation details
(e.g., choices about case sensitivity of dictionary keys versus column
names) so it was easier to leave it to something layered on top of the
API rather than as part of the API proper.

-- David

/-----------------------------------------------------------------------\
 \               David Bolen            \   E-mail: db3l@fitlinxx.com  /
  |             FitLinxx, Inc.            \  Phone: (203) 708-5192    |
 /  860 Canal Street, Stamford, CT  06902   \  Fax: (203) 316-5150     \
\-----------------------------------------------------------------------/