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

Need a 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

Need a 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

Need a 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

Need a hint?

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