How to print a MySQLdb unicode result in human readable way

Question

This is a question for python programmers. Does anybody know how can I print a MySQLdb query result in human-readable way? Something similar to the tables printed by mysql shell would be great.

Something like:

+-----+---------------------+-----------+
| id  | now()               | aaa       |
+-----+---------------------+-----------+
|  28 | 2012-03-01 14:24:02 | some text |
|  77 | 2012-03-01 14:24:02 | some text |
|  89 | 2012-03-01 14:24:02 | some text |
| 114 | 2012-03-01 14:24:02 | some text |
| 116 | 2012-03-01 14:24:02 | some text |
| 252 | 2012-03-01 14:24:02 | some text |
+-----+---------------------+-----------+

Important note: it must be unicode, as the language is not necessarily English. Thanks in advance!

Example:

MySql part:

create table test.tmp_ids(id integer);
insert into test.tmp_ids values (28),(77),(89),(114),(116),(252);

python code:

cursor = db.cursor()
cursor.execute("select id, now(), 'some text' as aaa from test.tmp_ids")
rows = cursor.fetchall()
print rows <<<<<<<<<<< here instead of just "print rows" I would like to print it as a human-readable table.

Answer 1

There are at least three modules in the Cheeseshop that do text tables:

• asciitable
• texttable
• PrettyTable

On first inspection the first two don't seem to support Unicode out of the box (unutbu's example data raises an Unicode exception). PrettyTable does, though:

import prettytable
data = [                                     
    ('id', 'now()', 'aaa'),
    ('28', '2012-03-01 14:24:02', u'To \N{INFINITY} and beyond'),
    ('77', '2012-03-01 14:24:02', u"All with me's meet"),
    ('89', '2012-03-01 14:24:02', u' that I can fashion fit \N{BLACK SMILING FACE}'),
    ('114', '2012-03-01 14:24:02', u'\N{LATIN CAPITAL LETTER OU}'),
    ('116', '2012-03-01 14:24:02', u'An extra wide unicode: \N{COMBINING CYRILLIC HUNDRED THOUSANDS SIGN}'),
    ('252', '2012-03-01 14:24:02', u'\N{THEREFORE}'),
    ]
t = prettytable.PrettyTable(['id', 'now()', 'aaa'])
for row in data:
    t.add_row(row)
t.printt()
+-----+---------------------+---------------------------+
|  id |        now()        |            aaa            |
+-----+---------------------+---------------------------+
|  28 | 2012-03-01 14:24:02 |      To ∞ and beyond      |
|  77 | 2012-03-01 14:24:02 |     All with me's meet    |
|  89 | 2012-03-01 14:24:02 |  that I can fashion fit ☻ |
| 114 | 2012-03-01 14:24:02 |             Ȣ             |
| 116 | 2012-03-01 14:24:02 |  An extra wide unicode: ҈ |
| 252 | 2012-03-01 14:24:02 |             ∴             |
+-----+---------------------+---------------------------+

You can also output an html table with t.print_html(). pip install prettytable to install it.

Answer 2

I'm not aware of a good solution for arbitrary unicode, since some unicode can be extra narrow or wide and even monospace fonts do not seem to present all unicode as having the same width (see below for an example).

Nevertheless, this variation on George Sakkis's table indentation recipe may suffice:

Save this in a file table.py:

import operator
import itertools
import re
import math
import functools
import logging
logger = logging.getLogger(__name__)

zip_longest = itertools.izip_longest

