Mastering SQLite3 in Python

SQLite is embedded, zero-configuration, and incredibly powerful for most applications.

Setting Up

import sqlite3

conn = sqlite3.connect('myapp.db')
conn.row_factory = sqlite3.Row  # Return rows as dicts
cursor = conn.cursor()

Creating Tables

cursor.execute("""
    CREATE TABLE IF NOT EXISTS posts (
        id          INTEGER PRIMARY KEY AUTOINCREMENT,
        title       TEXT    NOT NULL,
        content     TEXT,
        created_at  TEXT    DEFAULT (datetime('now'))
    )
""")
conn.commit()

CRUD Operations

# Create
cursor.execute(
    "INSERT INTO posts (title, content) VALUES (?, ?)",
    ("My Post", "Hello World")
)
conn.commit()

# Read
posts = cursor.execute("SELECT * FROM posts ORDER BY id DESC").fetchall()
for post in posts:
    print(dict(post))

# Update
cursor.execute(
    "UPDATE posts SET title=? WHERE id=?",
    ("Updated Title", 1)
)

# Delete
cursor.execute("DELETE FROM posts WHERE id=?", (1,))
conn.commit()

Performance Optimisation

# Enable WAL mode for concurrent reads
conn.execute("PRAGMA journal_mode=WAL")

# Enable foreign keys
conn.execute("PRAGMA foreign_keys=ON")

# Add indexes for frequent queries
cursor.execute("CREATE INDEX IF NOT EXISTS idx_posts_created ON posts(created_at)")

Context Manager Pattern

from contextlib import contextmanager

@contextmanager
def get_db():
    conn = sqlite3.connect('app.db')
    conn.row_factory = sqlite3.Row
    try:
        yield conn
        conn.commit()
    except Exception:
        conn.rollback()
        raise
    finally:
        conn.close()

# Usage
with get_db() as db:
    db.execute("INSERT INTO posts (title) VALUES (?)", ("New Post",))

When to Use SQLite vs PostgreSQL

Scenario SQLite PostgreSQL
Single server ✅ ✅
Multiple servers ❌ ✅
< 100k daily users ✅ ✅
High write concurrency ❌ ✅
Embedded/mobile ✅ ❌