Sqlalchemy 复杂 in_ 子句与元组列表中的元组 [英] Sqlalchemy complex in_ clause with tuple in list of tuples

问题描述

我试图找到一种方法让 SQLAlchemy 生成以下形式的查询:

<前>select * from t where (a,b) in ((a1,b1),(a2,b2));

这可能吗?

如果没有，有什么模拟方法的建议吗?

解决方案

好吧，感谢上面的 Hao Lian，我想出了一个功能性但痛苦的解决方案.

假设我们有一个声明式的映射类，Clazz，以及一个复合主键值元组的list，values(编辑为使用更好的(IMO)sql 生成样式):

<前>从 sqlalchemy.sql.expression 导入文本，bindparam...def __gParams(self, f, vs, ts, bs):对于 enumerate(vs) 中的 j,v:键 = f % (j+97)bs.append(bindparam(key, value=v, type_=ts[j]))产量 ':%s' % 键def __gRows(self, ts, values, bs):对于 enumerate(values) 中的 i,vs:f = '%%c%d' % i产量 '(%s)' % ', '.join(self.__gParams(f, vs, ts, bs))def __gKeys(self, k, ts):对于 c 中的 k:ts.append(c.type)产量 str(c)def __makeSql(self,Clazz, values):t = []b = []返回文本('(%s) 在 (%s)' % (', '.join(self.__gKeys(Clazz.__table__.primary_key,t)),', '.join(self.__gRows(t,values,b))),绑定参数=b)

此解决方案适用于复合主键或简单主键.不过对于简单的主键，它可能比 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_ 子句与元组列表中的元组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！