Print results in MySQL format with Python

Question

What is the easiest way to print the result from MySQL query in the same way MySQL print them in the console using Python? For example I would like to get something like that:

+---------------------+-----------+---------+
| font                | documents | domains |
+---------------------+-----------+---------+
| arial               |     99854 |    5741 |
| georgia             |     52388 |    1955 |
| verdana             |     43219 |    2388 |
| helvetica neue      |     22179 |    1019 |
| helvetica           |     16753 |    1036 |
| lucida grande       |     15431 |     641 |
| tahoma              |     10038 |     594 |
| trebuchet ms        |      8868 |     417 |
| palatino            |      5794 |     177 |
| lucida sans unicode |      3525 |     116 |
| sans-serif          |      2947 |     216 |
| times new roman     |      2554 |     161 |
| proxima-nova        |      2076 |      36 |
| droid sans          |      1773 |      78 |
| calibri             |      1735 |      64 |
| open sans           |      1479 |      60 |
| segoe ui            |      1273 |      57 |
+---------------------+-----------+---------+
17 rows in set (19.43 sec)

Notice: I don't know the max width for each column a priori, and yet I would like to be able to that without going over the table twice. Should I add to the query length() for each column? How does MySQL do it, in order to not impact severely the memory or processing time?

EDIT

I did not think it was relevant to the question but, this is the query I send:

SELECT font.font as font,count(textfont.textid) as documents, count(DISTINCT td.domain) as domains
FROM textfont 
RIGHT JOIN font
ON textfont.fontid = font.fontid
RIGHT JOIN (
        SELECT text.text as text,url.domain as domain, text.textid as textid 
        FROM text 
        RIGHT JOIN url 
        ON text.texturl = url.urlid) as td 
ON textfont.textid = td.textid
WHERE textfont.fontpriority <= 0 
AND textfont.textlen > 100
GROUP BY font.font 
HAVING documents >= 1000 AND domains >= 10
ORDER BY 2 DESC;

And this is the python code I use:

import MySQLdb as mdb

print "%s\t\t\t%s\t\t%s" % ("font","documents","domains")
res = cur.execute(query , (font_priority,text_len,min_texts,min_domains))
for res in cur.fetchall():
    print "%s\t\t\t%d\t\t%d" % (res[0],res[1],res[2])

But this code produces a messy output due to different widths.

Answer 1

There is no need for an external library. The prints out the data with the column names. All lines with the 'columns' variable can be eliminated if you do not need the column names.

sql = "SELECT * FROM someTable"
cursor.execute(sql)
conn.commit()
results = cursor.fetchall()

widths = []
columns = []
tavnit = '|'
separator = '+' 

for cd in cursor.description:
    widths.append(max(cd[2], len(cd[0])))
    columns.append(cd[0])

for w in widths:
    tavnit += " %-"+"%ss |" % (w,)
    separator += '-'*w + '--+'

print(separator)
print(tavnit % tuple(columns))
print(separator)
for row in results:
    print(tavnit % row)
print(separator)

This is the output:

+--------+---------+---------------+------------+------------+
| ip_log | user_id | type_id       | ip_address | time_stamp |
+--------+---------+---------------+------------+------------+
| 227    | 1       | session_login | 10.0.0.2   | 1358760386 |
| 140    | 1       | session_login | 10.0.0.2   | 1358321825 |
| 98     | 1       | session_login | 10.0.0.2   | 1358157588 |
+--------+---------+---------------+------------+------------+

The magic lies in the third column of each cursor.description line (called cd[2] in the code). This column represents the length in characters of the longest value. Thus we size the displayed column as the greater between that and the length of the column header itself (max(cd[2], len(cd[0]))).

Answer 2

Use prettytable

x = PrettyTable(["City name", "Area", "Population", "Annual Rainfall"])
x.set_field_align("City name", "l") # Left align city names
x.set_padding_width(1) # One space between column edges and contents (default)
x.add_row(["Adelaide",1295, 1158259, 600.5])
x.add_row(["Brisbane",5905, 1857594, 1146.4])
x.add_row(["Darwin", 112, 120900, 1714.7])
x.add_row(["Hobart", 1357, 205556, 619.5])
x.add_row(["Sydney", 2058, 4336374, 1214.8])
x.add_row(["Melbourne", 1566, 3806092, 646.9])
x.add_row(["Perth", 5386, 1554769, 869.4])
print x

