python

Using OR on filter query SQLAlchemy

You can apply OR in SQLAlchemy by importing _or from SQLAlchemy and then passing conditions on models.

from sqlalchemy import or_

session.query(
    UserModel.id,
    UserModel.first_name
).filter(
    or_(
        UserModel.username == 'john', 
        UserModel.username == 'michael'
    )
).all()

In the code snippet, we are applying OR condition on the username column where it will get the records if its username column has the value 'john' or 'michael'.

Using or in a join query

We can use the or_ in an SQLAlchemy query where we are joining multiple table models. We can place a filter in the query based on the values of multiple columns that exist inside different tables.

Code Example

session.query(
    StudentModel.first_name,
    StudentModel.last_name,
    ScoreModel.math_score,
    ScoreModel.english_score,
).join(
    ScoreModel,
    StudentModel.id == ScoreModel.student_id,
).filter(
    or_(
        StudentModel.class_name == "high school",
        ScoreModel.section_name == "c"
    )
).all()

In the above SQLAlchemy query,

We are using two table models - StudentModel and ScoreModel.
The StudentModel columns contain information about students. The ScoreModel columns contain information about the scores that are obtained by students in multiple subjects.
First, we are joining models based on StudentModels id and Foreign key student_id in ScoreModel.
After, joining the tables we are applying filters of class_name that exists in StudentModel and section_name that exists inside the ScoreModel.

Was this helpful?