How to Optimize SQLite Database Performance in Python

import sqlite3

DATABASE_PATH = "beginners.db"
UNOPTIMIZED_TABLE_SCHEMA = """
CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT NOT NULL
)
"""


def optimize_database(db_path: str = DATABASE_PATH) -> dict:
    with sqlite3.connect(db_path) as connection:
        cursor = connection.cursor()
        cursor.execute(UNOPTIMIZED_TABLE_SCHEMA)

        # Optimize: create an index on frequently queried columns
        cursor.execute("CREATE INDEX IF NOT EXISTS idx_users_email ON users(email)")

        # Optimize: set WAL mode for better concurrent reads/writes
        cursor.execute("PRAGMA journal_mode=WAL")
        # Optimize: tune synchronous for speed (safe for most apps)
        cursor.execute("PRAGMA synchronous=NORMAL")

        connection.commit()
        journal_mode = cursor.execute("PRAGMA journal_mode").fetchone()[0]
        synchronous_setting = cursor.execute("PRAGMA synchronous").fetchone()[0]
        has_index = cursor.execute(
            "SELECT 1 FROM sqlite_master WHERE type='index' AND name='idx_users_email'"
        ).fetchone()

    return {
        "database": db_path,
        "journal_mode": journal_mode,
        "synchronous": synchronous_setting,
        "index_created": bool(has_index),
        "optimization_status": "optimized" if has_index else "not optimized",
    }


if __name__ == "__main__":
    result = optimize_database()
    print(f"Optimization result: {result}")