Welcome, guest | Sign In | My Account | Store | Cart

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.

Python, 27 lines
 1
 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.

6 comments

Farhad Fouladi 19 years, 2 months ago  # | flag

TypeError in case of NUMBER. In the following line:

rls = [len(row[col]) for row in data if row[col]]

in case of a NUMBER column, you get a TypeError in len() function.

Steve Holden (author) 18 years, 10 months ago  # | flag

Thanks, Farhad. As you can see, I've now changed that line to read

rls = [len(str(row[col])) for row in data if row[col]]

which should avoid the error.

Rosendo Maritnez 18 years, 6 months ago  # | flag

Cursor Description. According to standard dbapi2, this it's the cursor description :

.description

        This read-only attribute is a sequence of 7-item
        sequences.  Each of these sequences contains information
        describing one result column: (name, type_code,
        display_size, internal_size, precision, scale,
        null_ok). The first two items (name and type_code) are
        mandatory, the other five are optional and must be set to
        None if meaningfull values are not provided.

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.

David Berry 17 years, 9 months ago  # | flag

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()

Kevin Ryan 17 years, 9 months ago  # | flag

SQLite TypeError.

Hi -

Not sure if anyone ran into this or not.  I've just acquired a copy
of SQLite, and wanted to try out the "pretty" version for printing
columns w/ rowlens=1.  When I ran it, I got:

-> TypeError: not enough arguments for format string

After some debugging, I think SQLite returns a string instead of a
tuple at the line:

-> result.append(format % row)

at the very end of the script.  Simply changing this to:

-> result.append(format % tuple(row))

seemed to clean it up, though, and it now works :)
Jonathan Rogers 11 years, 1 month ago  # | flag

It looks like local "t" is never used. Perhaps line 9 should read "data = cursor.fetchall()"?

Created by Steve Holden on Thu, 11 Oct 2001 (PSF)
Python recipes (4591)
Steve Holden's recipes (3)
Python Cookbook Edition 2 (117)
Python Cookbook Edition 1 (103)

Required Modules

  • (none specified)

Other Information and Tasks