Recipe7.15.Pretty-Printing the Contents of Database Cursors

Recipe 7.15. Pretty-Printing the Contents of Database Cursors

Credit: Steve Holden, Farhad Fouladi, Rosendo Martinez, David Berry, Kevin Ryan

Problem

You want to present a query's result with appropriate column headers (and optionally widths), but you do not want to hard-code this information in your program. Indeed, you may not even know the column headers and widths at the time you're writing the code.

Solution

Discovering the column headers and widths dynamically is the most flexible approach, and it gives you code that's highly reusable over many such presentation tasks:

def pp(cursor, data=None, check_row_lengths=False):     if not data:         data = cursor.fetchall( )     names = [  ]     lengths = [  ]     rules = [  ]     for col, field_description in enumerate(cursor.description):         field_name = field_description[0]         names.append(field_name)         field_length = field_description[2] or 12         field_length = max(field_length, len(field_name))         if check_row_lengths:             # double-check field length, if it's unreliable             data_length = max([ len(str(row[col])) for row in data ])             field_length = max(field_length, data_length)         lengths.append(field_length)         rules.append('-' * field_length)     format = " ".join(["%%-%ss" % l for l in lengths])     result = [ format % tuple(names), format % tuple(rules) ]     for row in data:         result.append(format % tuple(row))     return "\n".join(result)

Discussion

Relational databases are often perceived as difficult to use. The Python DB API can make them much easier to use, but if your programs work with several different DB engines, it's sometimes tedious to reconcile the implementation differences between the various modules, and, even more, between the engines they connect to. One of the problems of dealing with databases is presenting the result of a query when you may not know much about the data. This recipe uses the cursor's description attribute to try to provide appropriate headings. The recipe optionally examines each output row to ensure that column widths are adequate.

In some cases, a cursor can yield a solid description of the data it returns, but not all database modules are kind enough to supply cursors that do so. The pretty printer function pp shown in this recipe's Solution takes as its first argument a cursor, on which you have just executed a retrieval operation (generally the execute of an SQL SELECT statement). It also takes an optional argument for the returned data; to use the data for other purposes, retrieve the data from the cursor, typically with fetchall, and pass it in as pp's data argument. The second optional argument tells the pretty printer to determine the column lengths from the data, rather than trusting the cursor's description; checking the data for column lengths can be time-consuming, but is helpful with some RDBMS engines and DB API module combinations, where the widths given by the cursor's description attribute can be inaccurate.

A simple test program shows the value of the second optional argument when a Microsoft Jet database is used through the mxODBC module:

import mx.ODBC.Windows as odbc import dbcp # contains pp function conn = odbc.connect("MyDSN") curs = conn.cursor( ) curs.execute("""SELECT Name, LinkText, Pageset FROM StdPage                 ORDER BY PageSet, Name""") rows = curs.fetchall( ) print "\n\nWithout rowlens:" print dbcp.pp(curs, rows) print "\n\nWith rowlens:" print dbcp.pp(curs, rows, rowlens=1) conn.close( )

In this case, the cursor's description does not include column lengths. The first output shows that the default column length of 12 is too short. The second output corrects this by examining the data:

Without rowlens: Name         LinkText     Pageset ------------ ------------ ------------ ERROR        ERROR: Cannot Locate Page None home         Home None consult      Consulting Activity Std ffx          FactFaxer    Std hardware     Hardware Platforms Std python       Python       Std rates        Rates        Std technol      Technologies Std wcb          WebCallback  Std With rowlens: Name         LinkText                  Pageset ------------ ------------------------- ------------ ERROR        ERROR: Cannot Locate Page None home         Home                      None consult      Consulting Activity       Std ffx          FactFaxer                 Std hardware     Hardware Platforms        Std python       Python                    Std rates        Rates                     Std technol      Technologies              Std wcb          WebCallback               Std

Module pysqlite, which handles relational databases in memory or in files by wrapping the SQLite library, is another example of a DB API module whose cursors' descriptions do not contain reliable values for field lengths. Moreover, pysqlite does not return real tuples from such methods as fetchall: rather, it returns instances of a convenience class which wraps tuple and also allocws field access with attribute access syntax, much like the approaches presented in Recipe 7.14. To deal with such small variations from the DB API specifications, this recipe carefully uses tuple(row), not just row, as the right-hand operand of operator % in the statement result.append(format % tuple(row)). Python's semantics specify that if the right-hand operand is not a tuple, then the left-hand (format string) operand may contain only one format specifier. This recipe uses a tuple as the right-hand operand because the whole point of the recipe is to build and use a format string with many format specifiers, one per field.

This recipe's function is useful during testing, since it lets you easily verify that you are indeed retrieving what you expect from the database. The output is pretty enough to display ad hoc query outputs to users. The function currently makes no attempt to represent null values other than the None the DB API returns, though it could easily be modified to show a null string or some other significant value.

Recipe7.15.Pretty-Printing the Contents of Database Cursors