Databases: Getting values by column name

[Ben Last]

> From: python-list-bounces+ben=benlast.com at python.org
> [mailto:python-list-bounces+ben=benlast.com at python.org]On Behalf Of
> Robert Ferber
> Anyway...
> only use database rows as sequences, ie as arrays with numerical index.
> This gives you loads of problems when using "select *" and also bad code
> readability when selecting specific columns.
> Is there a way to use them as dictionaries, ie with Index-Strings (=column
> names) as indexes?
The PEP describes the lowest-level database interface; usually you need to
wrap that with some application-aware code to get exactly what you need.
Anyway, the answer to your question is: Yes, if you're happy to write a
little extra Python.  A cursor object, on which you've executed a SELECT, in
a PEP249-compliant module would normally have a "description" attribute;
this is a list of tuples, one per result column.  The first element of each
tuple is the column name.  So:

    conn = MySQLdb.connect(...)
    cr = conn.cursor()
    cr.execute("select * from myTable")

    #Look up the columns and get a list of their names, in order.  This
    #isn't necessary for the following code, it's here as an example.
    cols = map(lambda x: x[0], cr.description))

    #Build a list of dicts, one per row, with the values stored by column
names as keys.
    #There are more elegant ways to do this; this is example code.
    rows = []
    for raw_row in cr.fetchall():
        row = {} #start with an empty dict
        for fieldIndex in range(len(cr.description)):
            #Each description is a tuple with the first element
            #being the field name.
            row[description[fieldIndex][0]] = raw_row[fieldIndex]
        rows.append(row)
    cr.close()

    #Now "rows" is a sequence of dicts, one per row, with the values indexed
by column name.

Just to be exact; the rows are returned as *tuples*, not arrays (at least in
MySQLdb; they might be lists in other database modules).  Lists and tuples
are not necessarily the same as arrays in other languages, depending on
exactly how you define an array.
Various people (including me) have also written code to return each row as
an object that has the column values as attributes, so you can refer to them
as myRow.Column1 rather than myRow['Column1'].  If you're interested, drop
me an email and I'll send you a module that can do that.  It's part of the
Quasi shell (http://quasi-shell.sourceforge.net/ - gratuitous plug) which
has a lot of SQL handling built-in.

regards
ben-who-spends-his-life-in-MySQLdb