SQLAlchemy 2.0 ORM in Python (2026): Mapped Models + Session

Web Development Oct 01, 2026

Watch on YouTube

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 legacy Query.
  • Models use DeclarativeBase, Mapped[T], and mapped_column().
  • Install with pip install sqlalchemy (we tested 2.1.1 on Python 3.13.5).
  • SQLite is enough to learn the API; swap the URL for Postgres/MySQL later.
  • Real run: filtered year >= 2021 returned 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.

APIStyleUse in 2026
Mapped + mapped_columnTyped declarativeDefault for new models
select(Model).where(...)2.0 queryPreferred over legacy Query
Session context managerUnit of workCommit / rollback boundaries

Versions tested (2026-10-01)

  • Python 3.13.5
  • sqlalchemy 2.1.1
  • Dialect: sqlite (file demo.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 + AsyncSession when your FastAPI routes are async.

When to use what

ToolBest for
SQLAlchemy ORMTyped domain models, relationships, unit-of-work
SQLAlchemy Core / text()Complex SQL, bulk analytics, hand-tuned queries
SQLModelPydantic-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.

Share:
Last updated: October 2026
Built-in Functions in Python 2026 str() in Python 2026: String Conversion + Modern Formatting & Best Practices memoryview with JAX in Python 2026: Zero-Copy NumPy → JAX Array Interop + Efficient ML Examples memoryview with TensorFlow in Python 2026: Zero-Copy NumPy → Tensor Interop + GPU Pinning & ML Examples memoryview with TensorFlow in Python 2026: Zero-Copy NumPy → Tensor Interop + ML Examples memoryview with NumPy & PyTorch in Python 2026: Zero-Copy Views, Efficient Slicing & ML Interop Examples memoryview with NumPy in Python 2026: Zero-Copy Views, Efficient Slicing & Real ML Examples memoryview() in Python 2026: Zero-Copy Magic for Large Binary Data + Real Examples import() in Python 2026: How It Works, Security Risks & Modern Alternatives (importlib) zip() in Python 2026: How to Use It, strict=True Behavior & Real-World Examples vars() in Python 2026: Accessing Object Namespace + Modern Introspection Patterns type() in Python 2026: Dynamic Type Inspection & Object Creation + Modern Patterns tuple() in Python 2026: Immutable Sequences + Modern Patterns & Best Practices super() in Python 2026: Method Resolution & Modern Inheritance Patterns sum() in Python 2026: Summing Iterables + Modern Numeric Patterns & Best Practices staticmethod() in Python 2026: Static Methods, Utility Functions & Modern Best Practices sorted() in Python 2026: Sorting Iterables + Modern Patterns & Best Practices slice() in Python 2026: Creating Slice Objects + Modern Patterns & Best Practices setattr() in Python 2026: Dynamic Attribute Setting + Modern Patterns & Safety set() in Python 2026: Mutable Sets Creation + Modern Patterns & Best Practices