在SQLAlchemy中使用OR

小开

最佳答案

from sqlalchemy import or_
filter(or_(User.name == 'ed', User.name == 'wendy'))

小开

SQLAlchemy重载了位操作符&， |和~，所以你可以使用这些操作符来代替or_()和and_()(像在Bastien的回答中那样)的丑陋和难以阅读的前缀语法:

.filter((AddressBook.lastname == 'bulger') | (AddressBook.firstname == 'whitey'))

注意，由于按位操作符的优先级，括号为没有可选的。

所以你的整个查询可以是这样的:

addr = session.query(AddressBook) \
.filter(AddressBook.city == "boston") \
.filter((AddressBook.lastname == 'bulger') | (AddressBook.firstname == 'whitey'))

小开

or_()函数在OR查询组件数量未知的情况下可能有用。

例如，让我们假设我们正在创建一个带有几个可选过滤器的REST服务，如果任何过滤器返回true，则应该返回记录。另一方面，如果请求中没有定义参数，则查询不应更改。如果没有or_()函数，我们必须这样做:

query = Book.query
if filter.title and filter.author:
query = query.filter((Book.title.ilike(filter.title))|(Book.author.ilike(filter.author)))
else if filter.title:
query = query.filter(Book.title.ilike(filter.title))
else if filter.author:
query = query.filter(Book.author.ilike(filter.author))

使用or_()函数，它可以被重写为:

query = Book.query
not_null_filters = []
if filter.title:
not_null_filters.append(Book.title.ilike(filter.title))
if filter.author:
not_null_filters.append(Book.author.ilike(filter.author))


if len(not_null_filters) > 0:
query = query.filter(or_(*not_null_filters))

小开

这真的很有帮助。下面是我对任何给定表的实现:

def sql_replace(self, tableobject, dictargs):


#missing check of table object is valid
primarykeys = [key.name for key in inspect(tableobject).primary_key]


filterargs = []
for primkeys in primarykeys:
if dictargs[primkeys] is not None:
filterargs.append(getattr(db.RT_eqmtvsdata, primkeys) == dictargs[primkeys])
else:
return


query = select([tableobject]).where(and_(*filterargs))


if self.r_ExecuteAndErrorChk2(query)[primarykeys[0]] is not None:
# update
filter = and_(*filterargs)
query = tableobject.__table__.update().values(dictargs).where(filter)
return self.w_ExecuteAndErrorChk2(query)


else:
query = tableobject.__table__.insert().values(dictargs)
return self.w_ExecuteAndErrorChk2(query)


# example usage
inrow = {'eqmtvs_id': eqmtvsid, 'datetime': dtime, 'param_id': paramid}


self.sql_replace(tableobject=db.RT_eqmtvsdata, dictargs=inrow)

小开

对于SQLAlchemy ORM 2.0， |和or_都被接受。

文档 .

from sqlalchemy.future import select
from sqlalchemy.sql import or_




query = select(User).where(or_(User.name == 'ed', User.name == 'wendy'))
print(query)


# also possible:


query = select(User).where((User.name == 'ed') | (User.name == 'wendy'))
print(query)

小开

如果你需要应用或条件，如果条件满足，过滤器可以存储在变量中，并用管道链接它们。这里有一个例子

 class Student(db.Model):
__tablename__ = "student"
id = Column(Integer, primary_key=True)
name = Column(String, nullable=False)


def get_by_name(self, name):
condition = # Code Here for condition
filters = (Student.name == "Stack") | (Student.name == "Overflow") if
condition else (Student.name == "Stack")
query = Student.query.filter(filters).order_by(Student.id.asc())