def tableinfo(rows,
              sep = u'─',
              corner = u'·',
              delim = None,
              corner_delim = None,
              prefix = u'│ ',
              postfix = u' │',
              colsep = u' │ ',
              has_header = False,
              header = None,
              separate_rows = False,
              framed = (True, True),
              separate_empty_lines = True,
              justify = 'right',
              wrapfunc = lambda x:x,
              width = None,
              phantom = None,
              **kw):
    # Based on: http://code.activestate.com/recipes/267662-table-indentation/
    # Author: http://code.activestate.com/recipes/users/2591466/ (George Sakkis)
    def row_wrapper(row):
        try:
            new_rows = [wrapper(item).split('\n') for (item, wrapper)
                        in zip(row, wrapfunc)]
        except TypeError:
            # This happens if wrapfunc is not an iterator
            # TypeError: zip argument #2 must support iteration
            new_rows = [wrapfunc(item).split('\n') for item in row]
        return list(zip_longest(*new_rows, fillvalue = u''))
    if header:
        has_header = True
        rows = itertools.chain(normalize([header]), rows)
    logical_rows = [row_wrapper(row) for row in rows]
    columns = zip(*functools.reduce(operator.add, logical_rows))
    max_width = (
        [max(len(item) for item in column) for column in columns]
        if width is None else width )
    if phantom is not None:
        max_width = [max(x) for x in zip(phantom, max_width)]
    lcorner = corner + sep*(len(prefix)-1) if len(prefix) >= 1 else u''
    rcorner = sep*(len(postfix)-1) + corner if len(postfix) >= 1 else u''

    delim = itertools.repeat(colsep) if delim is None else itertools.cycle(delim)
    corner_delim = (delim_to_corner(delim, sep, corner)
                  if corner_delim is None else itertools.cycle(corner_delim))    
    row_separator = (sep*w for w in max_width)
    row_separator = (lcorner
                     +''.join(list(iterjoin(corner_delim, row_separator)))
                     +rcorner)
    dispatch = {'center':unicode.center, 'right':unicode.rjust, 'left':unicode.ljust}
    try: justify = itertools.cycle([dispatch[item] for item in justify])
    except KeyError: justify = itertools.repeat(dispatch[justify.lower()])
    result = []
    for physical_rows in logical_rows:
        row_result = []
        for row in physical_rows:
            if separate_empty_lines and not ''.join(row).strip():
                row_result.append(row_separator)
            else:
                pieces = [justifier(item, w) for (item, w, justifier)
                        in zip(row, max_width, justify)]
                row_result.append(
                    prefix
                    + u''.join(list(iterjoin(delim, pieces)))
                    + postfix )                
        result.append(u'\n'.join(row_result))
        if has_header and not separate_rows:
            result.insert(1, row_separator)
            has_header = False
    joiner = u'\n'+row_separator+u'\n' if separate_rows else u'\n'
    result = joiner.join(result)
    top_framed, bottom_framed = framed
    if top_framed: result = row_separator+u'\n'+result
    if bottom_framed: result = result+u'\n'+row_separator
    return result, max_width

def iterjoin(sep, it):
    sep = itertools.cycle(sep)
    it = iter(it)
    yield next(it)
    for a, b in zip(it, sep):
        yield b
        yield a

def normalize(rows):
    new_rows = []
    for row in rows:
        new_rows.append([unicode(elt).expandtabs() for elt in row])
    return new_rows

def delim_to_corner(delim, sep, corner):
    for d in delim:
        d = d.replace(u'│', corner).replace(u'|', corner)
        for c in '< >': d = d.replace(c, sep)
        yield d

def wrap_onspace(text, width):
    # written by Mike Brown
    # http://aspn.activestate.com/ASPN/Cookbook/Python/Recipe/148061
    '''
    A word-wrap function that preserves existing line breaks
    and most spaces in the text. Expects that existing line
    breaks are posix newlines (\n).
    '''
    words = iter(text.split(' '))
    line = next(words)
    for word in words:
        contemplated_width = (len(line[line.rfind('\n')+1:]) +
                              len(word.split('\n', 1)[0]))
        if contemplated_width >= width:
            line += '\n'+word
        else:
            line += ' '+word
    return line

