问题描述
我试图找到一种方法让 SQLAlchemy 生成以下形式的查询:
select * from t where (a,b) in ((a1,b1),(a2,b2));这可能吗?
如果没有,有什么模拟方法的建议吗?
好吧,感谢上面的 Hao Lian,我想出了一个功能性但痛苦的解决方案.
假设我们有一个声明式的映射类,Clazz
,以及一个复合主键值元组的list
,values
(编辑为使用更好的(IMO)sql 生成样式):
此解决方案适用于复合主键或简单主键.不过对于简单的主键,它可能比 col.in_(keys)
慢一点.
我仍然对更好的方法的建议感兴趣,但这种方法目前有效,并且比 or_(and_(conditions))
方法或 输入键:do_stuff(q.get(key))
方式.
I'm trying to find a way to cause SQLAlchemy to generate a query of the following form:
select * from t where (a,b) in ((a1,b1),(a2,b2));
Is this possible?
If not, any suggestions on a way to emulate it?
Well, thanks to Hao Lian above, I came up with a functional if painful solution.
Assume that we have a declarative-style mapped class, Clazz
, and a list
of tuples of compound primary key values, values
(Edited to use a better (IMO) sql generation style):
from sqlalchemy.sql.expression import text,bindparam ... def __gParams(self, f, vs, ts, bs): for j,v in enumerate(vs): key = f % (j+97) bs.append(bindparam(key, value=v, type_=ts[j])) yield ':%s' % key def __gRows(self, ts, values, bs): for i,vs in enumerate(values): f = '%%c%d' % i yield '(%s)' % ', '.join(self.__gParams(f, vs, ts, bs)) def __gKeys(self, k, ts): for c in k: ts.append(c.type) yield str(c) def __makeSql(self,Clazz, values): t = [] b = [] return text( '(%s) in (%s)' % ( ', '.join(self.__gKeys(Clazz.__table__.primary_key,t)), ', '.join(self.__gRows(t,values,b))), bindparams=b)
This solution works for compound or simple primary keys. It's probably marginally slower than the col.in_(keys)
for simple primary keys though.
I'm still interested in suggestions of better ways to do this, but this way is working for now and performs noticeably better than the or_(and_(conditions))
way, or the for key in keys: do_stuff(q.get(key))
way.
这篇关于Sqlalchemy 复杂 in_ 子句与元组列表中的元组的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!