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.
SELECT id FROM users WHERE email=email OR name=name; How to achieve that in Flask-SQLAlchemy?
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 
& for and?
&, | and ~ overload the corresponding filter expressions and_ (which is the default logic join type, therefore only valuable as code clarification), or_, and not_. See docs.sqlalchemy.org/en/13/core/sqlelement.html.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.
filter_by is unpythonic. If anything it seems much more pythonic to me to use kwargs to check valuesI 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
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/
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)) 
from flask_sqlalchemy import or_but that didn't work.