#82: Filters in SQLAlchemy ORM

When we access our database, we most often only want to work with a few specific rows and not the whole table. Let us look what syntax SQLAlchemy ORM uses to filter our rows.

Pre-requisites

To follow along, please make sure that you have SQLAlchemy installed and that you have the Northwind database for SQLite next to your Python script. We need all the code from Creating the Foundation for SQLAlchemy ORM to work with the Employee table.

Overview on the filters in SQLAlchemy ORM

Here is an overview of the filters you can use with SQLAlchemy ORM:

Filter	Syntax
ALL	session.query(Employee)
EQUALS	session.query(Employee).filter(Employee.id == 1)
NOT EQUALS	session.query(Employee).filter(Employee.id != 1)
GREATER THAN	session.query(Employee).filter(Employee.id > 5)
LESS THAN	session.query(Employee).filter(Employee.id < 5)
LIKE	session.query(Employee).filter(Employee.last_name.like("Pe%k"))
	session.query(Employee).filter(Employee.last_name.contains("u"))
	session.query(Employee).filter(Employee.last_name.startswith("D"))
	session.query(Employee).filter(Employee.last_name.endswith("n"))
NOT LIKE	session.query(Employee).filter(Employee.last_name.not_like("Pe%k"))
IN	session.query(Employee).filter(Employee.id.in_([1,2,3]))
NOT IN	session.query(Employee).filter(Employee.id.not_in([1,2,3]))
	session.query(Employee).filter(~Employee.id.in_([1,2,3]))
AND	session.query(Employee).filter(filterA, filterB)
	session.query(Employee).filter(and_(filterA, filterB))
	session.query(Employee).filter(filterA).filter(filterB))
OR	session.query(Employee).filter(or_(filterA, filterB))

Select all rows

If we do not specify any filters, our query will get us all rows in a database table:

def query_all():
    session = db_session.factory()

    for employe in session.query(Employee):
        print(employe)

    session.close()

Equals / not equals

We can filter the rows for a column that is equal to a specific value with the same \== operator as we use in other parts of Python:

def query_equals():
    session = db_session.factory()

    for employe in session.query(Employee).\
        filter(Employee.id == 1):
        print(employe)

    session.close()  

If we want the opposite, we can use the != operator (not equals):

def query_not_equals():
    session = db_session.factory()

    for employe in session.query(Employee).\
        filter(Employee.id != 1):
        print(employe)

    session.close() 

Greater than / less than

If we want numerical values that are greater than (>)a specific value, we can use this code:

def query_greather_than():
    session = db_session.factory()

    for employe in session.query(Employee).\
        filter(Employee.id > 5):
        print(employe)

    session.close() 

For smaller values we can use < instead:

def query_less_than():
    session = db_session.factory()

    for employe in session.query(Employee).\
        filter(Employee.id < 3):
        print(employe)

    session.close() 

The same works with greater or equals (>=) and less or equals (<=).

Like / not like

If we want to match part of a string, we can use the like() method and use % as a placeholder:

def query_like():
    session = db_session.factory()

    for employe in session.query(Employee).\
        filter(Employee.last_name.like("Pe%k")):
        print(employe)

    session.close() 

The opposite effect has the not_like() method:

def query_not_like():
    session = db_session.factory()

    for employe in session.query(Employee).\
        filter(Employee.last_name.not_like("Pe%k")):
        print(employe)

    session.close() 

Contains, startswith and endswith

There are 3 methods for a bit more convenience that let us focus on the place in which we want to match a string. They translate to LIKE in SQL, but we do not need to set the % placeholder. If the string we search for can be anywhere, we use the contains() method:

def query_contains():
    session = db_session.factory()

    for employe in session.query(Employee).\
        filter(Employee.last_name.contains("u")):
        print(employe)

    session.close() 

If the string we search is at the beginning, we use the startswith() method:

def query_startswith():
    session = db_session.factory()

    for employe in session.query(Employee).\
        filter(Employee.last_name.startswith("D")):
        print(employe)

    session.close() 

For strings that end in a certain way we can use the endswith() method:

def query_endswith():
    session = db_session.factory()

    for employe in session.query(Employee).\
        filter(Employee.last_name.endswith("n")):
        print(employe)

    session.close()

In / not in

We can filter rows where a value is in a list with the in_() method:

def query_in_():
    session = db_session.factory()

    for employe in session.query(Employee).\
        filter(Employee.id.in_([1,2,3])):
        print(employe)

    session.close()

The word in is a reserved keyword in Python. Therefore, SQLAlchemy cannot use it for its own method and adds a _ at the end.

For the opposite of in_ we can use the not_in() method:

def query_not_in():
    session = db_session.factory()

    for employe in session.query(Employee).\
        filter(Employee.id.not_in([1,2,3])):
        print(employe)

    session.close()  

Sometimes you find code examples that use a ~ operator (bitwise NOT) that you can use to negate the in_() method:

def query_in_negated():
    session = db_session.factory()

    for employe in session.query(Employee).\
        filter(~Employee.id.in_([1,2,3])):
        print(employe)

    session.close()

Combine filters with AND

The power of SQL comes with combining multiple filters in the same query. If we want multiple filters to be true at the same time, we can create an AND in SQL by adding multiple filters separated by a comma (,):

def query_multiple_filter_in_one():
    session = db_session.factory()

    for employe in session.query(Employee).\
        filter(Employee.id == 7, Employee.last_name == "King"):
        print(employe)

    session.close()  

The same effect has the and_() method (the _ is necessary because and is also a reserved keyword in Python):

def query_and_():
    session = db_session.factory()

    for employe in session.query(Employee).\
        filter(and_(Employee.id == 7, Employee.last_name == "King")):
        print(employe)

    session.close()  

To complete the list of possible ways to combine filters with AND, you can add multiple .filter() clauses:

def query_multiple_filter():
    session = db_session.factory()

    for employe in session.query(Employee).\
        filter(Employee.id == 7).\
        filter(Employee.last_name == "King"):
        print(employe)

    session.close()  

In SQLAlchemy Core we used the & operator, but this one does not work in ORM and throws an exception:

TypeError: unsupported operand type(s) for &: 'int' and 'InstrumentedAttribute'

Combine filters with OR

If we want multiple filters where at least one is true, we can create an OR in SQL with the or_() method:

def query_or_():
    session = db_session.factory()

    for employe in session.query(Employee).\
        filter(or_(Employee.id == 2, Employee.id == 3)):
        print(employe)

    session.close()

The Python | operator works in SQLAlchemy Core, but throws an exception in the ORM part:

TypeError: unsupported operand type(s) for |: 'int' and 'InstrumentedAttribute'