def wrap_onspace_strict(text, width):
    '''Similar to wrap_onspace, but enforces the width constraint:
       words longer than width are split.'''
    word_pat = re.compile(r'\S{'+unicode(width)+r',}')
    return wrap_onspace(word_pat.sub(lambda m: wrap_always(m.group(), width), text),
                        width)

def wrap_always(text, width):
    '''A simple word-wrap function that wraps text on exactly width characters.
       It doesn\'t split the text in words.'''
    return '\n'.join( text[width*i:width*(i+1)]
                       for i in xrange(int(math.ceil(1.*len(text)/width))) )

def onspace(width):
    return functools.partial(wrap_onspace, width = width)

def strict(width):
    return functools.partial(wrap_onspace_strict, width = width)

def always(width):
    return functools.partial(wrap_always, width = width)

def table(rows,
          sep = u'─',
          corner = u'·',
          delim = None,
          corner_delim = None,
          prefix = u'│ ',
          postfix = u' │',
          has_header = False,
          header = None,
          separate_rows = False,
          framed = (True, True),
          separate_empty_lines = True,
          justify = 'right',
          wrapfunc = lambda x:x,
          width = None,
          **kw):
    '''
    ·──────────────────────·─────────────────────────────────────────────────────────·
    │                 rows │ A sequence of sequences of items, one sequence per row. │
    ·──────────────────────·─────────────────────────────────────────────────────────·
    │               framed │ row separator on top and bottom                         │
    ·──────────────────────·─────────────────────────────────────────────────────────·
    │                  sep │ Character to be used for the row separator line (if     │
    │                      │ has_header==True or separate_rows==True).               │
    ·──────────────────────·─────────────────────────────────────────────────────────·
    │                delim │ A sequence of column delimiters. The delimiters are     │
    │                      │ repeated in a cycle                                     │
    ·──────────────────────·─────────────────────────────────────────────────────────·
    │         corner_delim │ A sequence of column delimiters used in row separators, │
    │                      │ repeated in a cycle.                                    │
    ·──────────────────────·─────────────────────────────────────────────────────────·
    │               prefix │ A string prepended to each printed row.                 │
    ·──────────────────────·─────────────────────────────────────────────────────────·
    │              postfix │ A string appended to each printed row.                  │
    ·──────────────────────·─────────────────────────────────────────────────────────·
    │           has_header │ True if there is a row separator between the first and  │
    │                      │ second row                                              │
    ·──────────────────────·─────────────────────────────────────────────────────────·
    │        separate_rows │ True if all rows are to be separated                    │
    ·──────────────────────·─────────────────────────────────────────────────────────·
    │               framed │ True if top (and/or bottom) have a row separator        │
    ·──────────────────────·─────────────────────────────────────────────────────────·
    │ separate_empty_lines │ replace empty lines with a row separator                │
    ·──────────────────────·─────────────────────────────────────────────────────────·
    │              justify │ Determines how the data is justified in each column.    │
    │                      │ Valid values are 'left','right' and 'center', or a list │
    │                      │ of such values (one element per column).                │
    ·──────────────────────·─────────────────────────────────────────────────────────·
    │             wrapfunc │ A function f(text), or list of functions, for wrapping  │
    │                      │ text; each element in the table is first wrapped by     │
    │                      │ this function. If wrapfunc is a list of functions, then │
    │                      │ table will apply one function per column.               │
    ·──────────────────────·─────────────────────────────────────────────────────────·
    │               width  │ A list of column widths. If None, the widths will be    │
    │                      │ calculated.                                             │
    ·──────────────────────·─────────────────────────────────────────────────────────·
    '''
    result, max_width = tableinfo(
        normalize(rows), sep = sep, corner = corner, delim = delim,
        corner_delim = corner_delim,
        prefix = prefix, postfix = postfix, has_header = has_header, header = header,
        separate_rows = separate_rows, framed = framed,
        separate_empty_lines = separate_empty_lines, justify = justify,
        wrapfunc = wrapfunc, width = width)
    return result

