Python’s SQL toolkit
SQLAlchemy, created by Mike Bayer in 2006, is the main SQL toolkit and ORM for Python. It has three layers:
- Core — SQL expression builder (agnostic query builder)
- ORM — class-table mapping, unit of work, identity map
- Engine — connection pooling, driver abstraction
MIT licence. Compatible with PostgreSQL, MySQL, MariaDB, SQLite, Oracle, MSSQL, and dozens of others via drivers.
SQLAlchemy 2.0 — January 2023
SQLAlchemy 2.0, released on 26 January 2023 after years of work, is a major release with structural changes:
Unified 2.0-style API
Previously ORM and Core had divergent APIs. 2.0 unifies: Core Select and ORM queries use the same select()-based syntax, consistent throughout.
# Legacy 1.x style
result = session.query(User).filter_by(name='Anna').all()
# 2.0 style (recommended)
stmt = select(User).where(User.name == 'Anna')
result = session.execute(stmt).scalars().all()Full typing
SQLAlchemy 2.0 declares relationships with modern type hints (PEP 484+):
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column
class Base(DeclarativeBase): pass
class User(Base):
__tablename__ = 'users'
id: Mapped[int] = mapped_column(primary_key=True)
name: Mapped[str]
email: Mapped[str | None]Typed queries, better mypy/pyright support.
Async support
async with AsyncSession(engine) as session:
result = await session.execute(select(User))
users = result.scalars().all()Native async (from 1.4, consolidated 2.0) for use with FastAPI, Starlette, asyncio.
Usage
SQLAlchemy is used in:
- Flask + Flask-SQLAlchemy as standard pattern
- FastAPI + SQLAlchemy + Alembic (migrations)
- SQLModel (Sebastián Ramírez) — high-level on Pydantic + SQLAlchemy
- Celery — result persistence
- Airflow — metadata DB
Alembic (migrations)
Alembic, same author, is the migration tool for SQLAlchemy. Generates Python migration scripts from differences between models and DB schema. Standard for schema evolution in Python applications.
In the Italian context
SQLAlchemy is in practically every production-grade Italian Python application, from SMEs to large companies.
References: SQLAlchemy 2.0 (26 January 2023). Mike Bayer. MIT licence. Core + ORM + Engine. Alembic for migrations. Async support (1.4+). Type hints (PEP 484).