Query & Filter Records

You’ve built your models, you’ve saved your first records — but now comes the moment every web app stands or falls on: getting exactly the right rows back. Writing User.objects.all() works for a demo, but as soon as your database has thousands of orders, hundreds of thousands of events, or a user who wants to see only their unpaid invoices from last month, a blunt “give me everything” query turns into slow pages, wrong data, and angry users. Filtering is the skill that turns a database from a storage dump into a precision tool — and without it, every feature you build next (search, dashboards, pagination, reports) will be built on sand.

The problem this lesson solves

Why can’t you just load every record and use a Python if statement to pick what you need? You can — for about 50 rows. But the moment your table grows past a few thousand records, pulling them all into memory and filtering in Python is like using a sledgehammer to trim a bonsai tree. It wastes memory, it wastes bandwidth, and it makes your API or web page painfully slow. Worse, it breaks down as soon as you need to join related data across multiple tables.

The real solution, used by every production Django, Flask-SQLAlchemy, or FastAPI + SQLAlchemy app, is to push the filtering work down into the database itself. Databases are built for exactly this: fast, indexed searches over millions of rows. Your job as a Python developer is to translate a human request — "show me all active users in Berlin" — into a precise query that the database can execute in milliseconds.

Core concept / mental model

Think of your database as a massive library, and your ORM (Object-Relational Mapper) as a helpful librarian. You don’t ask the librarian to bring every book in the building so you can look at each one. You ask: "Show me all books by Jane Austen, published after 2000, and only available in hardcover." The librarian then goes into the stacks with that exact list and brings you only the matching books. That’s querying — and filtering is just adding more conditions to your request.

In Python web frameworks, you work with query sets (Django) or query objects (SQLAlchemy). These are lazy: they don’t hit the database until you force them to, like iterating over them or calling .count()/.first(). This laziness is a superpower — you can build a filter step by step, condition by condition, and the database runs one efficient query at the end. The mental model is: build a pipeline of filters, then execute it once.

How it works step by step

Let’s break down the anatomy of a filtered query, using Django ORM as our primary example (the same concepts apply to SQLAlchemy):

1. Start with a model: You have a Django model, say Order, with fields like status, total, created_at.
2. Write the base query set: Order.objects.all() — this is your starting point, representing every row in the orders table.
3. Apply filters: Use .filter(**conditions) to narrow the set. Each condition is a keyword argument like status="paid" or total__gte=100. The double underscore __ is how Django lets you access related fields and lookup types.
4. Chain filters: You can call .filter() multiple times, and they combine with AND logic. For OR logic, use Q objects.
5. Evaluate: Convert the lazy query set into actual data with .all(), .first(), .values(), .count(), or by iterating in a template.

For SQLAlchemy (used in Flask or FastAPI), the flow is similar but slightly more explicit:

1. Start with session.query(Model).
2. Add conditions with .filter(Model.field == value) or .filter_by(field=value).
3. Call .all(), .first(), .one(), or .count() to execute.

The underlying SQL is almost identical: SELECT * FROM orders WHERE status = 'paid' AND total >= 100. The ORM is just a safe, Pythonic wrapper that prevents SQL injection and keeps your code readable.

Hands-on walkthrough

Let’s make this concrete with a Django example. First, define a simple Order model if you don’t have one:

# models.py
from django.db import models

class Order(models.Model):
    status = models.CharField(max_length=20)  # 'pending', 'paid', 'cancelled'
    total = models.DecimalField(max_digits=10, decimal_places=2)
    customer_email = models.EmailField()
    created_at = models.DateTimeField(auto_now_add=True)

Now, in a Django shell or a view, you can run filtered queries:

# In your view or shell
from myapp.models import Order

# All paid orders over $100
paid_orders = Order.objects.filter(status="paid", total__gte=100)

# Orders that are either pending or cancelled (OR logic)
from django.db.models import Q
pending_or_cancelled = Order.objects.filter(Q(status="pending") | Q(status="cancelled"))

