Python comes with an sqlite3 library built-in, nothing to install

[jpivarski]

I used it in this tutorial: https://github.com/jpivarski-talks/2024-07-08-scipy-teen-track/blob/main/site/files/10-shakespeare.ipynb (in JupyterLite, in the students' browsers, so that they didn't need to install Python).

>>> import sqlite3
>>> db = sqlite3.connect(":memory:")
>>> db.execute("CREATE TABLE works(title TEXT, type TEXT, characters INTEGER, year_low INTEGER, year_high INTEGER)")
<sqlite3.Cursor object at 0x1019933c0>
>>> data_in_python = [
...     ["The Sonnets", "poetry", None, 1609, 1609],
...     ["All’s Well that Ends Well", "comedy", 23, 1604, 1605],
...     ["The Tragedy of Antony and Cleopatra", "tragedy", 42, 1606, 1606],
...     ["As You Like It", "comedy", 27, 1599, 1600],
...     ["The Comedy of Errors", "comedy", 18, 1594, 1594],
...     ["The Tragedy of Coriolanus", "tragedy", 30, 1608, 1608],
...     ["Cymbeline", "mixed", 20, 1609, 1610],
...     ["The Tragedy of Hamlet, Prince of Denmark", "tragedy", 30, 1599, 1601],
...     ["The First Part of King Henry the Fourth", "history", 25, 1596, 1597],
...     ["The Second Part of King Henry the Fourth", "history", 25, 1597, 1598],
...     ["The Life of King Henry the Fifth", "history", 30, 1599, 1599],
...     ["The First Part of Henry the Sixth", "history", 40, 1590, 1592],
...     ["The Second Part of King Henry the Sixth", "history", 30, 1590, 1591],
...     ["The Third Part of King Henry the Sixth", "history", 30, 1591, 1591],
...     ["King Henry the Eighth", "history", 30, 1612, 1613],
...     ["The Life and Death of King John", "history", 20, 1596, 1596],
...     ["The Tragedy of Julius Caesar", "tragedy", 40, 1599, 1599],
...     ["The Tragedy of King Lear", "tragedy", 20, 1605, 1606],
...     ["Love’s Labour’s Lost", "comedy", 23, 1594, 1595],
...     ["The Tragedy of Macbeth", "tragedy", 20, 1606, 1606],
...     ["Measure for Measure", "comedy", 20, 1603, 1604],
...     ["The Merchant of Venice", "comedy", 22, 1596, 1597],
...     ["The Merry Wives of Windsor", "comedy", 24, 1597, 1597],
...     ["A Midsummer Night’s Dream", "comedy", 21, 1595, 1596],
...     ["Much Ado About Nothing", "comedy", 23, 1598, 1599],
...     ["The Tragedy of Othello, the Moor of Venice", "tragedy", 21, 1603, 1604],
...     ["Pericles, Prince of Tyre", "late romance", 20, 1607, 1608],
...     ["King Richard the Second", "history", 20, 1595, 1595],
...     ["King Richard the Third", "history", 30, 1592, 1593],
...     ["The Tragedy of Romeo and Juliet", "tragedy", 20, 1595, 1595],
...     ["The Taming of the Shrew", "comedy", 16, 1590, 1592],
...     ["The Tempest", "late romance", 12, 1610, 1611],
...     ["The Life of Timon of Athens", "tragedy", 20, 1605, 1606],
...     ["The Tragedy of Titus Andronicus", "tragedy", 25, 1591, 1592],
...     ["Troilus and Cressida", "mixed", 30, 1601, 1602],
...     ["Twelfth Night; or, What You Will", "comedy", 18, 1601, 1602],
...     ["The Two Gentlemen of Verona", "comedy", 20, 1589, 1593],
...     ["The Two Noble Kinsmen", "comedy", 20, 1613, 1614],
...     ["The Winter’s Tale", "comedy", 21, 1609, 1611],
...     ["A Lover’s Complaint", "poetry", None, 1609, 1609],
...     ["The Passionate Pilgrim", "poetry", None, 1599, 1599],
...     ["The Phoenix and the Turtle", "poetry", None, 1601, 1601],
...     ["The Rape of Lucrece", "poetry", 2, 1594, 1594],
...     ["Venus and Adonis", "poetry", 2, 1593, 1593],
... ]
>>> db.executemany("INSERT INTO works VALUES(?, ?, ?, ?, ?)", data_in_python)
<sqlite3.Cursor object at 0x101993640>
>>> db.commit()

