Querying with SQLAlchemy ORM

Basic Queries (SQLAlchemy 2.0)

from sqlalchemy import select
from sqlalchemy.orm import Session

with Session(engine) as session:
    # Select all users
    stmt = select(User)
    users = session.scalars(stmt).all()

    # Select with filter
    stmt = select(User).where(User.username == "john")
    user = session.scalars(stmt).first()

    # Select specific columns
    stmt = select(User.username, User.email)
    results = session.execute(stmt).all()

Filtering

# Equality
select(User).where(User.id == 1)

# Not equal
select(User).where(User.id != 1)

# Greater/less than
select(User).where(User.age > 18)
select(User).where(User.age >= 18)

# IN clause
select(User).where(User.id.in_([1, 2, 3]))

# LIKE / ILIKE
select(User).where(User.name.like("%john%"))
select(User).where(User.name.ilike("%JOHN%"))  # case insensitive

# NULL checks
select(User).where(User.deleted_at.is_(None))
select(User).where(User.deleted_at.is_not(None))

# AND / OR
from sqlalchemy import and_, or_
select(User).where(
    and_(User.age > 18, User.is_active == True)
)
select(User).where(
    or_(User.role == "admin", User.role == "moderator")
)

# Multiple where clauses (AND)
select(User).where(User.age > 18).where(User.is_active == True)

Ordering and Limiting

# Order by
select(User).order_by(User.created_at.desc())
select(User).order_by(User.last_name, User.first_name)

# Limit and offset
select(User).limit(10).offset(20)

# First result
user = session.scalars(select(User)).first()

# One result (raises if not exactly one)
user = session.scalars(select(User).where(User.id == 1)).one()

# One or none
user = session.scalars(select(User).where(User.id == 1)).one_or_none()

Aggregations

from sqlalchemy import func

# Count
stmt = select(func.count(User.id))
count = session.scalar(stmt)

# Count with filter
stmt = select(func.count(User.id)).where(User.is_active == True)

# Sum, avg, min, max
select(func.sum(Order.total))
select(func.avg(Order.total))
select(func.min(Order.total))
select(func.max(Order.total))

# Group by
stmt = select(
    User.role,
    func.count(User.id).label("user_count")
).group_by(User.role)

# Having
stmt = select(
    User.role,
    func.count(User.id).label("count")
).group_by(User.role).having(func.count(User.id) > 5)

ORM Queries

ORM Queries

Querying with SQLAlchemy ORM

Basic Queries (SQLAlchemy 2.0)

Filtering

Ordering and Limiting

Aggregations