Python Friday #77: Filters in SQLAlchemy Core

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 how we can use filters in SQLAlchemy Core.

This post is part of my journey to learn Python. You can find the other parts of this series here. You find the code for this post in my PythonFriday repository on GitHub.

 

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 the section about metadata from the last post to use filters on the employee table.

To print out the results in my script, I wrote a little helper function print_result():

 

Overview on the filters in SQLAlchemy Core

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

Filter	Syntax
ALL	select(employees)
EQUALS	select(employees).where(employees.c.Id == 1)
NOT EQUALS	select(employees).where(employees.c.Id != 1)
GREATER THAN	select(employees).where(employees.c.Id > 5)
LESS THAN	select(employees).where(employees.c.Id < 5)
LIKE	select(employees).where(employees.c.LastName.like(“Pe%k”))
	select(employees).where(employees.c.LastName.contains(“u”))
	select(employees).where(employees.c.LastName.startswith(“D”))
	select(employees).where(employees.c.LastName.endswith(“n”))
NOT LIKE	select(employees).where(employees.c.LastName.not_like(“Pe%k”))
IN	select(employees).where(employees.c.Id.in_([1,2,3]))
NOT IN	select(employees).where(employees.c.Id.not_in([1,2,3]))
AND	select(employees).where(filterA & filterB)
	select(employees).where(and_(filterA, filterB))
	select(employees).where(filterA).where(filterB))
OR	select(employees).where(filterA | filterB)
	select(employees).where(or_(filterA, filterB))

 

Select all rows

If we do not specify a where() clause, our query will get us all rows in a database:

 

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:

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

 

Greater than / less than

If we want numerical values that are greater than a specific value, we can use the > operator:

For smaller values we can use < instead:

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:

The opposite effect has the not_like() method:

 

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:

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

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

 

In / not in

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

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:

 

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 with the & operator of Python:

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

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

 

Combine filters with OR

If we want multiple filters where at least one is true, we can create an OR in SQL with the | operator of Python:

You can get the same result with the or_() method:

 

Next

Relational databases are great in keeping your data in a consistent state. Next week we look at how SQLAlchemy Core builds relationships between tables.