SQLAlchemy 2.0 ORM in Python (2026): Mapped Models + Session
SQLAlchemy 2.0 ORM in Python (2026) shows how to define typed models with Mapped / mapped_column, open a Session, insert rows, and query with the 2.0-style select() API — all against SQLite so you can run the demo without Postgres.
After the ORM layer is solid, wire schema changes with Alembic migrations for FastAPI. For the HTTP side of the same stack, see FastHTML pure-Python web apps or stream tokens with FastAPI SSE.
TL;DR
- SQLAlchemy 2.0 prefers
select()+Session.scalars()over legacyQuery. - Models use
DeclarativeBase,Mapped[T], andmapped_column(). - Install with
pip install sqlalchemy(we tested2.1.1on Python 3.13.5). - SQLite is enough to learn the API; swap the URL for Postgres/MySQL later.
- Real run: filtered
year >= 2021returned Fluent Python first at 1012 pages;avg_pages=514.0.
Why SQLAlchemy 2.0 in 2026?
Most Python web and data apps still need a reliable ORM. SQLAlchemy 2.0 is the current baseline: clearer typing, a unified Core/ORM select() style, and better async support when you need it. If your mental model is still session.query(Model).filter(...), this article is the upgrade path.
| API | Style | Use in 2026 |
|---|---|---|
Mapped + mapped_column | Typed declarative | Default for new models |
select(Model).where(...) | 2.0 query | Preferred over legacy Query |
Session context manager | Unit of work | Commit / rollback boundaries |
Versions tested (2026-10-01)
- Python
3.13.5 sqlalchemy2.1.1- Dialect:
sqlite(filedemo.db)
python -m venv .venv && source .venv/bin/activate
pip install sqlalchemy==2.1.1
python orm_demo.py
1. DeclarativeBase + Mapped model
Save this as orm_demo.py. We delete any old demo.db, define a Book model, create the table, and insert five rows.
from pathlib import Path
import sqlalchemy as sa
from sqlalchemy import select, create_engine
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, Session
DB = Path("demo.db")
if DB.exists():
DB.unlink()
class Base(DeclarativeBase):
pass
class Book(Base):
__tablename__ = "books"
id: Mapped[int] = mapped_column(primary_key=True)
title: Mapped[str] = mapped_column(sa.String(120))
year: Mapped[int]
pages: Mapped[int]
engine = create_engine(f"sqlite:///{DB}", echo=False)
Base.metadata.create_all(engine)
print("sqlalchemy", sa.__version__)
print("dialect", engine.dialect.name)
with Session(engine) as session:
session.add_all(
[
Book(title="Fluent Python", year=2022, pages=1012),
Book(title="Architecture Patterns", year=2020, pages=448),
Book(title="Robust Python", year=2021, pages=278),
Book(title="Python Distilled", year=2021, pages=352),
Book(title="Effective Python", year=2019, pages=480),
]
)
session.commit()
total = session.scalar(select(sa.func.count()).select_from(Book))
print("inserted", total, "books")
DeclarativeBase is the 2.0 root. Integer primary keys auto-increment on SQLite. Always open a Session as a context manager so connections close cleanly.
2. select() + where + order_by
Continue in the same Session block (or open a new one). Filter books from 2021 onward and sort by page count descending.
stmt = (
select(Book)
.where(Book.year >= 2021)
.order_by(Book.pages.desc())
)
rows = session.scalars(stmt).all()
print("query year>=2021 order by pages desc:")
for i, b in enumerate(rows, 1):
print(f" #{i} id={b.id} pages={b.pages} year={b.year} title={b.title}")
avg_pages = session.scalar(select(sa.func.avg(Book.pages)))
print(f"avg_pages={avg_pages:.1f}")
print("OK Mapped + Session + select()")
session.scalars(stmt) yields ORM instances. Use session.execute(stmt) when you need full Row objects or multi-entity results. Aggregates like func.avg stay on Core expressions inside select().
Real output (this machine)
sqlalchemy 2.1.1
dialect sqlite
inserted 5 books
query year>=2021 order by pages desc:
#1 id=1 pages=1012 year=2022 title=Fluent Python
#2 id=4 pages=352 year=2021 title=Python Distilled
#3 id=3 pages=278 year=2021 title=Robust Python
avg_pages=514.0
OK Mapped + Session + select()
Three of five books match year >= 2021. Fluent Python leads on pages. Mean page count across all five rows is 514.0.
Common upgrades
- Postgres URL:
create_engine("postgresql+psycopg://user:pass@localhost/db")— same Mapped models. - Relationships:
Mapped[list["Chapter"]] = relationship(back_populates=...). - Migrations: generate revisions with Alembic against the same
Base.metadata. - Async:
create_async_engine+AsyncSessionwhen your FastAPI routes are async.
When to use what
| Tool | Best for |
|---|---|
| SQLAlchemy ORM | Typed domain models, relationships, unit-of-work |
| SQLAlchemy Core / text() | Complex SQL, bulk analytics, hand-tuned queries |
| SQLModel | Pydantic-style models on top of SQLAlchemy (see prior tutorials) |
FAQ
Is session.query() gone? It still exists for compatibility, but new code should use select().
Do I need Alembic on day one? For throwaway SQLite demos, create_all is fine. For any shared schema, add Alembic early.
Does this work with FastAPI? Yes — inject a Session (or AsyncSession) per request and keep the same Mapped models.
Next steps
Clone the pattern above, point the engine at your real database URL, and add Alembic before the first production deploy. Pair the ORM with FastHTML or FastAPI SSE when you expose the data over HTTP.