Flask SQLAlchemy filter by value OR another

Question

I have a Flask project that interacts with MySQL db through Flask-SQLAlchemy.

My question is, how to select a row from the database based on a value OR another value.

The results I want in SQL looks like this

SELECT id FROM users WHERE email=email OR name=name;

How to achieve that in Flask-SQLAlchemy?

Answer 1

The following may help:

# app.py
from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'url_or_path/to/database'
db = SQLAlchemy(app)

class User(db.Model):
    __tablename__ = 'users'
    id = db.Column(db.Integer, primary_key=True)
    email = db.Column(db.String(50), unique=True)
    name = db.Column(db.String(30))

    def __init__(self, name=None, email=None):
        if not name:
            raise ValueError('\'name\' cannot be None')
        if not email:
            raise ValueError('\'email\' cannot be None')
        self.name = name
        self.email = email

class UserQuery(object):
    @staticmethod
    def get_user_id_by_email_or_name(email=None, name=None):
        user = User.query.filter((User.email == email) | (User.name == name)).first()
        return user.id if hasattr(user, 'id') else None

The '|' can be used inside a filter instead of 'or_'. See Using OR in SQLAlchemy.

You can use like this:

>>> from app import db, User, UserQuery
>>> db.create_all()
>>> user = User(name='stan', email='stan@email.com')
>>> db.session.add(user)
>>> db.session.commit()
>>> by_name_id = UserQuery.get_user_id_by_email_or_name(name='stan')
>>> by_email_id = UserQuery.get_user_id_by_email_or_name(email='stan@email.com')
>>> by_name_id == by_email_id
True

Answer 2

I also needed this case today, I found the nice answer here:

So, we can make OR logic like the below example:

from sqlalchemy import or_
db.session.query(User).filter(or_(User.email=='useremail@example.com', User.name=="username")).first()

When using the filter() expression, you must use proper comparison operators, whereas filter_by() uses a shortened unPythonic form.

Answer 3

I also wanted to have an or along with and condition

I found this after googling around:

# Users whose age is 23 AND (firstname IS alex OR lastname IS watson)
usrs = session.query(User) \
    .filter(User.age === "23") \
    .filter((User.firstname == 'alex') | (User.lastname == 'watson')) \
    .all()

hopefully, it helps other people coming here looking for it

Answer 4

The question is very old, the answer here is for flask 2.x and flask-sqlalchemy 3.0.x

db.session.query is now called legacy query interface. they ask you to instead use db.session.execute

if you are following miguel grinbergs mega flask tutorial and your code is looking something like this:

orders = Order.query.filter_by(supplier_id=company_id, status="Sent").all()

the way to add an or in the where would be something like this:

from sqlalchemy import or_

orders = db.session.execute(
    db.select(Order)
    .where(Order.supplier_id == company_id)
    .where(or_(Order.status == "Sent", Order.status == "Accepted"))
).scalars()

here db is the flask_sqlalchemy.SQLAlchemy instance. Order is a db.Model derived class.

https://flask-sqlalchemy.palletsprojects.com/en/3.0.x/queries/

Answer 5

Just noticed that, it does not allow to use filter_by with '|' condition, at least with python 2.x. You will see "SyntaxError: invalid syntax". For ex:

Rule.query.filter_by((is_active=True) | (id=rule_id))