ActiveState Code

Recipe 81189: Pretty Printing of Database Cursor Contents

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

Discussion

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.

Comments

  1. 1. At 5:28 a.m. on 11 oct 2002, Farhad Fouladi said:

    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.

  2. 2. At 7:31 a.m. on 28 jan 2003, Steve Holden (the author) said:

    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.

  3. 3. At 7:12 a.m. on 10 jun 2003, Rosendo Maritnez said:

    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.

  4. 4. At 5:11 a.m. on 23 feb 2004, David Berry said:

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

  5. 5. At 6:03 p.m. on 23 feb 2004, Kevin Ryan said:

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

Sign in to comment