The Hidden Cost of ORM — N+1 Queries in Real Apps

import sqlite3

# Connect directly to the database
connection = sqlite3.connect('bookstore.db')
cursor = connection.cursor()

# Step 1: Create the table with raw SQL — notice this is just a plain string.
# If you mistype 'VARCHAR', nothing warns you until you run this.
cursor.execute('''
    CREATE TABLE IF NOT EXISTS books (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        title VARCHAR(200),
        author VARCHAR(100),
        price REAL
    )
''')

# Step 2: Insert a book — the values are positional. Easy to mix up the order.
cursor.execute(
    'INSERT INTO books (title, author, price) VALUES (?, ?, ?)',
    ('Foundation', 'Isaac Asimov', 12.99)
)
connection.commit()

# Step 3: Fetch all books — the result is a list of plain tuples, not objects.
# cursor.fetchall() returns [(1, 'Foundation', 'Isaac Asimov', 12.99)]
# You must remember that index 1 is title, index 2 is author, etc.
rows = cursor.execute('SELECT * FROM books').fetchall()

for row in rows:
    # Accessing data by index position — fragile and hard to read
    print(f"Title: {row[1]}, Author: {row[2]}, Price: ${row[3]:.2f}")

connection.close()

# Using SQLAlchemy — one of the most popular Python ORMs.
# Install it first: pip install sqlalchemy

from sqlalchemy import create_engine, Column, Integer, String, Float
from sqlalchemy.orm import DeclarativeBase, Session

# --- STEP 1: Define the database connection ---
# 'sqlite:///bookstore.db' means: use SQLite, stored in a file called bookstore.db
# The ORM handles the actual connection — you just describe WHERE the database is.
engine = create_engine('sqlite:///bookstore.db', echo=False)

# --- STEP 2: Define your Base class ---
# All ORM model classes will inherit from this. It's the foundation the ORM builds on.
class Base(DeclarativeBase):
    pass

# --- STEP 3: Define the Book model (this REPLACES the CREATE TABLE SQL) ---
# This class definition IS your table definition. The ORM reads it and knows
# the table name, the column names, and the data types.
class Book(Base):
    __tablename__ = 'books'  # The name of the database table this maps to

    id = Column(Integer, primary_key=True, autoincrement=True)
    title = Column(String(200), nullable=False)
    author = Column(String(100), nullable=False)
    price = Column(Float, nullable=False)

    # This makes printing a Book object readable — very handy when debugging
    def __repr__(self):
        return f"Book(title='{self.title}', author='{self.author}', price=${self.price:.2f})"

# --- STEP 4: Create the table in the database ---
# The ORM inspects all classes that inherit from Base and issues CREATE TABLE
# statements automatically. You never write CREATE TABLE again.
Base.metadata.create_all(engine)

# --- STEP 5: Insert a record using a Python object, not a SQL string ---
# A 'Session' is your workspace — think of it as a shopping basket.
# Changes go in the basket (session) and are committed to the database together.
with Session(engine) as session:
    new_book = Book(
        title='Foundation',
        author='Isaac Asimov',
        price=12.99
    )
    session.add(new_book)   # Add the object to the basket
    session.commit()        # Flush the basket — this runs INSERT INTO books (...) VALUES (...)

    # --- STEP 6: Query using Python, get back Python objects ---
    # This line becomes: SELECT * FROM books WHERE author = 'Isaac Asimov'
    asimov_books = session.query(Book).filter_by(author='Isaac Asimov').all()

    for book in asimov_books:
        # 'book' is a real Book object — access properties by name, not index
        print(f"Found: {book.title} by {book.author} — ${book.price:.2f}")

    # --- STEP 7: Update a record — just change the property and commit ---
    # No UPDATE SQL needed. The ORM detects the change and writes the SQL for you.
    first_book = asimov_books[0]
    first_book.price = 9.99  # Change the price on the Python object
    session.commit()         # ORM runs: UPDATE books SET price = 9.99 WHERE id = 1

    print(f"Updated price: ${first_book.price:.2f}")

    # --- STEP 8: Delete a record ---
    session.delete(first_book)  # Mark for deletion
    session.commit()            # ORM runs: DELETE FROM books WHERE id = 1
    print("Book deleted.")

from sqlalchemy import create_engine, text, Column, Integer, String, Float
from sqlalchemy.orm import DeclarativeBase, Session

engine = create_engine('sqlite:///bookstore.db', echo=False)

class Base(DeclarativeBase):
    pass

class Book(Base):
    __tablename__ = 'books'
    id = Column(Integer, primary_key=True, autoincrement=True)
    title = Column(String(200))
    author = Column(String(100))
    price = Column(Float)

Base.metadata.create_all(engine)

# Seed some data so our queries have something to work with
with Session(engine) as session:
    session.add_all([
        Book(title='Foundation', author='Isaac Asimov', price=12.99),
        Book(title='Dune', author='Frank Herbert', price=14.99),
        Book(title='Neuromancer', author='William Gibson', price=10.49),
        Book(title='The Left Hand of Darkness', author='Ursula K. Le Guin', price=11.99),
    ])
    session.commit()

with Session(engine) as session:

    # ---- APPROACH 1: ORM Query ----
    # Clean, readable, safe from SQL injection, works across database brands
    print("=== ORM Query: Books under $13 ===")
    affordable_books = (
        session.query(Book)
        .filter(Book.price < 13.00)  # The ORM adds a WHERE clause
        .order_by(Book.price)        # The ORM adds ORDER BY
        .all()
    )
    for book in affordable_books:
        print(f"  {book.title} — ${book.price:.2f}")

    # ---- APPROACH 2: Raw SQL via the ORM connection ----
    # Useful for complex queries the ORM struggles to express cleanly.
    # text() tells SQLAlchemy this is a literal SQL string — handle with care.
    print("\n=== Raw SQL: Average price per author initial ===")
    raw_query = text("""
        SELECT
            SUBSTR(author, 1, 1) AS first_initial,
            ROUND(AVG(price), 2) AS average_price,
            COUNT(*) AS book_count
        FROM books
        GROUP BY first_initial
        ORDER BY average_price DESC
    """)
    results = session.execute(raw_query).fetchall()
    for row in results:
        # Raw SQL results come back as named tuples — row.first_initial works here
        print(f"  Initial '{row.first_initial}': avg ${row.average_price} ({row.book_count} books)")