python-database-patterns

# Python Database Patterns SQLAlchemy 2.0 and database best practices. ## SQLAlchemy 2.0 Basics ```python from sqlalchemy import create_engine, select from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, Session class Base(DeclarativeBase): pass class User(Base): __tablename__ = "users" id: Mapped[int] = mapped_column(primary_key=True) name: Mapped[str] = mapped_column(String(100)) email: Mapped[str] = mapped_column(String(255), unique=True) is_active: Mapped[bool] = mapped_column(default=True) # Create engine and tables engine = create_engine("postgresql://user:pass@localhost/db") Base.metadata.create_all(engine) # Query with 2.0 style with Session(engine) as session: stmt = select(User).where(User.is_active == True) users = session.execute(stmt).scalars().all() ``` ## Async SQLAlchemy ```python from sqlalchemy.ext.asyncio import ( AsyncSession, async_sessionmaker, create_async_engine, ) from sqlalchemy import select # Async engine engine = create_async_engine( "postgresql+asyncpg://user:pass@localhost/db", echo=False, pool_size=5, max_overflow=10, ) # Session factory async_session = async_sessionmaker(engine, expire_on_commit=False) # Usage async with async_session() as session: result = await session.execute(select(User).where(User.id == 1)) user = result.scalar_one_or_none() ``` ## Model Relationships ```python from sqlalchemy import ForeignKey from sqlalchemy.orm import relat...