Python Cookbook 2Nd Edition Jun 1002005 [Electronic resources] نسخه متنی

Recipe 7.14. Using dtuple for Flexible Accessto Query Results

Credit: Steve Holden, Hamish Lawson, Kevin Jacobs

Problem

You want flexible access to
sequences, such as the rows in a database query, by either name or
column number.

Solution

Rather than coding your own solution, it's often
more clever to reuse a good existing one. For this
recipe's task, a good existing solution is packaged
in Greg Stein's dtuple module:

import dtuple
import mx.ODBC.Windows as odbc
flist = ["Name", "Num", "LinkText"]
descr = dtuple.TupleDescriptor([[n] for n in flist])
conn = odbc.connect("HoldenWebSQL")   # Connect to a database
curs = conn.cursor( )                  # Create a cursor
sql = ""SELECT %s FROM StdPage
WHERE PageSet='Std' AND Num<25
ORDER BY PageSet, Num"" % ", ".join(flist)
print sql
curs.execute(sql)
rows = curs.fetchall( )
for row in rows:
row = dtuple.DatabaseTuple(descr, row)
print "Attribute: Name: %s Number: %d" % (row.Name, row.Num or 0)
print "Subscript: Name: %s Number: %d" % (row[0], row[1] or 0)
print "Mapping:   Name: %s Number: %d" % (row["Name"], row["Num"] or 0)
conn.close( )

Discussion

Novice Python
programmers are sometimes deterred from using databases because query
results are presented by DB API-compliant modules as a list of
tuples. Since tuples can only be numerically subscripted, code that
uses the query results becomes opaque and difficult to maintain. Greg
Stein's dtuple module, available
from http://www.lyra.org/greg/python/dtuple.py,
helps by defining two useful classes:
TupleDescriptor and
DatabaseTuple. To access an arbitrary SQL
database, this recipe uses the ODBC protocol through the
mxODBC module, http://www.egenix.com//image/library/english/10241_python/mxODBCl,
but nothing relevant to the recipe's task would
change if any other standard DB API-compliant module was used
instead.

The
TupleDescriptor class creates a description of
tuples from a list of sequences, the first element of each
subsequence being a column name. It is often convenient to describe
data with such sequences. For example, in an interactive forms-based
application, each column name might be followed by validation
parameters such as data type and allowable length.
TupleDescriptor's purpose is to
allow the creation of DatabaseTuple objects. In
this particular application, no other information about the columns
is needed beyond the names, so the required list of sequences is a
list of singleton lists (meaning lists that have just one element
each), constructed from a list of field names using a list
comprehension.

Created from TupleDescriptor and a tuple such as a
database row, DatabaseTuple is an object whose
elements can be accessed by numeric subscript (like a tuple) or
column-name subscript (like a dictionary). If column names are legal
Python names, you can also access the columns in your
DatabaseTuple as attributes. A purist might object
to this crossover between items and attributes, but
it's a highly pragmatic choice in this case. Python
is nothing if not a highly pragmatic language, so I see nothing wrong
with this convenience.

To demonstrate the utility of DatabaseTuple, the
simple test program in this recipe creates a
TupleDescriptor and uses it to convert each row
retrieved from an SQL query into DatabaseTuple.
Because the sample uses the same field list to build both
TupleDescriptor and the SQL
SELECT statement, it demonstrates how database
code can be parameterized relatively easily.

Alternatively, if you wish to get all the fields (an SQL
SELECT * query), and dynamically get the field
names from the cursor, as previously described in Recipe 7.13, you can do so. Just remove
variable flist, which you don't
need any more, and move the construction of variable
descr to right after the call to the
cursor's execute method, as
follows:

curs.execute(sql)
descr = dtuple.TupleDescriptor(curs.description)

The rest of the recipe can remain unchanged.

A more sophisticated approach,
with functionality similar to
dtuple's and even better
performance, is offered by the Python Database Row Module (also known
as db_row) made freely available by the OPAL
Group. For downloads and information, visit http://opensource.theopalgroup.com/.

Module pysqlite, which handles relational
databases in memory or in files by wrapping the SQLite library, does
not return real tuples from such methods as
fetchall: rather, it returns instances of a
convenience class that wraps tuple and also allows
field access with attribute-access syntax, much like the approaches
mentioned in this recipe.

See Also

Recipe 7.13 for a simpler,
less functionally rich way to convert field names to column numbers;
the dtuple module is at http://www.lyra.org/greg/python/dtuple.py;
OPAL's db_row is at http://opensource.theopalgroup.com/; SQLite,
a fast, lightweight, embedded relational database (http://www.sqlite.org/), and its Python DB
API interface module pysqlite (http://pysqlite.sourceforge.net/).