Python SQLite Part 2: CRUD, Joins, Transactions, Indexes

CRUD: Create / Read / Update / Delete — the four operations on persistent data. Joins combine rows from multiple tables. Transactions batch operations atomically. Indexes speed up lookups. The patterns are universal across SQL databases.

Part 1 covered the basics. Part 2 is the bigger picture: how to structure schema, query across tables, and keep things fast.

CRUD as functions

import sqlite3

DB = "shopping.db"

def add(name, price):
  with sqlite3.connect(DB) as conn:
    cur = conn.execute(
      "INSERT INTO products (name, price) VALUES (?, ?)",
      (name, price)
    )
    return cur.lastrowid

def get(product_id):
  with sqlite3.connect(DB) as conn:
    conn.row_factory = sqlite3.Row
    row = conn.execute(
      "SELECT * FROM products WHERE id = ?", (product_id,)
    ).fetchone()
    return dict(row) if row else None

def update(product_id, **fields):
  cols = ", ".join(f"{k} = ?" for k in fields)
  vals = list(fields.values()) + [product_id]
  with sqlite3.connect(DB) as conn:
    cur = conn.execute(f"UPDATE products SET {cols} WHERE id = ?", vals)
    return cur.rowcount

def delete(product_id):
  with sqlite3.connect(DB) as conn:
    cur = conn.execute("DELETE FROM products WHERE id = ?", (product_id,))
    return cur.rowcount

Wrapping each operation in a function keeps SQL out of business logic. Each function does one thing.

