How to Query Data Using Flask-SQLAlchemy: Simple Guide

To query data using flask-sqlalchemy, use the model's query attribute like Model.query.filter_by() or Model.query.get() to fetch records. These methods let you retrieve data easily with filters or by primary key.

Syntax

Flask-SQLAlchemy uses the query attribute on your model classes to fetch data from the database. Common methods include:

• Model.query.all() - gets all records.
• Model.query.get(id) - gets a record by primary key.
• Model.query.filter_by(field=value) - filters records by field equality.
• Model.query.filter(condition) - filters with custom conditions.

These return query objects or lists of model instances.

python

User.query.all()
User.query.get(1)
User.query.filter_by(username='alice').first()
User.query.filter(User.age > 20).all()

Example

This example shows how to define a simple User model and query users from the database 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)
    age = db.Column(db.Integer, nullable=False)

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

    # Query all users
    users = User.query.all()
    print('All users:', [user.username for user in users])

    # Query user by primary key
    user = User.query.get(2)
    print('User with ID 2:', user.username if user else 'Not found')

    # Filter users by username
    alice = User.query.filter_by(username='alice').first()
    print('User named alice:', alice.username if alice else 'Not found')

    # Filter users by age condition
    older_users = User.query.filter(User.age > 30).all()
    print('Users older than 30:', [u.username for u in older_users])

Output

All users: ['alice', 'bob', 'carol']
User with ID 2: bob
User named alice: alice
Users older than 30: ['carol']

Common Pitfalls

Common mistakes when querying with Flask-SQLAlchemy include:

• Using filter_by() for complex conditions instead of filter().
• Not calling .first() or .all() to execute the query, leading to no results.
• Confusing get() which only works with primary keys.
• Forgetting to use app.app_context() when querying outside request context.

python

wrong = User.query.filter_by(age=20).all()  # Incorrect: filter_by expects keyword args
right = User.query.filter(User.age > 20).all()  # Correct

no_results = User.query.filter_by(username='nonexistent')  # Missing .first() or .all()
results = User.query.filter_by(username='nonexistent').first()  # Correct

Quick Reference

Here is a quick cheat sheet for querying with Flask-SQLAlchemy:

Method	Description	Returns
Model.query.all()	Get all records	List of model instances
Model.query.get(id)	Get record by primary key	Single model instance or None
Model.query.filter_by(field=value)	Filter by field equality	Query object
Model.query.filter(condition)	Filter by custom condition	Query object
.first()	Get first result	Single model instance or None
.all()	Get all results	List of model instances

Key Takeaways

Use Model.query with methods like all(), get(), and filter_by() to fetch data.

Call .first() or .all() to execute queries and get results.

Use filter() for complex conditions, not filter_by().

Remember get() only works with primary keys.

Always run queries inside Flask app context to avoid errors.