Generating Ascii tables using python

By Andreas Wilhelm Apr 2, 2013

Today I would like to show you how to use masks to format your output data. Since I often have to monitor some log changes or processes I decided to show you how to monitor MySQL processes using python.

Caution! The following script uses an old syntax to format output data. The example works also with Python 3, but there is a new way to do this. For more details see the Format String Syntax page of the python 3 documentation.

#!/usr/bin/python
import MySQLdb as mdl;
import sys;
mdo = None;

try:
	# Setup the database connection ...
	mdo = mdl.connect(host='host', port=3306, user='username', passwd='secret', db='database');

	# ... and fetch a cursor.
	cur = mdo.cursor();

	# Initialize the cursor with the process list ...
	cur.execute("SHOW FULL PROCESSLIST");

	# ... and fetch the results.
	rows = cur.fetchall();

	# Setup the mask to format the process list ...
	procMask = "| %8.8s | %16.16s | %16.16s | %16.16s | %16.16s | %16.16s |\n";

	# ... and initialize the ascii process table.
	asciiTable = procMask % ("Id", "User", "Host", "Database", "Command", "Time");
	asciiTable += "".rjust(len(asciiTable) - 1, '-') + "\n";

	# Add an entry to the ascii process table.
	for proc in rows:
		asciiTable += procMask % (proc[0], proc[1], proc[2], proc[3], proc[4], proc[5]);

	print asciiTable;
except mdl.Error, e:
	print "Error %d: %s" % (e.args[0], e.args[1]);
	sys.exit(1);
finally:
	if mdo:
		mdo.close();

I used this old formatting style, because I could not figure out how to limit the length of a string. I was just able to pad the output to a certain length, but if the input string was longer then the specified width, python did not care. So I would be most pleased to have your hints.

asciiTable += "| {0:>8} | {1:>16} | {2:>16} | {3:>16} | {4:>16} | {5:>16} |\n".format(
	proc[0], proc[1], proc[2], proc[3], proc[4], proc[5]);

Also Python 3 uses a new formatting style, there is one advantage of this example. This fashion to format output using a mask is also used in many other languages like C/C++ and PHP, it is very easy to adopt this script. I would be happy to see some of your own implementations in your preferred programming language.

So until next time, happy coding!


is a Computer Science MSc. interested in hardware hacking, embedded Linux, compilers, etc.