Querying with filter and filter_by in Flask - Step-by-Step Execution

Concept Flow - Querying with filter and filter_by

Start Query on Model

↓

Choose filter or filter_by

↓

filter_by: simple key=value

↓

Build SQL WHERE clause

↓

filter: complex expressions

↓

Build SQL WHERE clause

↓

Execute Query

↓

Return Results

This flow shows how a query starts on a model, then uses filter_by for simple key=value filters or filter for complex expressions, builds the SQL WHERE clause, executes the query, and returns results.

Execution Sample

Flask

user = User.query.filter_by(name='Alice').first()
posts = Post.query.filter(Post.user_id == user.id).all()

This code finds the first user named Alice, then finds all posts by that user.

Execution Table

Step	Action	Query Part	Result
1	Start query on User model	SELECT * FROM user	Query object created
2	Apply filter_by(name='Alice')	WHERE name = 'Alice'	Query updated with filter_by
3	Execute first()	LIMIT 1	Returns first User with name Alice
4	Start query on Post model	SELECT * FROM post	Query object created
5	Apply filter(Post.user_id == user.id)	WHERE user_id = user's id	Query updated with filter expression
6	Execute all()	Fetch all matching rows	Returns list of Posts by Alice

💡 Query executes after filters applied; first() returns one object, all() returns list

Variable Tracker

Variable	Start	After Step 3	After Step 6
user	null	User object with name='Alice'	User object unchanged
posts	null	null	List of Post objects by Alice

Key Moments - 2 Insights

Why use filter_by for simple filters and filter for complex ones?

filter_by accepts simple key=value pairs (see Step 2), while filter accepts expressions like Post.user_id == user.id (Step 5). Using the right one matches the query syntax.

What does first() do compared to all()?

first() returns the first matching record or null (Step 3), while all() returns a list of all matching records (Step 6). This affects the type of result you get.

Visual Quiz - 3 Questions

Look at the execution table, what SQL clause is added at Step 2?

A LIMIT 1

B WHERE name = 'Alice'

C WHERE user_id = Alice's id

D ORDER BY name

💡 Hint

Check the 'Query Part' column at Step 2 in the execution_table

At which step does the query return a list of posts?

A Step 3

B Step 4

C Step 6

D Step 2

💡 Hint

Look at the 'Result' column for Step 6 in the execution_table

If we replaced filter_by(name='Alice') with filter(User.name == 'Alice'), what changes in the execution?

A filter allows more complex expressions, so syntax changes

B No change, both produce the same SQL

C filter_by cannot be replaced by filter

D filter_by returns a list, filter returns one object

💡 Hint

Compare Step 2 and Step 5 'Query Part' columns and the explanation in key_moments

Concept Snapshot

Querying with filter and filter_by in Flask-SQLAlchemy:
- Use filter_by(key=value) for simple equality filters.
- Use filter(expression) for complex conditions.
- filter_by builds WHERE with key=value.
- filter builds WHERE with expressions like Model.field == value.
- Use first() to get one result, all() for list.
- Queries build SQL then execute to return results.

Full Transcript

In Flask-SQLAlchemy, querying data involves starting a query on a model. You can use filter_by for simple filters like key=value, which builds a WHERE clause with equality. For more complex conditions, use filter with expressions like Model.field == value. After applying filters, you execute the query with methods like first() to get one record or all() to get all matching records. This process builds the SQL query step-by-step and returns the results accordingly.

Practice

1. What is the main difference between filter and filter_by in Flask querying?

easy

A. filter is used only for deleting records; filter_by is for selecting.

B. filter_by allows complex expressions; filter uses simple keyword arguments.

C. filter allows complex expressions; filter_by uses simple keyword arguments.

D. filter and filter_by are exactly the same in usage.

Solution

1. Step 1: Understand filter usage

   filter accepts expressions like User.age > 20, allowing detailed conditions.

2. Step 2: Understand filter_by usage

   filter_by uses simple keyword arguments like filter_by(name='John') for equality checks.

3. Final Answer:

   filter allows complex expressions; filter_by uses simple keyword arguments. -> Option C

4. Quick Check:

   filter = complex, filter_by = simple keyword [OK]

