Printing A Properly Formatted SQLite Table In Python

I've written a Python script to add rows to my tables. I decided it would be nice if I could also view my tables with the same script instead of having to either quit the script an

Solution 1:

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 sqlite3
import pandas as pd

conn = 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 way
print list(c.execute("SELECT * FROM stu"))

# Pretty way
print 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 Partner
0  1234567890  John Doe      3852  DEGR-AA   4    None
1  1234567890  John Doe      3852  DEGR-AA   4    None
2  1234567890  John Doe      3852  DEGR-AA   4    None
3  1234567890  John Doe      3852  DEGR-AA   4    None
4  1234567890  John Doe      3852  DEGR-AA   4    None
5  1234567890  John Doe      3852  DEGR-AA   4    None
6  1234567890  John Doe      3852  DEGR-AA   4    None
7  1234567890  John Doe      3852  DEGR-AA   4    None
8  1234567890  John Doe      3852  DEGR-AA   4    None
9  1234567890  John Doe      3852  DEGR-AA   4    None

Solution 2:

The way I've done this in the past is to simply use a pandas data frame.

import pandas as pd

data = [(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)