Printing a properly formatted SQLite table in Python

Question

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 and run sqlite3 or switch to another shell and run sqlite3. So I wrote up what I expected would give me what I want and it sort of does... This is the part of the script in question:

import sqlite3

conn = sqlite3.connect('stu.db')
c = conn.cursor()

var = 1
while var == 1:

    enquiry = raw_input("What would you like to do?> ")

    enquiry == 'stu db' or enquiry == 'sd':
    c.execute("SELECT * FROM stu")
    conn.commit

In sqlite3 when you run SELECT * FROM stu you get a nicely formatted table with uniform rows and columns. When I run it here I get a long list of the information in parenthesis instead. It looks sort of like this (I didn't print the actual results as that would violate some Federal laws):

[(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)]

I think I know what's going on. Python is just spitting out what the query to sqlite returns, but is there a way to format this information so that it is easily readable?

http://stackoverflow.com/questions/8356501/python-format-tabular-output Try something on these lines. — Vivek, May 05 '16 at 13:19
http://stackoverflow.com/questions/36794110/how-do-i-print-tables-from-an-sqlite-databse-in-python/36795777#36795777 suggests `row_factory` for the column names, which might also help. — ChrisP, May 05 '16 at 13:29

Robᵩ · Accepted Answer · 2016-05-05T14:48:32.967

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

Wonderful! This actually cuts down on the number of lines! Thanks! — Ian Pringle, May 05 '16 at 15:30

score 3 · Answer 2 · answered May 05 '16 at 13:47

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)

Printing a properly formatted SQLite table in Python

2 Answers2

Linked