Querying with filter and filter_by in Flask

Introduction

We use filter and filter_by to find specific data in a database easily.

When you want to get users with a specific name from your database.

When you need to find all posts created after a certain date.

When you want to search for products with a price less than a value.

When you want to filter records based on multiple conditions.

Syntax

Flask

query.filter(condition)
query.filter_by(attribute=value)

filter lets you write detailed conditions using expressions.

filter_by is simpler and uses keyword arguments for equality checks.

Examples

Finds all users older than 20.

Flask

User.query.filter(User.age > 20).all()

Finds the first user named Alice.

Flask

User.query.filter_by(name='Alice').first()

Finds all posts with 'Flask' in the title.

Flask

Post.query.filter(Post.title.like('%Flask%')).all()

Sample Program

This example creates a simple user table in memory, adds three users, and then shows how to use filter and filter_by to find users by age and name.

Flask

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///:memory:'
db = SQLAlchemy(app)

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50))
    age = db.Column(db.Integer)

with app.app_context():
    db.create_all()
    db.session.add_all([
        User(name='Alice', age=30),
        User(name='Bob', age=20),
        User(name='Charlie', age=25)
    ])
    db.session.commit()

    # Using filter to find users older than 21
    users_over_21 = User.query.filter(User.age > 21).all()
    print('Users older than 21:')
    for user in users_over_21:
        print(f'{user.name} ({user.age})')

    # Using filter_by to find user named Bob
    bob = User.query.filter_by(name='Bob').first()
    print('\nUser named Bob:')
    print(f'{bob.name} ({bob.age})')

Important Notes

filter accepts complex conditions like greater than, less than, or like.

filter_by only works with simple equality checks using keyword arguments.

Always call .all() or .first() to get results from the query.

Summary

filter is for detailed conditions using expressions.

filter_by is simpler for checking if fields equal values.

Use these to find exactly the data you want from your database.