Note update is a small DSL — column names are interpolated (safely, since they're code-defined keys, not user input), values are parameterized.

Multiple tables: schema design

A real-world shopping schema has more than one table:

CREATE TABLE customers (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  email TEXT UNIQUE NOT NULL
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  customer_id INTEGER NOT NULL,
  created_at TEXT DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);

CREATE TABLE order_items (
  order_id INTEGER NOT NULL,
  product_id INTEGER NOT NULL,
  quantity INTEGER NOT NULL,
  FOREIGN KEY (order_id) REFERENCES orders(id),
  FOREIGN KEY (product_id) REFERENCES products(id)
);

Three tables. orders references customers via customer_id. order_items links orders to products (many-to-many).

FOREIGN KEY declares a relationship. By default SQLite doesn't enforce it — enable with:

conn.execute("PRAGMA foreign_keys = ON")

Always do this on every connection. Otherwise, INSERT INTO orders (customer_id) VALUES (999) succeeds even if customer 999 doesn't exist.

JOIN: combining tables

cursor.execute("""
  SELECT
    customers.name,
    orders.id AS order_id,
    orders.created_at
  FROM orders
  JOIN customers ON orders.customer_id = customers.id
""")

JOIN matches rows from orders with rows from customers where the join condition holds. Default is INNER JOIN — only matching rows from both sides.

-- LEFT JOIN: all orders, even those without a customer (shouldn't happen with FK)
SELECT * FROM orders LEFT JOIN customers ON ...

-- Three-way join
SELECT
  c.name AS customer,
  p.name AS product,
  oi.quantity
FROM order_items oi
JOIN orders o ON oi.order_id = o.id
JOIN customers c ON o.customer_id = c.id
JOIN products p ON oi.product_id = p.id;

Aliases (oi, o, c, p) keep the query readable. SQL doesn't care about whitespace; format for the human.

Transactions

conn = sqlite3.connect(DB)
try:
  conn.execute("BEGIN")
  conn.execute("UPDATE accounts SET balance = balance - ? WHERE id = ?", (100, sender_id))
  conn.execute("UPDATE accounts SET balance = balance + ? WHERE id = ?", (100, receiver_id))
  conn.commit()
except Exception:
  conn.rollback()
  raise
finally:
  conn.close()

A transaction is a group of operations that must succeed together or fail together. Either both balance updates apply, or neither does.

Without transactions, a crash between the two UPDATEs could leave money missing.

The Python sqlite3 module starts a transaction implicitly on the first DML statement. with conn: commits if successful, rolls back on exception:

with sqlite3.connect(DB) as conn:
  conn.execute("UPDATE accounts SET balance = balance - 100 WHERE id = 1")
  conn.execute("UPDATE accounts SET balance = balance + 100 WHERE id = 2")
# committed if no exception, rolled back otherwise

ACID properties

Transactions guarantee:

• Atomicity — all or nothing.
• Consistency — invariants preserved (FKs, NOT NULL, etc.).
• Isolation — concurrent transactions don't see each other's incomplete state.
• Durability — committed data survives crashes.

SQLite is fully ACID. For most apps, you can rely on this without thinking.

Indexes for performance

CREATE INDEX idx_products_category ON products(category);
CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE UNIQUE INDEX idx_users_email ON users(email);

An index is a sorted lookup structure. For columns you frequently filter or join on, indexes turn O(N) scans into O(log N) seeks.

Without indexes:

SELECT * FROM products WHERE category = 'Electronics';   -- scans all rows

With CREATE INDEX idx_products_category ON products(category); — direct lookup.

The PRIMARY KEY column gets an index automatically. Columns with UNIQUE get one too. For everything else, add manually based on your queries.

Cost: indexes take space and slow writes (each insert/update has to maintain the index). Don't index every column — index what your queries actually filter on.

EXPLAIN QUERY PLAN

cursor.execute("EXPLAIN QUERY PLAN SELECT * FROM products WHERE category = 'Books'")
for row in cursor:
  print(row)

Shows how SQLite will execute the query. Look for SCAN TABLE (slow) vs SEARCH TABLE ... USING INDEX (fast).

If a query is slow, EXPLAIN tells you whether you need an index.

NULL semantics

SELECT * FROM contacts WHERE phone = NULL;     -- returns nothing, ever
SELECT * FROM contacts WHERE phone IS NULL;    -- correct

NULL doesn't equal anything (not even itself). Always use IS NULL / IS NOT NULL.

In Python, None ↔ NULL. Just pass None as the parameter:

cursor.execute("INSERT INTO contacts (name, phone) VALUES (?, ?)", ("Alice", None))

Schema migrations

For real projects, schema evolves:

ALTER TABLE products ADD COLUMN supplier_id INTEGER;

SQLite has limited ALTER TABLE — you can add columns and rename tables, but not drop or rename columns easily (until SQLite 3.25+).

For complex migrations, the SQLite-recommended pattern is:

BEGIN;
CREATE TABLE products_new (...);
INSERT INTO products_new SELECT ... FROM products;
DROP TABLE products;
ALTER TABLE products_new RENAME TO products;
COMMIT;

For long-term maintenance, use Alembic (Python migration tool, works with SQLite).

Date/time storage

SQLite has no native datetime type. Three options:

# 1. ISO string (most readable)
cursor.execute("INSERT INTO events (when_) VALUES (?)", ("2024-03-15 10:30:00",))

# 2. Unix timestamp (most compact)
import time
cursor.execute("INSERT INTO events (when_) VALUES (?)", (int(time.time()),))

# 3. Use Python's datetime adapters
import sqlite3
from datetime import datetime
sqlite3.register_adapter(datetime, lambda d: d.isoformat())
sqlite3.register_converter("DATETIME", lambda b: datetime.fromisoformat(b.decode()))

ISO strings are the safest for cross-tool compatibility. SQLite's date functions work on ISO format directly: WHERE date(when_) = '2024-03-15'.

A complete shopping example

import sqlite3

with sqlite3.connect("shop.db") as conn:
  conn.execute("PRAGMA foreign_keys = ON")
  conn.row_factory = sqlite3.Row

  # Schema
  conn.executescript("""
    CREATE TABLE IF NOT EXISTS customers (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT NOT NULL,
      email TEXT UNIQUE
    );

    CREATE TABLE IF NOT EXISTS products (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT NOT NULL,
      price REAL NOT NULL
    );

    CREATE TABLE IF NOT EXISTS orders (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      customer_id INTEGER REFERENCES customers(id),
      product_id INTEGER REFERENCES products(id),
      quantity INTEGER NOT NULL,
      created_at TEXT DEFAULT CURRENT_TIMESTAMP
    );

    CREATE INDEX IF NOT EXISTS idx_orders_customer ON orders(customer_id);
  """)

  # Query: orders with customer + product names
  rows = conn.execute("""
    SELECT
      c.name AS customer,
      p.name AS product,
      o.quantity,
      p.price,
      o.quantity * p.price AS total,
      o.created_at
    FROM orders o
    JOIN customers c ON o.customer_id = c.id
    JOIN products p ON o.product_id = p.id
    ORDER BY o.created_at DESC
  """).fetchall()

  for r in rows:
    print(f"{r['customer']} bought {r['quantity']}x {r['product']} for ${r['total']:.2f}")

Foreign keys on. Joined query. Aggregate computed in SQL. Index for fast lookup by customer.

Common stumbles

Forgetting PRAGMA foreign_keys = ON. FKs aren't enforced, integrity rots silently.

No indexes on join columns. Slow queries on big tables. Add indexes for foreign keys you join on.

Indexing every column. Slows writes, wastes space. Only index what you filter on.

= NULL instead of IS NULL. Returns nothing, no warning.

Mixing types in a column. SQLite allows it (dynamic typing) but it'll bite you. Stick to declared types.

Long-running transactions. Locks. Keep transactions short.

One big transaction over thousands of inserts. Faster than per-row commits but blocks reads. Batch in chunks of 1000-10000.

Trusting cursor.rowcount for SELECT. It's -1 for SELECT — only meaningful for INSERT/UPDATE/DELETE.

What's next

Lesson 36: SQLAlchemy ORM. Map Python classes to database tables — work with objects, not SQL strings.

Recap

CRUD = Create/Read/Update/Delete; wrap each in a function. Multi-table schemas with FOREIGN KEY (enable with PRAGMA foreign_keys = ON). JOIN to query across tables. Transactions via with conn: for atomic groups; rolled back on exception. Indexes (CREATE INDEX) speed up filters and joins. EXPLAIN QUERY PLAN to debug performance. Always IS NULL, never = NULL. Store dates as ISO strings for portability.

Next lesson: SQLAlchemy ORM.