8.10 Using dtuple for Flexible Access to Query Results

Credit: Steve Holden

8.10.1 Problem

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

8.10.2 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(  )

8.10.3 Discussion

Novice Python programmers are often deterred from using databases because query results are presented by DB API-compliant modules as a list of tuples. Since these 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.

The TupleDescriptor class creates a description of tuples from a list of sequences, the first element of which is a column name. It is often convenient to describe data with such sequences. For example, in an interactive, or 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 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, and 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 a 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.

8.10.4 See Also

See Recipe 8.9 for a simple way to convert field names to column numbers; the dtuple module (http://www.lyra.org/greg/python/dtuple.py).