TopolearnDashboards and reports are aggregations, and doing them in SQL is far faster than loading objects into Python.
SQL functions come from the func namespace: func.count(), func.sum(Order.total), func.avg(...), func.max(...), func.lower(User.name). Any name works (func.date_trunc(...)), rendered as that SQL function, so database-specific functions are available too.
Grouping looks like SQL: select(User.name, func.count(Post.id)).join(User.posts).group_by(User.name). Every selected column must be grouped or aggregated. .having(func.count(Post.id) > 2) filters groups after aggregation, while .where() filters rows before it.
Give computed columns a name with .label("n_posts"). The label becomes the attribute name on result rows (row.n_posts) and can be reused in order_by, either as the label object or by name string.
For single numbers, session.scalar(select(func.count()).select_from(User)) counts rows; note select_from, because func.count() alone doesn't mention a table. Counting a filtered query is select(func.count()).select_from(stmt.subquery()).
Aggregates return Rows, not ORM objects. Combine with an outer join to include zero counts: count a column from the outer side (func.count(Post.id)), because count(*) would count the NULL row as 1.
Example
from sqlalchemy import ForeignKey, create_engine, select, func
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship, Session
class Base(DeclarativeBase): pass
class User(Base):
__tablename__ = "users"
id: Mapped[int] = mapped_column(primary_key=True)
name: Mapped[str]
posts: Mapped[list["Post"]] = relationship()
class Post(Base):
__tablename__ = "posts"
id: Mapped[int] = mapped_column(primary_key=True)
user_id: Mapped[int] = mapped_column(ForeignKey("users.id"))
engine = create_engine("sqlite://")
Base.metadata.create_all(engine)
with Session(engine) as s:
s.add_all([User(name="ada", posts=[Post(), Post(), Post()]),
User(name="bob", posts=[Post()]), User(name="cy")])
s.commit()
n = func.count(Post.id).label("n_posts")
stmt = select(User.name, n).outerjoin(User.posts).group_by(User.name).order_by(n.desc())
for row in s.execute(stmt):
print(row.name, row.n_posts) # ada 3 / bob 1 / cy 0
print(s.scalar(select(func.count()).select_from(User))) # 3Remember
- func.<name>() renders any SQL function
- where() filters rows, having() filters groups
- .label('x') names a result column for row.x and order_by
- Count a column from the outer-joined side to get zeros
Watch out for
- Using count(*) with an outer join, which counts 1 for parents with no children.