How to print MySQLdb unicode result in human-friendly way

Question

This is a question for python programmers. Does anyone know how I can print the result of a MySQLdb query with human rights? Something similar to the tables printed by the mysql shell would be great.

Sort of:

+-----+---------------------+-----------+ | 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, since 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

I don’t know a good solution for arbitrary Unicode, as some Unicode can be too narrow or wide, and even monospaced fonts do not seem to represent all Unicode as having the same width (see example below).

However, this variation on the George Sakkis table descriptor recipe may suffice:

Save this in table.py file:

 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 as follows:

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

what gives

 +-----+---------------------+---------------------------+ | id | now() | aaa | +-----+---------------------+---------------------------+ | 28 | 2012-03-01 14:24:02 | To ∞ and beyond | | 77 | 2012-03-01 14:24:02 | All with me 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 | ∴ | +-----+---------------------+---------------------------+ 

Note that the lines corresponding to id = 114 and 116 do not quite match the others.

(Read the docstring for table.table for more information on the options available.)

Answer 2

Cheeseshop has at least three modules that execute text tables:

• asciitable
• texttable
• Prettytable

On first inspection, the first two don't seem to support Unicode out of the box (the unutbu example data raises the 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 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 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 the html table with t.print_html() . pip install prettytable to install it.

Answer 3

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

I used this and its work is excellent. We must display the result on the console in the correct format. I used links on unix to generate output. I can use HTML table formatting to display my data in the proper format.

try it

 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 result as follows

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