这是python程序员的一道题。 有人知道如何以人类可读的方式打印 MySQLdb 查询结果吗？ 类似于 mysql shell 打印的表的东西会很棒。

类似于:

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

重要说明:它必须是 unicode，因为语言不一定是英语。 提前致谢!

示例:

MySql部分:

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

python 代码:

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.

最佳答案

我不知道针对任意 unicode 的好的解决方案，因为一些 unicode 可以特别窄或特别宽，甚至等宽字体似乎也不会将所有 unicode 呈现为具有相同的宽度(请参见下面的示例)。

然而，George Sakkis's table indentation recipe 上的这个变体可能就足够了:

将其保存在文件 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

然后你可以像这样使用它:

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

产生

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

请注意，对应于 id = 114 和 116 的行与其他行并不完全对齐。

(阅读 table.table 的文档字符串以获取有关可用参数的更多信息。)

关于python - 如何以人类可读的方式打印 MySQLdb unicode 结果，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/9516247/