One thing that this interface doesn't have is a good way to display a table. However, Pandas can fill a DataFrame from an SQL query, and Pandas has a nice rendering in both console text and Jupyter HTML. So we can do dummy queries (SELECT * FROM tablename) to visualize small tables.

>>> import pandas as pd
>>> pd.read_sql("SELECT * FROM works", db)
                                         title          type  characters  year_low  year_high
0                                  The Sonnets        poetry         NaN      1609       1609
1                    All’s Well that Ends Well        comedy        23.0      1604       1605
2          The Tragedy of Antony and Cleopatra       tragedy        42.0      1606       1606
3                               As You Like It        comedy        27.0      1599       1600
4                         The Comedy of Errors        comedy        18.0      1594       1594
5                    The Tragedy of Coriolanus       tragedy        30.0      1608       1608
6                                    Cymbeline         mixed        20.0      1609       1610
7     The Tragedy of Hamlet, Prince of Denmark       tragedy        30.0      1599       1601
8      The First Part of King Henry the Fourth       history        25.0      1596       1597
9     The Second Part of King Henry the Fourth       history        25.0      1597       1598
10            The Life of King Henry the Fifth       history        30.0      1599       1599
11           The First Part of Henry the Sixth       history        40.0      1590       1592
12     The Second Part of King Henry the Sixth       history        30.0      1590       1591
13      The Third Part of King Henry the Sixth       history        30.0      1591       1591
14                       King Henry the Eighth       history        30.0      1612       1613
15             The Life and Death of King John       history        20.0      1596       1596
16                The Tragedy of Julius Caesar       tragedy        40.0      1599       1599
17                    The Tragedy of King Lear       tragedy        20.0      1605       1606
18                        Love’s Labour’s Lost        comedy        23.0      1594       1595
19                      The Tragedy of Macbeth       tragedy        20.0      1606       1606
20                         Measure for Measure        comedy        20.0      1603       1604
21                      The Merchant of Venice        comedy        22.0      1596       1597
22                  The Merry Wives of Windsor        comedy        24.0      1597       1597
23                   A Midsummer Night’s Dream        comedy        21.0      1595       1596
24                      Much Ado About Nothing        comedy        23.0      1598       1599
25  The Tragedy of Othello, the Moor of Venice       tragedy        21.0      1603       1604
26                    Pericles, Prince of Tyre  late romance        20.0      1607       1608
27                     King Richard the Second       history        20.0      1595       1595
28                      King Richard the Third       history        30.0      1592       1593
29             The Tragedy of Romeo and Juliet       tragedy        20.0      1595       1595
30                     The Taming of the Shrew        comedy        16.0      1590       1592
31                                 The Tempest  late romance        12.0      1610       1611
32                 The Life of Timon of Athens       tragedy        20.0      1605       1606
33             The Tragedy of Titus Andronicus       tragedy        25.0      1591       1592
34                        Troilus and Cressida         mixed        30.0      1601       1602
35            Twelfth Night; or, What You Will        comedy        18.0      1601       1602
36                 The Two Gentlemen of Verona        comedy        20.0      1589       1593
37                       The Two Noble Kinsmen        comedy        20.0      1613       1614
38                           The Winter’s Tale        comedy        21.0      1609       1611
39                         A Lover’s Complaint        poetry         NaN      1609       1609
40                      The Passionate Pilgrim        poetry         NaN      1599       1599
41                  The Phoenix and the Turtle        poetry         NaN      1601       1601
42                         The Rape of Lucrece        poetry         2.0      1594       1594
43                            Venus and Adonis        poetry         2.0      1593       1593

[jpivarski]

Also works with SQLite files and other connection types (not just in-memory).

[panta-123]

@jpivarski , seems introducing pandas into the flow seems to be little off track from the chapters.
If we can simplify this chapter a little more and expain without pandas into it then that will be best.

[jpivarski]

Pandas is only being used as a way to display it. A little helper function could pretty-print the few rows, but I don't know why that's not built into the sqlite3 library. I didn't find any suggestions on StackOverflow, so I wrote one.

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")

When you call

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

you get

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 ----+-----------+------------+----------+----------