8.11 Pretty-Printing the Contents of Database Cursors

Credit: Steve Holden

8.11.1 Problem

You want to present a query's result with appropriate column headers and widths (optional), but you do not want to hardcode this information, which you may not even know when you're writing the code, in your program.

8.11.2 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, rowlens=0):     d = cursor.description     if not d:         return "#### NO RESULTS ###"     names = []     lengths = []     rules = []     if not data:         data = cursor.fetchall(  )     for dd in d:    # iterate over description         l = dd[1]         if not l:             l = 12             # or default arg ...         l = max(l, len(dd[0])) # Handle long names         names.append(dd[0])         lengths.append(l)     for col in range(len(lengths)):         if rowlens:             rls = [len(row[col]) for row in data if row[col]]             lengths[col] = max([lengths[col]]+rls)         rules.append("-"*lengths[col])     format = " ".join(["%%-%ss" % l for l in lengths])     result = [format % tuple(names)]     result.append(format % tuple(rules))     for row in data:         result.append(format % row)     return "\n".join(result)

8.11.3 Discussion

Relational databases are often perceived as difficult to use. The Python DB API can make them much easier, but if your programs work with several different DB engines, it's sometimes tedious to reconcile the implementation differences between the various modules and 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 and provide appropriate headings and optionally examines each output row to ensure 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 takes as an argument a cursor, on which you have just executed a retrieval operation (such as 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 it from the cursor, typically with fetchall, and pass it in. The second optional argument tells the pretty printer to determine the column lengths from the data rather than from the cursor's description, which is helpful with some RDBMS engines and DB API module combinations.

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 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 contact      Contact Us   Std expertise    Areas of Expertise Std ffx          FactFaxer    Std hardware     Hardware Platforms Std ltree        Learning Tree 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 contact      Contact Us                Std expertise    Areas of Expertise        Std ffx          FactFaxer                 Std hardware     Hardware Platforms        Std ltree        Learning Tree             Std python       Python                    Std rates        Rates                     Std technol      Technologies              Std wcb          WebCallback               Std

This function is useful during testing, as 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.

8.11.4 See Also

The mxODBC package, a DB API-compatible interface to ODBC (http://www.egenix.com/files/python/mxODBC.html).