Querying with filter and filter_by in Flask - Mini Project: Build & Apply

Querying with filter and filter_by in Flask

📖 Scenario: You are building a simple Flask app to manage a list of books in a library. You want to find books by their author or by their publication year using Flask-SQLAlchemy queries.

🎯 Goal: Learn how to use filter and filter_by methods in Flask-SQLAlchemy to query the database for specific books.

📋 What You'll Learn

Create a Book model with id, title, author, and year fields

Add a configuration variable search_year with the value 2020

Write a query using filter_by to find books by author 'Alice'

Write a query using filter to find books published after search_year

💡 Why This Matters

🌍 Real World

Filtering database records is common in web apps to show users only relevant data, like books by a certain author or recent publications.

💼 Career

Knowing how to query databases efficiently with filter and filter_by is essential for backend web developers working with Flask and SQL databases.

1

Create the Book model

Create a Flask-SQLAlchemy model called Book with columns: id (integer primary key), title (string), author (string), and year (integer).

Flask

from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()

# Define the Book model here

Hint

Use db.Column to define each field with the correct type. Remember primary_key=True for id.

2

Add a search_year variable

Add a variable called search_year and set it to 2020.

Flask

from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()

class Book(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(100))
    author = db.Column(db.String(50))
    year = db.Column(db.Integer)

# Define search_year variable here

Hint

Just create a variable named search_year and assign it the number 2020.

3

Query books by author using filter_by

Write a query called books_by_alice using Book.query.filter_by(author='Alice').all() to get all books where the author is 'Alice'.

Flask

from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()

class Book(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(100))
    author = db.Column(db.String(50))
    year = db.Column(db.Integer)

search_year = 2020

# Query books by author 'Alice' using filter_by

Hint

Use filter_by(author='Alice') on Book.query and call .all() to get the list.

4

Query books published after search_year using filter

Write a query called books_after_year using Book.query.filter(Book.year > search_year).all() to get all books published after search_year.

Flask

from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()

class Book(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(100))
    author = db.Column(db.String(50))
    year = db.Column(db.Integer)

search_year = 2020

books_by_alice = Book.query.filter_by(author='Alice').all()

# Query books published after search_year using filter

Hint

Use filter(Book.year > search_year) on Book.query and call .all() to get the list.

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