sqlite3, Deep Dive

The database in the standard library: transactions, types, and a whole database in one file you can copy.

Complete 20 notebooks 20 written

Before this guide: Files, Paths and Formats.

The notebooks

Open in Colab runs a notebook in your browser, and Read shows it on GitHub without running it. Every notebook runs on its own, and the answers to its exercises are in a separate notebook, linked as Solutions.

1	Why sqlite3One file, one import, and the CSV you can stop re-reading	Open in Colab Read Solutions
2	Connections and Cursorsconnect, cursor, fetchone against fetchall, and the connection you forgot to close	Open in Colab Read Solutions
3	Tables and QueriesCREATE TABLE, SELECT, JOIN, and the double quotes that became a string	Open in Colab Read Solutions
4	SQL SyntaxWritten order against run order, aggregates, and the date range that lost its last day	Open in Colab Read Solutions
5	JoinsINNER, LEFT, RIGHT, FULL, CROSS and self joins, and the payroll a join counted twice	Open in Colab Read Solutions
6	ParametersQuestion marks against f-strings, and the injection an f-string invites	Open in Colab Read Solutions
7	Row Factoriessqlite3.Row, a dict factory, and rows that answer to a column name	Open in Colab Read Solutions
8	Type AffinityWhy a column declared INTEGER accepted the string, and what STRICT changes	Open in Colab Read Solutions
9	Adapters and Convertersregister_adapter, PARSE_DECLTYPES, and the datetime adapter deprecated in 3.12	Open in Colab Read Solutions
10	Transactionscommit, rollback, and the rows that were gone when you reopened the file	Open in Colab Read Solutions
11	autocommit and isolation_levelLEGACY_TRANSACTION_CONTROL, autocommit=False, and the CREATE TABLE that rollback could not undo	Open in Colab Read Solutions
12	ConstraintsUNIQUE constraint failed, and the foreign key nobody was enforcing	Open in Colab Read Solutions
13	Changing a SchemaALTER TABLE ADD COLUMN, the twelve step table rebuild, and user_version as the migration number you already have	Open in Colab Read Solutions
14	executemanyexecutemany, one transaction, and the lastrowid that came back None	Open in Colab Read Solutions
15	Indexes and Query PlansEXPLAIN QUERY PLAN, SCAN against SEARCH, and the index your WHERE clause cannot use	Open in Colab Read Solutions
16	Full-Text SearchFTS5, bm25 ranking, and the MATCH you cannot use on an ordinary table	Open in Colab Read Solutions
17	Concurrency and WALdatabase is locked, busy_timeout, and the single writer WAL does not remove	Open in Colab Read Solutions
18	Backup and Copyingconn.backup, VACUUM INTO, iterdump, and why copying the file mid-write is not a backup	Open in Colab Read Solutions
19	A Searchable ArchiveA folder of documents into one indexed, searchable, backed-up file	Open in Colab Read Solutions
20	Everyday RequestsA college and a ledger, and the requests a developer answers with joins, aggregates and dates	Open in Colab Read Solutions