SQLAlchemy and Alembic 2 cheatsheet

SQLAlchemy and Alembic 2: Querying

8 common SQLAlchemy and Alembic 2 tasks.

Insert rows

Add objects, then commit to write them.

with Session(engine) as session:
    ada = User(name="Ada", email="[email protected]")
    ada.posts.append(Post(title="Notes on engines"))
    session.add(ada)
    session.commit()

Select rows

Build a select statement and read model objects with scalars().

from sqlalchemy import select

with Session(engine) as session:
    users = session.scalars(select(User).where(User.name == "Ada").order_by(User.id)).all()

Get by primary key

Look up one object by primary key; it returns None if missing.

user = session.get(User, 1)

Get exactly one row

scalar_one() raises if there is not exactly one match; first() returns None for no match.

user = session.scalars(select(User).where(User.email == "[email protected]")).one()

Join tables

Select columns across a relationship with an explicit join.

stmt = select(User.name, Post.title).join(Post, Post.user_id == User.id)
rows = session.execute(stmt).all()

Group and count

Count rows per group with SQL functions.

from sqlalchemy import func

stmt = select(User.name, func.count(Post.id)).join(Post).group_by(User.name)
rows = session.execute(stmt).all()

Paginate

Return the second page of 20 rows.

page = session.scalars(select(Post).order_by(Post.id).limit(20).offset(20)).all()

Avoid N+1 queries

Load related rows in the same query or one extra query, instead of one query per object.

from sqlalchemy.orm import selectinload

users = session.scalars(select(User).options(selectinload(User.posts))).all()