Rewrite List Expenses
Exit

Rewrite List Expenses

Replace the Python filter loop with a database query using select and where

💻

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

Building a query with select

The old list_expenses filtered expenses in Python by looping through the dict. The new version builds a SQL query and lets the database do the filtering:

statement = select(Expense)
if category:
    statement = statement.where(Expense.category == category)
return session.exec(statement).all()

  • select(Expense) — creates a query that selects all columns from the expense table. Think of it as SELECT * FROM expense.
  • .where(Expense.category == category) — adds a filter condition. SQLModel translates Expense.category == category into a SQL WHERE clause. You chain .where() calls to add more conditions — you will use this in Lesson 3 for date range filtering.
  • session.exec(statement).all() — sends the query to the database and returns all matching rows as a list.

If no category is provided, the if block is skipped and all expenses are returned. The filtering happens inside the database, not in a Python loop.

Instructions

Rewrite the list_expenses endpoint.

  1. Add select to the import from sqlmodel — add it to the existing import line that already has Session.
  2. Inside list_expenses, replace pass with a statement = select(Expense) line.
  3. Add an if category: block. Inside it, reassign statement = statement.where(Expense.category == category).
  4. Return session.exec(statement).all().

← Previous ChapterNext Chapter →