Printing a properly formatted SQLite table in Python
You can use pandas for this:
print pd.read_sql_query("SELECT * FROM stu", conn)Sample program (python 2.7.6, pandas 0.18.0):
import sqlite3import pandas as pdconn = sqlite3.connect(':memory:')c = conn.cursor()c.execute('create table stu ( ID, Name, ShoeSize, Course, IQ, Partner )')conn.commit()c.executemany('insert into stu VALUES (?, ?, ?, ?, ?, ?)', [(1234567890, u'John Doe', 3852, u'DEGR-AA', 4, None), (1234567890, u'John Doe', 3852, u'DEGR-AA', 4, None), (1234567890, u'John Doe', 3852, u'DEGR-AA', 4, None), (1234567890, u'John Doe', 3852, u'DEGR-AA', 4, None), (1234567890, u'John Doe', 3852, u'DEGR-AA', 4, None), (1234567890, u'John Doe', 3852, u'DEGR-AA', 4, None), (1234567890, u'John Doe', 3852, u'DEGR-AA', 4, None), (1234567890, u'John Doe', 3852, u'DEGR-AA', 4, None), (1234567890, u'John Doe', 3852, u'DEGR-AA', 4, None), (1234567890, u'John Doe', 3852, u'DEGR-AA', 4, None)])conn.commit()# Ugly wayprint list(c.execute("SELECT * FROM stu"))# Pretty wayprint pd.read_sql_query("SELECT * FROM stu", conn)Result, which includes both the ugly and the pretty output:
[(1234567890, u'John Doe', 3852, u'DEGR-AA', 4, None), (1234567890, u'John Doe', 3852, u'DEGR-AA', 4, None), (1234567890, u'John Doe', 3852, u'DEGR-AA', 4, None), (1234567890, u'John Doe', 3852, u'DEGR-AA', 4, None), (1234567890, u'John Doe', 3852, u'DEGR-AA', 4, None), (1234567890, u'John Doe', 3852, u'DEGR-AA', 4, None), (1234567890, u'John Doe', 3852, u'DEGR-AA', 4, None), (1234567890, u'John Doe', 3852, u'DEGR-AA', 4, None), (1234567890, u'John Doe', 3852, u'DEGR-AA', 4, None), (1234567890, u'John Doe', 3852, u'DEGR-AA', 4, None)] ID Name ShoeSize Course IQ Partner0 1234567890 John Doe 3852 DEGR-AA 4 None1 1234567890 John Doe 3852 DEGR-AA 4 None2 1234567890 John Doe 3852 DEGR-AA 4 None3 1234567890 John Doe 3852 DEGR-AA 4 None4 1234567890 John Doe 3852 DEGR-AA 4 None5 1234567890 John Doe 3852 DEGR-AA 4 None6 1234567890 John Doe 3852 DEGR-AA 4 None7 1234567890 John Doe 3852 DEGR-AA 4 None8 1234567890 John Doe 3852 DEGR-AA 4 None9 1234567890 John Doe 3852 DEGR-AA 4 None
The way I've done this in the past is to simply use a pandas data frame.
import pandas as pddata = [(1234567890, u'John Doe', 3852, u'DEGR-AA', 4, None), (1234567890, u'John Doe', 3852, u'DEGR-AA', 4, None),(1234567890, u'John Doe', 3852, u'DEGR-AA', 4, None),(1234567890, u'John Doe', 3852, u'DEGR-AA', 4, None),(1234567890, u'John Doe', 3852, u'DEGR-AA', 4, None),(1234567890, u'John Doe', 3852, u'DEGR-AA', 4, None),(1234567890, u'John Doe', 3852, u'DEGR-AA', 4, None),(1234567890, u'John Doe', 3852, u'DEGR-AA', 4, None),(1234567890, u'John Doe', 3852, u'DEGR-AA', 4, None),(1234567890, u'John Doe', 3852, u'DEGR-AA', 4, None)]pd.DataFrame(data)