Database Helper in Python with SQLite Scaling Optimization

import sqlite3
from contextlib import contextmanager


class DatabaseHelper:
    """Beginner-friendly helper for SQLite database operations with scaling tips."""

    def __init__(self, db_path):
        self.db_path = db_path

    @contextmanager
    def connection(self):
        """Context manager for automatic commit/rollback and closing."""
        conn = sqlite3.connect(self.db_path)
        conn.execute("PRAGMA journal_mode=WAL")  # Better concurrency
        conn.execute("PRAGMA synchronous=NORMAL")  # Faster writes
        try:
            yield conn
            conn.commit()
        except Exception:
            conn.rollback()
            raise
        finally:
            conn.close()

    def create_sample_table(self):
        """Create a simple indexed table for demonstration."""
        with self.connection() as conn:
            conn.execute("""
                CREATE TABLE IF NOT EXISTS users (
                    id INTEGER PRIMARY KEY,
                    name TEXT NOT NULL,
                    email TEXT UNIQUE NOT NULL,
                    age INTEGER
                )
            """)
            conn.execute("CREATE INDEX IF NOT EXISTS idx_users_age ON users(age)")

    def insert_many(self, users):
        """Insert multiple rows efficiently using executemany."""
        with self.connection() as conn:
            conn.executemany(
                "INSERT INTO users (name, email, age) VALUES (?, ?, ?)",
                users
            )

    def query_by_age(self, min_age):
        """Query using the index — faster on large datasets."""
        with self.connection() as conn:
            return list(conn.execute(
                "SELECT name, email FROM users WHERE age >= ?",
                (min_age,)
            ))


if __name__ == "__main__":
    db = DatabaseHelper(":memory:")  # in-memory for demo
    db.create_sample_table()
    db.insert_many([
        ("Alice", "alice@example.com", 30),
        ("Bob", "bob@example.com", 25),
        ("Carol", "carol@example.com", 40)
    ])
    results = db.query_by_age(28)
    for row in results:
        print(row)