How to Use filter in Flask-SQLAlchemy: Syntax and Examples

In Flask-SQLAlchemy, use the filter() method on a query object to select records matching specific conditions. It takes expressions like Model.column == value and returns a filtered query you can execute with .all() or .first().

Syntax

The filter() method is called on a query object to narrow down results based on conditions.

• Model.query.filter(condition): Starts a query filtered by condition.
• condition: A comparison like Model.column == value or other SQL expressions.
• Returns a query object that you can further refine or execute.

python

filtered_query = Model.query.filter(Model.column == value)
results = filtered_query.all()

Example

This example shows how to filter users by their username in a Flask app using Flask-SQLAlchemy.

python

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)
    username = db.Column(db.String(80), unique=True, nullable=False)

with app.app_context():
    db.create_all()
    # Add sample users
    db.session.add_all([
        User(username='alice'),
        User(username='bob'),
        User(username='carol')
    ])
    db.session.commit()

    # Filter users with username 'bob'
    bob_user = User.query.filter(User.username == 'bob').first()
    print(bob_user.username if bob_user else 'No user found')

Output

bob

Common Pitfalls

Common mistakes when using filter() include:

• Forgetting to call .all() or .first() to execute the query, which returns a query object, not results.
• Using Python == outside of SQLAlchemy expressions, which won't work for filtering.
• Not importing or defining the model correctly before querying.

python

wrong = User.query.filter(User.username = 'bob')  # SyntaxError: use == for comparison
right = User.query.filter(User.username == 'bob').first()

Quick Reference

Method	Description
filter(condition)	Filters query results by the given condition
filter_by(**kwargs)	Filters by keyword arguments matching column names
all()	Executes query and returns all results as a list
first()	Executes query and returns the first result or None

Key Takeaways

Use filter() with SQLAlchemy expressions like Model.column == value to narrow query results.

Always call .all() or .first() to get actual data from a filtered query.

Avoid using single equals = inside filter(); use double equals == for comparisons.

You can chain multiple filter() calls to combine conditions with AND logic.

Use filter_by() for simpler keyword argument filtering when no complex expressions are needed.