What SQL Can Do That Python Can't
Exit

What SQL Can Do That Python Can't

Understand why database-side filtering and aggregation outperforms Python loops

The remaining gap

Your endpoints now read from and write to SQLite. But two things still happen in Python that should happen in the database:

Filtering happens after loading. GET /expenses?category=food already uses .where() to filter in the database — that's correct. But there is no way to filter by date range yet. Without database-side date filtering, you would have to load all expenses, loop through them, and filter in Python — the same problem you had with the JSON file.

Aggregation happens in a loop. The GET /summary endpoint currently loops through all expenses in Python and sums up totals by category. If you have 10,000 expenses, it loads all 10,000 into memory just to produce a dict with 5 entries. A database can do that aggregation itself — loading only the results, not the source rows.

What this lesson adds

Three additions to your existing endpoints:

  • Date range filtering — GET /expenses?start=2024-01-01&end=2024-03-31 returns only expenses within a date range. Chained .where() conditions handle this at the database level.
  • SQL aggregation — GET /summary is rewritten to use func.sum() and .group_by(). The database adds up the totals and returns one row per category — no Python loop, no loading all rows.
  • Description search — GET /expenses?search=coffee returns expenses whose description contains the search term. SQLModel's .contains() maps to a SQL LIKE query.

How SQL aggregation works

The GROUP BY clause in SQL groups rows by a column value and applies a function — like SUM — to each group:

SELECT category, SUM(amount) FROM expense GROUP BY category

This query scans the table once, groups rows by category, and returns one row per category with the total amount. Your Python loop does the same thing, but loads all the data first.

In SQLModel, you write this as:

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

You will write this in the third chapter of this lesson.

Next Chapter →