Recipe7.14.Using dtuple for Flexible Accessto Query Results

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/files/python/mxODBC.html, 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.

Recipe7.14.Using dtuple for Flexible Accessto Query Results