Python Cookbook

Pretty-Printing the Contents of Database Cursors

Credit: Steve Holden

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.

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)

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.

See Also

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