How to Eager Load with JOIN to Reduce N+1 Queries in Python

import sqlite3


def eager_load_join_reduce(mock_db_path=":memory:"):
    """Demonstrate eager loading where joins reduce query count from N+1 to 1."""
    conn = sqlite3.connect(mock_db_path)
    cursor = conn.cursor()
    cursor.executescript(
        """
        CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT);
        CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT, author_id INTEGER);
        INSERT INTO authors VALUES (1, 'Tolkien'), (2, 'Asimov');
        INSERT INTO books VALUES
            (1, 'The Hobbit', 1),
            (2, 'Foundation', 2),
            (3, 'I, Robot', 2);
        """
    )
    conn.commit()

    # Lazy loading (N+1): per author, query the DB
    cursor.execute("SELECT id, name FROM authors")
    authors = cursor.fetchall()
    lazy_queries = 1  # authors query
    lazy_books = {}
    for author_id, _ in authors:
        cursor.execute("SELECT title FROM books WHERE author_id=?", (author_id,))
        lazy_books[author_id] = [title for (title,) in cursor.fetchall()]
        lazy_queries += 1

    # Eager loaded join: single query fetches nested data
    cursor.execute(
        """
        SELECT a.id, a.name, b.title
        FROM authors a
        LEFT JOIN books b ON b.author_id = a.id
        ORDER BY a.id
        """
    )
    rows = cursor.fetchall()
    eager_queries = 1
    eager_books = {}
    for author_id, name, title in rows:
        eager_books.setdefault(author_id, {"name": name, "books": []})["books"].append(title)

    conn.close()

    print(f"Lazy queries: {lazy_queries} -> authors: {lazy_books}")
    print(f"Eager queries: {eager_queries} -> authors: {eager_books}")
    assert lazy_queries > eager_queries, "Eager should use fewer queries"


if __name__ == "__main__":
    eager_load_join_reduce()