How to filter results by multiple columns in SQLAlchemy

Introduction

Filtering results by multiple columns is essential for querying databases effectively. SQLAlchemy, a powerful ORM for Python, provides elegant ways to build complex queries including filters across numerous fields.

Basic Filtering

Before delving into more complex examples, let’s look at the basics of filtering in SQLAlchemy using the filter() method. Assume you have a User model with name and age columns:

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    age = Column(Integer)

# Connect to the database
engine = create_engine('sqlite:///:memory:')
Session = sessionmaker(bind=engine)
Base.metadata.create_all(engine)
session = Session()

# Basic single column filtering
query = session.query(User).filter(User.name == 'Alice')
results = query.all()

This basic filtering allows you to find all users named Alice. To expand our filtering to multiple columns, we can chain filter conditions:

results = session.query(User).filter(User.name == 'Alice', User.age > 30).all()

This will retrieve users named Alice who are older than 30.

AND Conditions

SQLAlchemy provides the and_() function to explicitly define AND conditions when filtering:

from sqlalchemy import and_

results = session.query(User).filter(and_(User.name == 'Alice', User.age > 30)).all()

While chaining filters with commas is equivalent to using and_(), the latter can be more readable, especially with more complex queries.

OR Conditions

To filter using OR conditions, you can use the or_() method:

from sqlalchemy import or_

results = session.query(User).filter(or_(User.name == 'Alice', User.age < 20)).all()

This returns users who are either named Alice or are younger than 20.

Combining AND and OR

Combining AND and OR conditions needs careful parenthesis placement to produce the correct query logic. Below, the and_() nesting ensures that we get users named Alice who are either older than 30 or have a specific ID:

results = session.query(User).filter(and_(User.name == 'Alice', or_(User.age > 30, User.id == 2))).all()

Using filter_by() for Simple Equality Filters

For simple equality-based checks, filter_by() offers a concise syntax:

results = session.query(User).filter_by(name='Alice', age=30).all()

This will return users named Alice who are exactly 30 years old.

Advanced Querying: Joins and Filters

Suppose we have another model, Address, related to User. We can join these tables and filter across both:

class Address(Base):
    __tablename__ = 'addresses'
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('users.id'))
    email = Column(String)

# Advanced querying with a join
results = session.query(User).join(Address).filter(User.name == 'Alice').filter(Address.email.like('%@gmail.com')).all()

This query will return all users named Alice with a Gmail address.

Using Subqueries in Filters

SQLAlchemy also supports using subqueries within filters for more advanced scenarios. Here’s an example:

from sqlalchemy.sql import select

subquery = select([Address.user_id]).where(Address.email.like('%@gmail.com')).alias()
results = session.query(User).filter(User.id.in_(subquery)).all()

This fetches users with a Gmail account, leveraging subqueries for a powerful query structure.

Parameterized Filtering

To prevent SQL injection attacks, always use parameterized queries when the filter conditions incorporate user input:

name_to_find = 'Alice'
age_to_find = 30
results = session.query(User).filter(User.name == name_to_find, User.age == age_to_find).all()

This ensures your filter values are safely escaped.

Using Hybrid Properties for Advanced Filtering

SQLAlchemy’s hybrid properties allow for the definition of Python @property-like methods that can be queried like a regular column, facilitating advanced filtering techniques:

from sqlalchemy.ext.hybrid import hybrid_property

class User(Base):
    # ... existing User model fields ...
    
    @hybrid_property
    def is_adult(self):
        return self.age >= 18

    @is_adult.expression
    def is_adult(cls):
        return cls.age >= 18

# Filtering using a hybrid property
results = session.query(User).filter(User.is_adult == True).all()

This adds a layer of abstraction and can make complex queries more readable.

Conclusion

In conclusion, filtering by multiple columns in SQLAlchemy is a flexible and powerful feature that can be applied in various ways to suit complex querying needs. Utilizing functions like and_(), or_(), and filter_by(), as well as advanced features like subqueries and hybrid properties, can create precise and optimized queries for your application’s data retrieval requirements.