# Only get the first matching order (evaluates immediately)
first_paid = Order.objects.filter(status="paid").first()

# Count matching records without loading them
count = Order.objects.filter(status="paid").count()

print("Paid orders over $100:", list(paid_orders))
print("Pending or cancelled:", list(pending_or_cancelled))
print("First paid:", first_paid)
print("Count:", count)

Expected output (assuming your database has some sample data):

Paid orders over $100: [<Order: Order object (3)>, <Order: Order object (5)>]
Pending or cancelled: [<Order: Order object (1)>, <Order: Order object (4)>]
First paid: <Order: Order object (3)>
Count: 2

Now, if you’re working with Flask or FastAPI and SQLAlchemy, the same logic looks like this:

# app.py (Flask) or main.py (FastAPI)
from sqlalchemy import or_
from your_models import Order, Session

session = Session()

# All paid orders over $100
paid_orders = session.query(Order).filter(Order.status == "paid", Order.total >= 100).all()

# OR logic using or_
from sqlalchemy import or_
pending_or_cancelled = session.query(Order).filter(or_(Order.status == "pending", Order.status == "cancelled")).all()

# First match
first_paid = session.query(Order).filter(Order.status == "paid").first()

# Count
count = session.query(Order).filter(Order.status == "paid").count()

print(paid_orders, pending_or_cancelled, first_paid, count)
session.close()

Pro tip: Always call .close() on a SQLAlchemy session (or use a context manager with with session: in Flask-SQLAlchemy) to release the database connection quickly.

Compare options / when to choose what

You have multiple ways to filter in both Django and SQLAlchemy, and choosing the right one keeps your code clean and efficient. Here’s a quick comparison:

In SQLAlchemy, you have analogous tools: .filter_by() for keyword-only exact matches, .filter() for more expressive conditions, and or_() / and_() for boolean logic.

Troubleshooting & edge cases

Issue: You run .filter() and it doesn’t seem to do anything — you still get all records.

Cause: You forgot to evaluate the query set. A query set is lazy; .filter() alone doesn’t hit the database. You must use .all(), .first(), .count(), or iterate over it.

Fix: Always end with an evaluation call:

# Wrong: prints QuerySet, never hits DB
result = Order.objects.filter(status="paid")
print(result)

# Right: loads the matching rows
result = Order.objects.filter(status="paid").all()
print(list(result))

Issue: You get a TypeError: 'Field' object is not callable when trying to filter with total__gte=100 in SQLAlchemy.

Cause: SQLAlchemy doesn’t use the double-underscore syntax; you need to compare columns directly: Order.total >= 100.

Fix: Use comparison operators with column attributes.

Issue: The query is running fast at first, but slowly as the table grows.

Cause: Missing database indexes on the columns you filter by, or you’re forcing a full table scan.

Fix: Add indexes in your model definitions (e.g., db_index=True in Django, index=True in SQLAlchemy) for fields used in filter() or order_by() frequently.

Issue: Your OR query is returning duplicate records.

Cause: When combining multiple conditions with Q objects that overlap, you can get duplicates if you use .filter(Q(...) | Q(...)) on a many-to-many relationship.

Fix: Use .distinct() to remove duplicates.

What you learned & what's next

You now know how to query and filter database records using Python web frameworks — you understand the lazy query set model, how to apply simple AND filters, how to build OR conditions with Q objects, and how to handle common pitfalls. These skills are the foundation for building real features like search pages, user dashboards, and report APIs.

You also practiced evaluating queries with .all(), .first(), and .count(), and learned how to avoid the classic mistakes that cause slow or broken queries.

In the next lesson in our Python web development path, you’ll build on this by learning how to organize and paginate results — turning big query sets into manageable pages, and then combining filtering with sorting for a complete list-and-search experience. After that, you’ll be ready to connect these queries to RESTful APIs or Django REST Framework views, making your web app truly dynamic.

Go ahead and try the challenge below — the more you practice filtering, the more automatic it becomes.