我已经查看了文档，我似乎不知道如何在SQLAlchemy中执行OR查询。我只想做这个问题。
SELECT address FROM addressbook WHERE city='boston' AND (lastname='bulger' OR firstname='whitey')
应该是这样的
addr = session.query(AddressBook).filter(City == "boston").filter(????)
当前回答
这真的很有帮助。 以下是我对任何给定表的实现:
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)
其他回答
来自教程:
from sqlalchemy import or_
filter(or_(User.name == 'ed', User.name == 'wendy'))
对于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())
这真的很有帮助。 以下是我对任何给定表的实现:
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重载了按位操作符&、|和~，因此，您可以使用这些操作符来代替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'))