Hint: Remember: filter = expressions, filter_by = simple equals [OK]

Common Mistakes:

• Thinking filter_by supports complex expressions
• Confusing filter_by with filter in syntax
• Assuming both methods behave identically

2. Which of the following is the correct syntax to filter users with age 30 using filter_by?

easy

A. User.query.filter_by('age == 30').all()

B. User.query.filter_by(age=30).all()

C. User.query.filter_by(age == 30).all()

D. User.query.filter_by(age > 30).all()

Solution

1. Step 1: Check filter_by syntax

   filter_by accepts keyword arguments like age=30, not expressions or strings.

2. Step 2: Identify correct usage

   Only User.query.filter_by(age=30).all() uses correct keyword argument syntax.

3. Final Answer:

   User.query.filter_by(age=30).all() -> Option B

4. Quick Check:

   filter_by uses keyword arguments [OK]

Hint: Use keyword arguments without quotes or operators in filter_by [OK]

Common Mistakes:

• Passing expressions or strings to filter_by
• Using comparison operators inside filter_by
• Confusing filter_by with filter syntax

3. Given the model User with fields name and age, what will this query return?

users = User.query.filter(User.age > 25).filter_by(name='Alice').all()

medium

A. All users named Alice older than 25

B. All users older than 25, ignoring name

C. All users named Alice, ignoring age

D. Syntax error due to mixing filter and filter_by

Solution

1. Step 1: Understand chaining filters

   Chaining filter and filter_by combines conditions with AND logic.

2. Step 2: Analyze conditions

   filter(User.age > 25) filters age > 25, filter_by(name='Alice') filters name = 'Alice'. Both apply together.

3. Final Answer:

   All users named Alice older than 25 -> Option A

4. Quick Check:

   Chained filters combine conditions [OK]

Hint: Chained filters combine all conditions with AND [OK]

Common Mistakes:

• Thinking filter_by overrides filter
• Assuming only one filter applies
• Believing mixing filter and filter_by causes error

4. What is wrong with this query?

users = User.query.filter_by(User.age > 20).all()

medium

A. filter_by requires a list, not a condition

B. User.age > 20 is a valid filter_by argument

C. The query is correct and returns users older than 20

D. filter_by does not accept expressions, only keyword arguments

Solution

1. Step 1: Check filter_by argument type

   filter_by only accepts keyword arguments like age=20, not expressions.

2. Step 2: Identify error in usage

   Passing User.age > 20 is an expression, invalid for filter_by.

3. Final Answer:

   filter_by does not accept expressions, only keyword arguments -> Option D

4. Quick Check:

   filter_by = keyword args only [OK]

Hint: filter_by needs simple key=value pairs, not expressions [OK]

Common Mistakes:

• Passing expressions to filter_by
• Confusing filter_by with filter syntax
• Expecting filter_by to parse conditions

5. You want to find all users whose name starts with 'J' and are younger than 40. Which query correctly uses filter and filter_by together?

hard

A. User.query.filter(User.name.like('J%')).filter(User.age < 40).all()

B. User.query.filter_by(name='J%').filter_by(age < 40).all()

C. User.query.filter(User.name.like('J%')).filter_by(age=40).all()

D. User.query.filter_by(name.like('J%')).filter(User.age < 40).all()

Solution

1. Step 1: Use filter for complex conditions

   Checking if name starts with 'J' requires like('J%'), which is an expression needing filter.

2. Step 2: Use filter for age less than 40

   Age < 40 is also an expression, so filter is needed again. filter_by cannot handle expressions.

3. Step 3: Verify option correctness

   User.query.filter(User.name.like('J%')).filter(User.age < 40).all() uses filter twice with correct expressions. Others misuse filter_by or syntax.

4. Final Answer:

   User.query.filter(User.name.like('J%')).filter(User.age < 40).all() -> Option A

5. Quick Check:

   Use filter for expressions, filter_by for simple equals [OK]

Hint: Use filter for expressions like like() and comparisons [OK]

Common Mistakes:

• Using filter_by with expressions like like() or <
• Mixing filter_by with invalid syntax
• Assuming filter_by can handle partial matches