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