Credit: Steve Holden
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.
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 "
".join(result)
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 " Without rowlens:" print dbcp.pp(curs, rows) print " With 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.
The mxODBC package, a DB API-compatible interface to ODBC (http://www.egenix.com/files/python/mxODBC.html).