Python Cookbook

Pretty-Printing the Contents of Database Cursors

Credit: Steve Holden

Problem

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.

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, 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[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(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 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 ...