Databases: Getting values by column name

[Anthony_Barker]

what about db_rows?

>>> cursor.execute('SELECT name, category FROM zoo;')
>>> results = cursor.fetchall()
>>> results
[ ("Giraffe","mammal"), ("Dog","mammal") ]

becomes:
>>> cursor.execute('SELECT name, category FROM zoo;')
# Make a class to store the resulting rows
>>> R = IMetaRow(cursor.description)
# Build the rows from the row class and each tuple returned from the cursor
>>> results = [ R(row) for row in cursor.fetchall() ]
>>> print results[1].fields.name, results[1]['category']

Unfortunately the http://opensource.theopalgroup.com/files/db_row-0.8.zip

site seems to be down right now.

I grabbed a copy (untested) from googles cache
http://xminc.com/linux/db_rows.txt

Anthony

"Ben Last" <ben at benlast.com> wrote in message news:<mailman.1800.1092753488.5135.python-list at python.org>...
> > 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