CODEWITHSUNDEEP
pythonmysqlflask-sqlalchemy
Michael Yousrie
Michael Yousrie

Reputation: 1142

Flask SQLAlchemy filter by value OR another

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?

Upvotes: 18

Views: 62921

Answers (5)

Kinjal Dixit
Kinjal Dixit

Reputation: 7945

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/

Upvotes: 1

Tri
Tri

Reputation: 3039

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=='[email protected]', User.name=="username")).first()

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

Upvotes: 10

Dat TT
Dat TT

Reputation: 3083

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

Upvotes: 0

Sahith Vibudhi
Sahith Vibudhi

Reputation: 5225

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

Upvotes: 6

Jonathan
Jonathan

Reputation: 1402

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='[email protected]')
>>> 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='[email protected]')
>>> by_name_id == by_email_id
True

Upvotes: 43

Related Questions