Skip to main content

Chapter 16 - SQLite Databases (Python)

Here's a concise walkthrough of the main ideas in Chapter 16, each with a small example.

Why databases and why SQLite​

  • Databases (using SQL) make complex queries easy, compared with scanning text files/spreadsheets (e.g., "all cats between 3–5 kg born before Oct 2023").
  • SQLite is a lightweight, embedded SQL database included with Python (sqlite3 module), storing the whole database in a single file.
  • It's ideal when you'd otherwise use JSON/CSV/spreadsheets but need fast, structured querying.

Spreadsheets vs databases​

  • A table ≈ a spreadsheet; rows = records, columns = fields.
  • SQLite automatically adds a rowid primary key column (unique integer) that doesn't change when rows are deleted/reordered, unlike spreadsheet row numbers.
  • Spreadsheets often serve as formatted forms (fixed size, lots of styling) which don't map well to database tables; databases hold raw, structured data.

SQLite vs other SQL databases​

Key differences:

  • Single file, easy to move/backup.
  • Runs in-process, no server or network.
  • Very small footprint, can live entirely in memory.
  • Loose typing by default (but strict mode exists).
  • No user/permission system (no GRANT/REVOKE).
  • Public domain, no licensing restrictions.

Limitation: not good for massive concurrent writes (e.g. high-traffic websites), but fine for large data sets and many reads.

Creating databases and tables​

Connecting to a database​

Use sqlite3.connect() to open or create a database file, getting a Connection object.

import sqlite3

conn = sqlite3.connect('example.db', isolation_level=None)
# isolation_level=None → autocommit mode, no explicit conn.commit() needed

Call conn.close() when done (also happens automatically at program exit).

Creating a table​

Use CREATE TABLE IF NOT EXISTS and conn.execute(sql_string).

import sqlite3

conn = sqlite3.connect('example.db', isolation_level=None)
conn.execute('''
    CREATE TABLE IF NOT EXISTS cats (
        name TEXT NOT NULL,
        birthdate TEXT,
        fur TEXT,
        weight_kg REAL
    ) STRICT
''')
  • IF NOT EXISTS avoids errors if the table already exists.
  • Convention: uppercase SQL keywords, lowercase table/column names with underscores.

Data types and STRICT mode​

SQLite types:

  • NULL (like None)
  • INTEGER (like int)
  • REAL (like float)
  • TEXT (like str)
  • BLOB (like bytes)

By default, SQLite is flexible: "42" in an INTEGER column becomes 42, "Hello" is stored even if column is INTEGER (type affinity).

STRICT table option (SQLite ≥ 3.37, Python ≥ 3.11) turns on strict mode:

  • Every column must have a type.
  • Wrong-type inserts raise sqlite3.IntegrityError.
  • Auto-casting still happens when possible ("42" → 42), but "Hello" in an INTEGER column fails.

No built-in Boolean type: use INTEGER 0/1. No date/datetime type: store as TEXT in forms like YYYY-MM-DD, YYYY-MM-DD HH:MM:SS.

Check SQLite version:

import sqlite3
print(sqlite3.sqlite_version)  # '3.xx.xx'

name TEXT NOT NULL disallows NULL in name.

Listing tables and columns​

List tables via sqlite_schema:

import sqlite3

conn = sqlite3.connect('example.db', isolation_level=None)
tables = conn.execute(
    'SELECT name FROM sqlite_schema WHERE type="table"'
).fetchall()
print(tables)   # [('cats',)]

Get table schema:

cols = conn.execute('PRAGMA TABLE_INFO(cats)').fetchall()
print(cols)
# e.g. (1, 'birthdate', 'TEXT', 0, None, 0)

Each tuple: (position, name, type, notnull_flag, default_value, is_primary_key).

Do not modify sqlite_schema manually; you can corrupt the database.

CRUD database operations​

CRUD = Create, Read, Update, Delete → INSERT, SELECT, UPDATE, DELETE.

Example statements:

  • INSERT INTO cats VALUES ("Zophie", "2021-01-24", "black", 5.6)
  • SELECT rowid, * FROM cats ORDER BY fur
  • UPDATE cats SET fur = "gray tabby" WHERE rowid = 1
  • DELETE FROM cats WHERE rowid = 1

Most applications are just UIs around CRUD operations: posting = INSERT, editing = UPDATE, deleting = DELETE, browsing = SELECT.

Inserting data​

Simple insert:

import sqlite3

conn = sqlite3.connect('example.db', isolation_level=None)
conn.execute('''
    INSERT INTO cats VALUES ("Zophie", "2021-01-24", "black", 5.6)
''')

Transactions and ACID​

Each INSERT (or any write) is a transaction; SQLite ensures ACID:

  • Atomic: all or nothing.
  • Consistent: respects constraints (NOT NULL, etc.).
  • Isolated: doesn't interfere with other transactions.
  • Durable: results survive crashes/power loss (tested).

Avoiding SQL injection with placeholders​

When inserting user-provided data, use ? placeholders instead of string formatting.

Bad:

cat_name = 'Zophie'
# f-string version can be vulnerable if values come from untrusted input
conn.execute(
    f'INSERT INTO cats VALUES ("{cat_name}", "2021-01-24", "black", 5.6)'
)

