framework_note/note/python/python tool/SQLAlchemy-Tutorial.md

SQLAlchemy 2.0 Tutorial (Recipe Scraper Edition)

This guide covers SQLAlchemy 2.0, the industry-standard SQL toolkit and Object-Relational Mapper (ORM) for Python. We'll use the Recipe Web Scraper project models as our primary examples.

---

1. What is SQLAlchemy?

SQLAlchemy has two main components:

1. Core: A SQL abstraction layer (SQL Expression Language, Schema definitions, Engine).
2. ORM: A layer on top of Core that maps Python classes to database tables.

In this project, we primarily use the ORM to treat recipes and ingredients as Python objects.

---

2. Defining Models (The Modern Way)

SQLAlchemy 2.0 introduced a type-hint-centric way to define models using Mapped and mapped_column.

The Base Class

All models inherit from a common Base class created from DeclarativeBase.

from sqlalchemy.orm import DeclarativeBase

class Base(DeclarativeBase):
    pass

Example: The Recipe Model

from sqlalchemy import String, Integer, DateTime
from sqlalchemy.orm import Mapped, mapped_column, relationship
from datetime import datetime

class Recipe(Base):
    __tablename__ = "recipes"  # Name of the table in the DB
    
    # Primary Key
    id: Mapped[int] = mapped_column(primary_key=True)
    
    # Simple Columns (SQLAlchemy infers types from Mapped[T])
    url: Mapped[str] = mapped_column(String, unique=True, index=True)
    title: Mapped[str]
    total_time: Mapped[int | None] # Optional column (nullable=True)
    
    # Column with a default value
    scraped_at: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow)

    # Relationships (Defined in section 4)
    ingredients: Mapped[list["Ingredient"]] = relationship(back_populates="recipe")

---

3. Engine and Session

The Engine

The Engine is the starting point for any SQLAlchemy application. It manages a pool of connections to the database.

from sqlalchemy import create_engine

# SQLite: The '///' means relative path to the current directory
engine = create_engine("sqlite:///recipes.db", echo=True) 
# echo=True logs all SQL commands to the terminal (great for debugging)

Creating Tables

You can tell SQLAlchemy to create all tables defined in your models:

Base.metadata.create_all(engine)

The Session

The Session handles the conversation with the database. Use sessionmaker to create a factory for sessions.

from sqlalchemy.orm import sessionmaker

SessionLocal = sessionmaker(bind=engine)

# Use as a context manager to ensure the connection is closed
with SessionLocal() as session:
    # do work here
    pass

---

4. Relationships (1-to-Many)

In our project, one Recipe has many Ingredients.

Foreign Key

The "child" table (Ingredient) must have a column pointing to the "parent" table (Recipe).

from sqlalchemy import ForeignKey

class Ingredient(Base):
    __tablename__ = "ingredients"
    id: Mapped[int] = mapped_column(primary_key=True)
    
    # Links to 'recipes.id'
    recipe_id: Mapped[int] = mapped_column(ForeignKey("recipes.id"))
    
    text: Mapped[str]
    
    # Back-reference to the parent Recipe object
    recipe: Mapped["Recipe"] = relationship(back_populates="ingredients")

Cascades

cascade="all, delete-orphan" ensures that if you delete a Recipe, all its Ingredients are also deleted automatically.

---

5. CRUD Operations

Create (Insert)

with SessionLocal() as session:
    new_recipe = Recipe(title="Pasta Carbonara", url="https://example.com/pasta")
    session.add(new_recipe)
    session.commit() # Save to DB

Read (Select)

from sqlalchemy import select

with SessionLocal() as session:
    # 1. Get by ID
    recipe = session.get(Recipe, 1)
    
    # 2. Filter by column
    stmt = select(Recipe).where(Recipe.title == "Pasta Carbonara")
    result = session.execute(stmt).scalars().first()
    
    # 3. Get all
    all_recipes = session.query(Recipe).all() # Older syntax, still common

Update

with SessionLocal() as session:
    recipe = session.get(Recipe, 1)
    recipe.title = "Authentic Pasta Carbonara"
    session.commit()

Delete

with SessionLocal() as session:
    recipe = session.get(Recipe, 1)
    session.delete(recipe)
    session.commit()

---

6. Common Pitfalls

1. Lazy Loading: By default, SQLAlchemy doesn't load relationships until you access them. This can cause "N+1" performance issues. Use joinedload to fetch everything in one query.
2. Session Lifecycle: Always use a context manager (with session:) or close your sessions manually.
3. Commit vs Flush: session.flush() sends changes to the DB but doesn't permanentize them. session.commit() makes them permanent.

---

7. Next Steps: Migrations with Alembic

As your models change (e.g., you add a rating column), you shouldn't just delete the DB and start over. Alembic is the tool used to handle database migrations.

Install it with: pip install alembic