def ascii_table(rows,
          sep = '-',
          corner = '+',
          delim = [' | '],
          corner_delim = None,
          prefix = u'| ',
          postfix = u' |',
          has_header = False,
          header = None,
          separate_rows = False,
          framed = (True, True),
          separate_empty_lines = True,
          justify = 'right',
          wrapfunc = lambda x:x,
          width = None,
          **kw):
    result, max_width = tableinfo(
        normalize(rows), sep = sep, corner = corner, delim = delim,
        corner_delim = corner_delim,
        prefix = prefix, postfix = postfix, has_header = has_header, header = header,
        separate_rows = separate_rows, framed = framed,
        separate_empty_lines = separate_empty_lines, justify = justify,
        wrapfunc = wrapfunc, width = width)
    return result

Then you can use it like this:

import table

data = [
    ('id', 'now()', 'aaa'),
    ('28', '2012-03-01 14:24:02', u'To \N{INFINITY} and beyond'),
    ('77', '2012-03-01 14:24:02', u"All with me's meet"),
    ('89', '2012-03-01 14:24:02', u' that I can fashion fit \N{BLACK SMILING FACE}'),
    ('114', '2012-03-01 14:24:02', u'\N{LATIN CAPITAL LETTER OU}'),
    ('116', '2012-03-01 14:24:02', u'An extra wide unicode: \N{COMBINING CYRILLIC HUNDRED THOUSANDS SIGN}'),
    ('252', '2012-03-01 14:24:02', u'\N{THEREFORE}'),
    ]

print(table.ascii_table(data, has_header = True))

which yields

+-----+---------------------+---------------------------+
|  id |               now() |                       aaa |
+-----+---------------------+---------------------------+
|  28 | 2012-03-01 14:24:02 |           To ∞ and beyond |
|  77 | 2012-03-01 14:24:02 |        All with me's meet |
|  89 | 2012-03-01 14:24:02 |  that I can fashion fit ☻ |
| 114 | 2012-03-01 14:24:02 |                         Ȣ |
| 116 | 2012-03-01 14:24:02 |  An extra wide unicode: ҈ |
| 252 | 2012-03-01 14:24:02 |                         ∴ |
+-----+---------------------+---------------------------+

Notice that the lines corresponding to id = 114 and 116 are not quite aligned with the others.

(Read the docstring for table.table for more info on the parameters available.)

Answer 3

Use mako template to create the html and then use any text based browser to display that html.

I used that and its working fine. We have to display result on console in proper format. I used links in unix to generate the output. I can use table formatting of HTML to display my data in proper format.

Try this

a = '''<%
    rows = [[v for v in range(0,10)] for row in range(0,10)]
%>
<table border="1">
    % for row in rows:
        ${makerow(row)}
    % endfor
</table>

<%def name="makerow(row)">
    <tr>
        % for name in row:
            <td>${name}</td>
        % endfor
    </tr>
</%def>
</td>
'''
from mako.template import Template

x = open('/tmp/test.html', 'w')
x.write(Template(a).render())
x.close()

import commands
a, b = commands.getstatusoutput('links -dump 1 /tmp/test.html')
print b

You will get the output like this

   +---------------------------------------+
   | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
   |---+---+---+---+---+---+---+---+---+---|
   | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
   |---+---+---+---+---+---+---+---+---+---|
   | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
   |---+---+---+---+---+---+---+---+---+---|
   | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
   |---+---+---+---+---+---+---+---+---+---|
   | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
   |---+---+---+---+---+---+---+---+---+---|
   | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
   |---+---+---+---+---+---+---+---+---+---|
   | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
   |---+---+---+---+---+---+---+---+---+---|
   | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
   |---+---+---+---+---+---+---+---+---+---|
   | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
   |---+---+---+---+---+---+---+---+---+---|
   | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
   +---------------------------------------+