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?

stackoverflow.com/questions/8356501/… Try something on these lines. — Vivek
– Vivek, Commented May 5, 2016 at 13:19
stackoverflow.com/questions/36794110/… suggests row_factory for the column names, which might also help. — ChrisP
– ChrisP, Commented May 5, 2016 at 13:29

Robᵩ · Accepted Answer · 2016-05-05 14:48:32Z

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!

mark s. · Accepted Answer · 2016-05-05 13:47:44Z

3

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)

answered May 5, 2016 at 13:47

mark s.

6562 gold badges7 silver badges14 bronze badges

Comments

Jim Pivarski · Accepted Answer · 2025-01-16 06:29:01Z

If you don't want to use Pandas (another dependency; sqlite3 is purely standard library), then you might want to consider a function like this:

def print_cursor(
    cursor: sqlite3.Cursor,
    *,
    first: int = 5,
    last: int = 5,
    show_header: bool = True,
    show_count: bool = True,
    total_width: int = 80,
    max_width: int = 15,
    stream = sys.stdout,
):
    # name of each column
    header = [x[0] for x in cursor.description]
    # width of the repr of each column
    widths = [min(len(x), max_width) for x in header]

    # iterate over the whole Cursor, but only keep `first + 2*last` rows in memory
    first_rows = []
    last_rows = []
    row_count = 0
    for row in cursor:
        if len(first_rows) < first + last:
            first_rows.append(row)
        last_rows.append(row)
        if len(last_rows) > last:
            last_rows = last_rows[1:]
        row_count += 1

    if row_count <= first + last:
        # if the number of rows <= `first + last`, show them all
        rows = first_rows
    else:
        # otherwise, show the `first`, an ellipsis row, and then `last`
        rows = first_rows[:first] + [[...] * len(header)] + last_rows

    # represent rows with mutable lists so that we can replace them with reprs
    rows = [list(x) for x in rows]

    align = [">"] * len(header)
    for row in rows:
        assert len(row) == len(header), f"{len(row)} columns != {len(header)} columns"
        for i, cell in enumerate(row):
            # if all values are str or bytes (ignoring None), left-align
            if cell != ... and isinstance(cell, (str, bytes)) and cell is not None:
                align[i] = "<"
            # replace data with their repr strings (except ellipsis)
            row[i] = "..." if cell == ... else repr(cell)
            # identify the maximum (string) width of each column, up to max_width
            widths[i] = min(max(widths[i], len(row[i])), max_width)

    # if the table is too wide, replace the last column with ellipsis
    if sum(widths) + (len(widths) - 1) * 2 > total_width:
        header[-1] = "..."
        widths[-1] = 3
        for row in rows:
            row[-1] = "..."

    # if the table is still too wide, remove columns
    while sum(widths) + (len(widths) - 1) * 2 > total_width and len(header) > 1:
        del header[-2]
        del widths[-2]
        for row in rows:
            del row[-2]

    # prepare a format string for each line of text
    formatter = " | ".join(f"{{:{a}{w}s}}" for a, w in zip(align, widths))
    # prepare the horizontal line between header and data
    header_separator = "-+-".join("-" * w for w in widths)

    if show_header:
        # print the table column names and a horizontal line under it
        stream.write(formatter.format(*[x[:w] for x, w in zip(header, widths)]) + "\n")
        stream.write(header_separator + "\n")
    for row in rows:
        # print each table row
        stream.write(formatter.format(*[x[:w] for x, w in zip(row, widths)]) + "\n")
    if show_count:
        # print the number of rows in another horizontal line
        count = f"--- {row_count} rows ---"
        stream.write(count + header_separator[len(count) :] + "\n")

I wish sqlite3 had something like this built-in, and maybe if enough people need it, maybe one can be contributed. This would be nice as a method on cursor.

For a call like

print_cursor(db.execute("SELECT * FROM compleat_works"))

the output looks like

title           | type      | characters | year_low | year_high
----------------+-----------+------------+----------+----------
'The Sonnets'   | 'poetry'  |       None |     1609 |      1609
'All’s Well tha | 'comedy'  |         23 |     1604 |      1605
'The Tragedy of | 'tragedy' |         42 |     1606 |      1606
'As You Like It | 'comedy'  |         27 |     1599 |      1600
'The Comedy of  | 'comedy'  |         18 |     1594 |      1594
...             | ...       |        ... |      ... |       ...
'A Lover’s Comp | 'poetry'  |       None |     1609 |      1609
'The Passionate | 'poetry'  |       None |     1599 |      1599
'The Phoenix an | 'poetry'  |       None |     1601 |      1601
'The Rape of Lu | 'poetry'  |          2 |     1594 |      1594
'Venus and Adon | 'poetry'  |          2 |     1593 |      1593
--- 44 rows ----+-----------+------------+----------+----------

Collectives™ on Stack Overflow

Printing a properly formatted SQLite table in Python

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related