Querying with the ORM: Select, Filter, and Join
You'll learn how to retrieve and filter data from your database using SQLAlchemy's ORM query interface.
Now that you've defined models and created a session, it's time to ask questions of your database. SQLAlchemy's ORM provides an intuitive, Pythonic way to build queries without writing raw SQL strings.
Building Basic Queries with session.query()
The session.query() method is your starting point for retrieving data. Pass your model class to specify which table you're querying:
from sqlalchemy.orm import Session
# Assuming you have a User model and session already set up
all_users = session.query(User).all() # Returns all User records
first_user = session.query(User).first() # Returns first User or None
Think of session.query() as saying "I want to look at this table" — like opening a specific drawer in a filing cabinet.
Filtering with Conditions
Use .filter() or .filter_by() to narrow your results. The filter_by() method uses simple keyword arguments for equality:
active_users = session.query(User).filter_by(is_active=True).all()
For more complex conditions, use .filter() with model attributes:
young_users = session.query(User).filter(User.age < 30).all()
Performing Joins
When your models have relationships defined, joins become straightforward. Use .join() to combine related tables:
# Assuming User has a relationship to Order model
users_with_orders = session.query(User).join(Order).all()
You can also filter on the joined table and access related data through the relationship attributes you defined in your models.
Key Takeaway: SQLAlchemy's ORM queries use session.query(Model) as the foundation, chaining .filter() or .filter_by() for conditions and .join() for combining related tables — all while staying in Python without writing SQL strings.