Using OR in SQLAlchemy

python

sqlalchemy

database-queries

data-manipulation

byNikita Barsukov·Nov 3, 2024

Harness the or_() function from sqlalchemy when implementing OR conditions in queries. Here's a direct approach:

from sqlalchemy import or_
result = session.query(MyModel).filter(or_(MyModel.column1 == 'value1', MyModel.column2 == 'value2')).all()

It retrieves records either containing 'value1' in column1 OR 'value2' in column2 variable.

Straightforward guide to OR expression

Bitwise Operator: A Stylish Alternative

Instead of sticking to the standard or_(), feel free to use bitwise operators such as | for OR, and & for AND. These operators promote readable and crisp queries:

# Because "style | substance" isn't just for fashion magazines.
result = session.query(MyModel).filter((MyModel.column1 == 'value1') | (MyModel.column2 == 'value2')).all()

Building Dynamic Filters

Need to handle dynamic OR conditions? Piece together a list of filters and use or_() for a clean solution:

# Let's mix and match filters like a DJ at a party.
filters = [MyModel.year > 2020, MyModel.color == 'red']
if additional_condition:  # Guest's song request
    filters.append(MyModel.make == 'Toyota')
result = session.query(MyModel).filter(or_(*filters)).all()

Playing Nice with `None` values

The unexpected guest None can crash your query party. Manage such mishaps via careful check and condition:

# Guarding against party crashers!
filter_condition = MyModel.column if some_value is not None else True
result = session.query(MyModel).filter(filter_condition).all()

Quick Data Manipulation Tricks

For data updates, utilize update().values():

# Ready, set, update!
session.query(MyModel).filter(MyModel.id == target_id).update({'column': new_value})
session.commit()

For new data inserts, call insert().values():

# Here’s some fresh data, coming right up!
session.execute(MyModel.insert().values(new_data))
session.commit()

Choose your columns: `select()`

Make use of select() to select different fields before applying the where clause:

# Customizing our treasure hunt!
from sqlalchemy import select
query = select([Model.id, Model.name]).where(or_(Model.status == 'active', Model.role == 'admin'))

Mastering complex queries

Combine select and where functions to assemble complex queries. For advanced operations like replace-or-insert operations, employ the sql_replace function:

# When the dress code calls for something a bit fancier...
session.execute(MyModel.sql_replace().values(replacement_data))
session.commit()

Crafting queries with optional parameters

Create a list of filters to gracefully handle optional parameters:

# Because "everyone's invited" doesn't mean "everyone's coming".
filters = [MyModel.name == name] if name else []
filters += [MyModel.type == type] if type else []
if len(filters) > 0:  # The more, the merrier.
    query = query.filter(or_(*filters))