Monthly Spending Summary
Exit

Monthly Spending Summary

Replace the Python loop in GET /summary with func.sum and group_by

💻

Writing code and entering commands is only available on desktop. Open this page on a larger screen to complete this chapter.

Why the loop is a problem

The current spending_summary loads every expense row from the database, then adds up totals in Python. For 10,000 expenses across 5 categories, it transfers 10,000 rows to produce 5 numbers.

A GROUP BY query does the same work inside the database and transfers only the 5 results:

results = session.exec(
    select(Expense.category, func.sum(Expense.amount)).group_by(Expense.category)
).all()
return {category: total for category, total in results}

  • func.sum(Expense.amount) — applies the SQL SUM function to the amount column within each group. func comes from SQLAlchemy and gives you access to any SQL function by name.
  • .group_by(Expense.category) — groups rows by category before applying SUM. Each category gets its own total.
  • session.exec(...).all() — returns a list of tuples, one per category: [("food", 42.50), ("transport", 12.00), ...].
  • {category: total for category, total in results} — unpacks each tuple into a dict entry.

func is imported from sqlalchemy, not from sqlmodel. SQLModel builds on SQLAlchemy and exposes its query API, but some lower-level tools like func come directly from SQLAlchemy.

Instructions

Rewrite spending_summary to use SQL aggregation.

  1. Add from sqlalchemy import func as a new import line.
  2. Inside spending_summary, replace the existing loop with a results variable. Set it to session.exec(select(Expense.category, func.sum(Expense.amount)).group_by(Expense.category)).all().
  3. Return {category: total for category, total in results}.

← Previous ChapterNext Chapter →