Python SQLite — Embedded Database Programming

SQLite is a serverless, zero-configuration database built into Python. Perfect for local storage, testing, and small applications.

Learning Objectives

• Create and connect to SQLite databases
• Perform CRUD operations safely
• Use transactions and context managers
• Design efficient schemas

Connecting and Creating Tables

import sqlite3

conn = sqlite3.connect('app.db')
cursor = conn.cursor()

cursor.execute('''
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        email TEXT UNIQUE NOT NULL,
        age INTEGER,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )
''')
conn.commit()

CRUD Operations

# INSERT
cursor.execute(
    "INSERT INTO users (name, email, age) VALUES (?, ?, ?)",
    ("Alice", "alice@email.com", 30)
)
conn.commit()

# INSERT many
users = [("Bob", "bob@email.com", 25), ("Charlie", "charlie@email.com", 35)]
cursor.executemany(
    "INSERT INTO users (name, email, age) VALUES (?, ?, ?)",
    users
)
conn.commit()

# SELECT
cursor.execute("SELECT * FROM users WHERE age > ?", (20,))
rows = cursor.fetchall()
for row in rows:
    print(row)

# UPDATE
cursor.execute("UPDATE users SET age = ? WHERE name = ?", (31, "Alice"))
conn.commit()

# DELETE
cursor.execute("DELETE FROM users WHERE name = ?", ("Charlie",))
conn.commit()

Context Manager Pattern

import sqlite3
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 users (name, email) VALUES (?, ?)", ("Alice", "a@b.com"))
    users = db.execute("SELECT * FROM users").fetchall()
    for user in users:
        print(dict(user))

Key Takeaways

1. SQLite is built into Python — no installation needed
2. Always use parameterized queries (?) to prevent SQL injection
3. Use contextmanager for safe connection handling
4. conn.row_factory = sqlite3.Row for named column access
5. Commit changes explicitly (or use context manager)