mirror of
https://github.com/Rainyy21/framework_note.git
synced 2026-10-11 01:10:28 -04:00
Rainyy21
177 lines
5.0 KiB
Markdown
177 lines
5.0 KiB
Markdown
# 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`.
|
|
|
|
```python
|
|
from sqlalchemy.orm import DeclarativeBase
|
|
|
|
class Base(DeclarativeBase):
|
|
pass
|
|
```
|
|
|
|
### Example: The Recipe Model
|
|
```python
|
|
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.
|
|
|
|
```python
|
|
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:
|
|
```python
|
|
Base.metadata.create_all(engine)
|
|
```
|
|
|
|
### The Session
|
|
The **Session** handles the conversation with the database. Use `sessionmaker` to create a factory for sessions.
|
|
|
|
```python
|
|
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`).
|
|
|
|
```python
|
|
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)
|
|
```python
|
|
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)
|
|
```python
|
|
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
|
|
```python
|
|
with SessionLocal() as session:
|
|
recipe = session.get(Recipe, 1)
|
|
recipe.title = "Authentic Pasta Carbonara"
|
|
session.commit()
|
|
```
|
|
|
|
### Delete
|
|
```python
|
|
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`
|