Greg Stein's dtuple module deserves wider recognition. This recipe shows you how to use it with a simple list of database field names.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24
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, row or 0) print "Mapping: Name: %s Number: %d" % (row["Name"], row["Num"] or 0) conn.close()
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 makes use of the query results becomes opaque and difficult to maintain. Greg Stein's dtuple module -- available from http://www.lyra.org/greg/python/dtuple.py -- really helps by defining two classes, TupleDescriptor and DatabaseTuple. The TupleDescriptor 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 datatype and allowable length. The TupleDescriptor's purpose is to allow the creation of DatabaseTuple objects (see below). In this particular application no other information is needed about the columns, so the required list of sequences is constructed from a list of field names using a list comprehension. Created from a TupleDescriptor and a tuple such as a database row, the 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. To demonstrate the utility of the DatabaseTuple the simple test program above creates a TupleDescriptor and then uses it to convert each row retrieved from a SQL query into a DatabaseTuple. Because the sample uses the same field list to build both the TupleDescriptor and the SQL SELECT statement, it demonstrates how database code can be relatively easily parameterized. The program gives the results shown below: <pre> SELECT Name, Num, LinkText FROM StdPage WHERE PageSet='Std' AND Num<25 ORDER BY PageSet, Num Attribute: Name: consult Number: 10 Subscript: Name: consult Number: 10 Mapping: Name: consult Number: 10 Attribute: Name: python Number: 12 Subscript: Name: python Number: 12 Mapping: Name: python Number: 12 Attribute: Name: expertise Number: 20 Subscript: Name: expertise Number: 20 Mapping: Name: expertise Number: 20 </pre>