+-----------+------+------------+-----------------+
| City name | Area | Population | Annual Rainfall |
+-----------+------+------------+-----------------+
| Adelaide  | 1295 |  1158259   |      600.5      |
| Brisbane  | 5905 |  1857594   |      1146.4     |
| Darwin    | 112  |   120900   |      1714.7     |
| Hobart    | 1357 |   205556   |      619.5      |
| Sydney    | 2058 |  4336374   |      1214.8     |
| Melbourne | 1566 |  3806092   |      646.9      |
| Perth     | 5386 |  1554769   |      869.4      |
+-----------+------+------------+-----------------+

Answer 3

Best and easiest way to print MySQL results into MySQL Table format using Python Library tabulate

user@system$ pip install tabulate

Python Code:

import mysql.connector
from tabulate import tabulate

mydb = mysql.connector.connect(
                host="localhost",
                user="root",
                passwd="password",
                database="testDB"
              )

mycursor = mydb.cursor()
mycursor.execute("SELECT emp_name, salary FROM emp_table")
myresult = mycursor.fetchall()


print(tabulate(myresult, headers=['EmpName', 'EmpSalary'], tablefmt='psql'))

Output:

user@system:~$ python python_mysql.py
+------------+-------------+
| EmpName    | EmpSalary   |
|------------+-------------|
| Ram        | 400         |
| Dipankar   | 100         |
| Santhosh   | 200         |
| Nirmal     | 470         |
| Santu      | 340         |
| Shiva      | 100         |
| Karthik    | 500         |
+------------+-------------+

Answer 4

The data is in some list it seems, and are printing the header. Consider some formatting like this:

res = ['trebuchet ms', 8868, 417]
res = ['lucida sans unicode', 3525, 116]

and

print(' {0[0]:20s} {0[1]:10d} {0[2]:10d}'.format(res))

give you

 trebuchet ms               8868        417
 lucida sans unicode        3525        116

Notice the indexing into the list is done inside the string, format only needs to supply the list or tuple.

Alternatively, you could specify widths programatically:

wid1 = 20
wid2 = 10
wid3 = 10
print(' {:{}s} {:{}d} {:{}d}'.format(res[0], wid1, res[1], wid2, res[2], wid3))

which gives identical output as above.

You'd have to adjust the field widths as required and loop through the list for each line of data instead of made up sample lines. Numbers are automatically right justified, string automatically left.

Advantage, to some, is of course that this doesn't rely on any external libraries, and is done with what Python already provides.

Learn More About String Formatting here

Answer 5

You need to do two passes:

1. Calculate the column widths
2. Print the table

So

table = cur.fetchall()
widths = [0]*len(table[0])  # Assuming there is always one row
for row in table:
    widths = [max(w,len(c)) for w,c in zip(widths,row)]

Now you can print the table trivially.

Remember the string.rjust method when printing the numbers.

Update

A more functional way of calculating widths is:

sizetable = [map(len,row) for row in table]
widths = map(max, zip(*sizetable))

Answer 6

I modified dotancohen's answer so only the resulting list of dict is needed as input. This is useful if you already have a library method returning results:

def format_table(self, results:list):
    if not len(results):
        return []
    widths = []
    max_widths = {}
    tavnit = '|'
    separator = '+'
    report = []
    # add col headers length to widths
    for key in results[0].keys():
        max_widths[key] = len(key)
    # add max content lengths to widths
    for row in results:
        for key in row.keys():
            if len(str(row[key])) > max_widths[key]:
                max_widths[key] = len(str(row[key]))
    for key in results[0].keys():
        widths.append(max_widths[key])
    for w in widths:
        tavnit += " %-" + "%s.%ss |" % (w, w)
        separator += '-' * w + '--+'
    # build report
    report.append(separator)
    report.append(tavnit % tuple(results[0].keys()))
    report.append(separator)
    for row in results:
        report.append(tavnit % tuple(row.values()))
    report.append(separator)
    return report