Good:

cat_name = 'Zophie'
cat_bday = '2021-01-24'
fur_color = 'black'
cat_weight = 5.6

conn.execute(
    'INSERT INTO cats VALUES (?, ?, ?, ?)',
    [cat_name, cat_bday, fur_color, cat_weight],
)

execute safely escapes values into the query.

Reading data (SELECT)​

Basic read:

import sqlite3

conn = sqlite3.connect('example.db', isolation_level=None)
rows = conn.execute('SELECT * FROM cats').fetchall()
print(rows)  # [('Zophie', '2021-01-24', 'black', 5.6)]
  • execute() returns a Cursor; call .fetchall() to get rows as a list of tuples.
  • SELECT column1, column2 ... FROM table chooses columns; * means all columns (not including rowid; you must request it explicitly).

Example selecting specific columns:

rows = conn.execute('SELECT rowid, name FROM cats').fetchall()
print(rows)  # [(1, 'Zophie')]

Looping over results​

You can iterate directly over conn.execute(...) without calling .fetchall().

import sqlite3

conn = sqlite3.connect('sweigartcats.db', isolation_level=None)

for row in conn.execute('SELECT * FROM cats'):
    print('Row data:', row)
    print(row[0], 'is one of my favorite cats.')

Filtering (WHERE), matching (LIKE / GLOB), sorting (ORDER BY), limiting (LIMIT)​

WHERE clause​

Filter rows:

rows = conn.execute(
    'SELECT * FROM cats WHERE fur = "black"'
).fetchall()

Operators similar to Python: =, !=, <, >, <=, >=, AND, OR, NOT (note = for equality).

Example with combined conditions:

import pprint

matching_cats = conn.execute(
    'SELECT * FROM cats WHERE fur = "black" OR birthdate >= "2024-01-01"'
).fetchall()
pprint.pprint(matching_cats)

Because dates are stored as strings YYYY-MM-DD, comparisons like >= "2024-01-01" work lexicographically as date comparisons.

LIKE with % wildcards (case-insensitive)​

  • % = any substring.
  • name LIKE "%y" – names ending with y.
  • name LIKE "Ja%" – names starting with Ja.
  • name LIKE "%ob%" – names containing ob anywhere.
rows = conn.execute(
    'SELECT rowid, name FROM cats WHERE name LIKE "%y"'
).fetchall()

LIKE is case-insensitive (%ob% matches ob, OB, etc.).

GLOB with * wildcards (case-sensitive)​

GLOB uses shell-style patterns and is case-sensitive.

rows = conn.execute(
    'SELECT rowid, name FROM cats WHERE name GLOB "*m*"'
).fetchall()

Matches only lowercase m.

ORDER BY​

Sort results in the database:

import pprint

rows = conn.execute(
    'SELECT * FROM cats ORDER BY fur'
).fetchall()
pprint.pprint(rows)

Multiple sort keys and directions:

rows = conn.execute(
    'SELECT * FROM cats ORDER BY fur ASC, birthdate DESC'
).fetchall()

If a WHERE exists, ORDER BY must come after it.

LIMIT​

Return only a subset efficiently:

rows = conn.execute(
    'SELECT * FROM cats LIMIT 3'
).fetchall()

With filter and sort:

rows = conn.execute(
    'SELECT * FROM cats WHERE fur="orange" '
    'ORDER BY birthdate LIMIT 4'
).fetchall()

LIMIT must come after WHERE and ORDER BY.

Indexes for faster reads​

Create indexes to speed up WHERE queries on specific columns (trade-off: slightly slower writes, more disk space).

conn.execute('CREATE INDEX idx_name ON cats (name)')
conn.execute('CREATE INDEX idx_birthdate ON cats (birthdate)')

List indexes for a table:

idxs = conn.execute(
    'SELECT name FROM sqlite_schema WHERE type="index" AND tbl_name="cats"'
).fetchall()
print(idxs)  # [('idx_name',), ('idx_birthdate',)]

Drop an index:

conn.execute('DROP INDEX idx_name')

For small tables, indexes usually don't matter; for large/slow queries, they can help.

Updating data (UPDATE)​

Change existing rows using UPDATE ... SET ... WHERE ... and placeholders for values.

Example: change Zophie's fur color:

conn.execute(
    'UPDATE cats SET fur = ? WHERE name = ?',
    ['gray tabby', 'Zophie'],
)

Without a WHERE, all rows would be updated, so always include a condition unless that's intentional.

Deleting data (DELETE)​

Remove rows using DELETE FROM ... WHERE ....

Example: delete one cat by rowid:

conn.execute('DELETE FROM cats WHERE rowid = 1')

Delete all cats with black fur:

conn.execute('DELETE FROM cats WHERE fur = "black"')

Again, omit WHERE only if you truly intend to delete all rows in the table.

Overall idea of the chapter​

Chapter 16 introduces SQLite as a lightweight, file-based database built into Python. The key concepts are: connecting with sqlite3.connect(), creating tables with typed columns and STRICT mode, CRUD operations (INSERT/SELECT/UPDATE/DELETE), filtering with WHERE/LIKE/GLOB, sorting with ORDER BY, limiting with LIMIT, indexes for performance, and always using ? placeholders to prevent SQL injection.