|I l@ve RuBoard|
8.11 Pretty-Printing the Contents of Database Cursors
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 if not l: l = 12 # or default arg ... l = max(l, len(dd)) # Handle long names names.append(dd) 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)
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.
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).
|I l@ve RuBoard|