Using OR in SQLAlchemy

or_() function can be useful in case of unknown number of OR query components.

For example, let's assume that we are creating a REST service with few optional filters, that should return record if any of filters return true. On the other side, if parameter was not defined in a request, our query shouldn't change. Without or_() function we must do something like this:

query = Book.query
if filter.title and filter.author:
    query = query.filter((Book.title.ilike(filter.title))|(Book.author.ilike(filter.author)))
else if filter.title:
    query = query.filter(Book.title.ilike(filter.title))
else if filter.author:
    query = query.filter(Book.author.ilike(filter.author))

With or_() function it can be rewritten to:

query = Book.query
not_null_filters = []
if filter.title:
    not_null_filters.append(Book.title.ilike(filter.title))
if filter.author:
    not_null_filters.append(Book.author.ilike(filter.author))

if len(not_null_filters) > 0:
    query = query.filter(or_(*not_null_filters))

From the tutorial:

from sqlalchemy import or_
filter(or_(User.name == 'ed', User.name == 'wendy'))

SQLAlchemy overloads the bitwise operators &, | and ~ so instead of the ugly and hard-to-read prefix syntax with or_() and and_() (like in Bastien's answer) you can use these operators:

.filter((AddressBook.lastname == 'bulger') | (AddressBook.firstname == 'whitey'))

Note that the parentheses are not optional due to the precedence of the bitwise operators.

So your whole query could look like this:

addr = session.query(AddressBook) \
    .filter(AddressBook.city == "boston") \
    .filter((AddressBook.lastname == 'bulger') | (AddressBook.firstname == 'whitey'))