Python Database Patterns

SQLAlchemy 2.0 and database best practices.

SQLAlchemy 2.0 Basics

python
1from sqlalchemy import create_engine, select
2from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, Session
3
4class Base(DeclarativeBase):
5    pass
6
7class User(Base):
8    __tablename__ = "users"
9
10    id: Mapped[int] = mapped_column(primary_key=True)
11    name: Mapped[str] = mapped_column(String(100))
12    email: Mapped[str] = mapped_column(String(255), unique=True)
13    is_active: Mapped[bool] = mapped_column(default=True)
14
15# Create engine and tables
16engine = create_engine("postgresql://user:pass@localhost/db")
17Base.metadata.create_all(engine)
18
19# Query with 2.0 style
20with Session(engine) as session:
21    stmt = select(User).where(User.is_active == True)
22    users = session.execute(stmt).scalars().all()

Async SQLAlchemy

python
1from sqlalchemy.ext.asyncio import (
2    AsyncSession,
3    async_sessionmaker,
4    create_async_engine,
5)
6from sqlalchemy import select
7
8# Async engine
9engine = create_async_engine(
10    "postgresql+asyncpg://user:pass@localhost/db",
11    echo=False,
12    pool_size=5,
13    max_overflow=10,
14)
15
16# Session factory
17async_session = async_sessionmaker(engine, expire_on_commit=False)
18
19# Usage
20async with async_session() as session:
21    result = await session.execute(select(User).where(User.id == 1))
22    user = result.scalar_one_or_none()

Model Relationships

python
1from sqlalchemy import ForeignKey
2from sqlalchemy.orm import relationship, Mapped, mapped_column
3
4class User(Base):
5    __tablename__ = "users"
6
7    id: Mapped[int] = mapped_column(primary_key=True)
8    name: Mapped[str]
9
10    # One-to-many
11    posts: Mapped[list["Post"]] = relationship(back_populates="author")
12
13class Post(Base):
14    __tablename__ = "posts"
15
16    id: Mapped[int] = mapped_column(primary_key=True)
17    title: Mapped[str]
18    author_id: Mapped[int] = mapped_column(ForeignKey("users.id"))
19
20    # Many-to-one
21    author: Mapped["User"] = relationship(back_populates="posts")

Common Query Patterns

python
1from sqlalchemy import select, and_, or_, func
2
3# Basic select
4stmt = select(User).where(User.is_active == True)
5
6# Multiple conditions
7stmt = select(User).where(
8    and_(
9        User.is_active == True,
10        User.age >= 18
11    )
12)
13
14# OR conditions
15stmt = select(User).where(
16    or_(User.role == "admin", User.role == "moderator")
17)
18
19# Ordering and limiting
20stmt = select(User).order_by(User.created_at.desc()).limit(10)
21
22# Aggregates
23stmt = select(func.count(User.id)).where(User.is_active == True)
24
25# Joins
26stmt = select(User, Post).join(Post, User.id == Post.author_id)
27
28# Eager loading
29from sqlalchemy.orm import selectinload
30stmt = select(User).options(selectinload(User.posts))

FastAPI Integration

python
1from fastapi import Depends, FastAPI
2from sqlalchemy.ext.asyncio import AsyncSession
3from typing import Annotated
4
5async def get_db() -> AsyncGenerator[AsyncSession, None]:
6    async with async_session() as session:
7        yield session
8
9DB = Annotated[AsyncSession, Depends(get_db)]
10
11@app.get("/users/{user_id}")
12async def get_user(user_id: int, db: DB):
13    result = await db.execute(select(User).where(User.id == user_id))
14    user = result.scalar_one_or_none()
15    if not user:
16        raise HTTPException(status_code=404)
17    return user

Quick Reference

Additional Resources

• ./references/sqlalchemy-async.md - Async patterns, session management
• ./references/connection-pooling.md - Pool configuration, health checks
• ./references/transactions.md - Transaction patterns, isolation levels
• ./references/migrations.md - Alembic setup, migration strategies

Assets

• ./assets/alembic.ini.template - Alembic configuration template

See Also

Prerequisites:

• python-typing-patterns - Mapped types and annotations
• python-async-patterns - Async database sessions

Related Skills:

• python-fastapi-patterns - Dependency injection for DB sessions
• python-pytest-patterns - Database fixtures and testing