Introduction

filter and filter_by both narrow query results in SQLAlchemy, but they are not interchangeable. The short version is that filter_by is a convenience API for simple equality checks, while filter accepts full SQL expression language and is the tool you reach for once the query gets even slightly complex.

What filter_by Does

filter_by takes keyword arguments whose names match mapped model attributes. Each keyword becomes an equality comparison, so it is concise and readable for straightforward lookups.

users = session.query(User).filter_by(is_active=True, role="admin").all()

That reads naturally and is roughly equivalent to:

1users = (
2    session.query(User)
3    .filter(User.is_active == True, User.role == "admin")
4    .all()
5)

The convenience comes with a limitation: filter_by is mostly about attribute equals value comparisons. You do not use it for inequalities, LIKE, IN, OR, functions, or explicit SQL operators.

What filter Does

filter accepts SQLAlchemy expressions, which makes it far more flexible. You can compare columns, compose conditions, call SQL functions, and express range checks or joins directly.

1from sqlalchemy import or_
2
3users = (
4    session.query(User)
5    .filter(
6        User.is_active.is_(True),
7        User.login_count >= 10,
8        or_(User.role == "admin", User.role == "staff"),
9    )
10    .all()
11)

Because filter works with column expressions, it is the better default once a query needs anything beyond plain equality.

Side-by-Side Examples

For a simple primary-table lookup, both methods are fine:

user = session.query(User).filter_by(email="[email protected]").first()

user = session.query(User).filter(User.email == "[email protected]").first()

For a more realistic query, only filter stays comfortable:

1from datetime import datetime, timedelta
2
3cutoff = datetime.utcnow() - timedelta(days=30)
4
5recent_users = (
6    session.query(User)
7    .filter(User.created_at >= cutoff)
8    .filter(User.deleted_at.is_(None))
9    .order_by(User.created_at.desc())
10    .all()
11)

Trying to express that with filter_by would either fail or become misleading because the API is not designed for operators such as greater-than or IS NULL.

Joins Make the Difference Clearer

The gap becomes even more obvious when another table is involved. Suppose User has a relationship to Address:

1results = (
2    session.query(User)
3    .join(User.addresses)
4    .filter(Address.city == "Toronto")
5    .filter(Address.is_primary.is_(True))
6    .all()
7)

This is where filter shines. The condition names the exact model columns involved. With filter_by, you rely on keyword resolution against the current entity context, which can be surprising in joined queries and harder to read later. Many teams avoid filter_by once joins appear for that reason alone.

SQLAlchemy 2 Style

If you are using modern SQLAlchemy, you may prefer select(...).where(...) instead of the older Query API. The same conceptual difference still applies:

1from sqlalchemy import select
2
3stmt = select(User).filter_by(is_active=True)
4stmt = stmt.where(User.created_at >= cutoff)

The API surface changes slightly, but the rule remains the same. filter_by is concise for simple mapped-attribute equality. Expression-based filtering is the broader and more explicit tool.

Readability Versus Power

There is nothing wrong with filter_by for very simple repository methods such as “find active users” or “look up order by external id.” It keeps code short and clear. The trouble starts when people try to stretch it beyond its sweet spot.

A practical guideline is:

• Use filter_by when every condition is a direct equality on one mapped entity.
• Use filter when you need operators, functions, NULL checks, joins, or explicit column references.

That rule keeps queries readable and makes later refactors easier.

Common Pitfalls

The most common mistake is trying to write non-equality conditions with filter_by, such as age greater than eighteen. That is what filter is for.

Another pitfall is assuming filter_by always targets the same model in a joined query. Depending on query construction, keyword names can resolve against the current entity context, which makes the code harder to reason about.

It is also easy to write User.is_active == True instead of User.is_active.is_(True) for nullable boolean columns. Both can work, but explicit is_ reads closer to SQL intent and avoids edge-case confusion.

Finally, remember that None comparisons are SQL comparisons, not normal Python identity checks. For null filtering, use User.deleted_at.is_(None) or is_not(None) rather than forcing it through filter_by.

Summary

• 'filter_by is a convenience API for simple equality checks on mapped attributes.'
• 'filter accepts full SQLAlchemy expressions and handles complex conditions cleanly.'
• For joins, functions, NULL checks, and inequality operators, prefer filter.
• 'filter_by is fine for short, obvious queries on a single entity.'
• If a query starts feeling clever, switch to explicit column expressions before it becomes confusing.