لطفا منتظر باشید ...
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 odbcIn this case, the cursor's
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( )
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:Module
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
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.
See Also
The mxODBC package, a DB API-compatible interface
to ODBC (http://www.egenix.com//image/library/english/10241_python/mxODBCl);
SQLite, a fast, lightweight embedded relational database (http://www.sqlite.org/), and its Python DB
API interface module pysqlite (http://pysqlite.sourceforge.net/).
