Using OR in SQLAlchemy

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'))

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.queryif 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.querynot_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))