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.
Download
Copy to clipboard1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | def pp(cursor, data=None, rowlens=0):
d = cursor.description
if not d:
return "#### NO RESULTS ###"
names = []
lengths = []
rules = []
if not data:
t = 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(str(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 frequently seen as difficult to use. The Python DB API can make them much easier, but it's tedious to reconcile the implementation differences of the various modules and the engines they connect to.
A cursor can, in some cases, yield a solid description of the data it returns, but not all modules do so. The pretty printer takes as an argument a cursor, on which you have just executed a retrieval operation. It also takes an optional argument for the returned data: to use the data for other purposes, retrieve it 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 engine and DB API module combinations.
A simple test program shows the value of the second argument when a Microsoft Jet database is used with the mxODBC module:<pre> 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() </pre> The description does not in this case 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:<pre> 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 </pre> This function is very useful during testing, letting you verify easily that you are indeed retrieveing 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 as other than the None the DB API returns, though it could easily be modified to show a null string or some other significant value.
TypeError in case of NUMBER. In the following line:
in case of a NUMBER column, you get a TypeError in len() function.
Thanks, Farhad. As you can see, I've now changed that line to read
which should avoid the error.
Cursor Description. According to standard dbapi2, this it's the cursor description :
.description
For this reason i belive that in the line where you write: for dd in d: # iterate over description l = dd[1]
Must be: for dd in d: # iterate over description l = dd[2]
Rosendo Martinez.
Bug if data argument is not set. Thanks for the example. Reading through the code I noticed that if you do not set the data argument in the calling code then the value of data is left as a None object and results in problems in the statement after if rowlens:
Simple fix,
if not data: data = cursor.fetchall()
SQLite TypeError.
It looks like local "t" is never used. Perhaps line 9 should read "data = cursor.fetchall()"?