Sqlalchemy Complex In_ Clause With Tuple In List Of Tuples

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 suggestion

Solution 1:

Use tuple_:

keys = [(a1, b1), (a2, b2)]
session.query(T).filter(tuple_(T.a, T.b).in_(keys)).all()

http://docs.sqlalchemy.org/en/latest/core/sqlelement.html#sqlalchemy.sql.expression.tuple_

Solution 2:

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.

Solution 3:

see the tuple_ construct in SQLAlchemy 0.6

Solution 4:

Simple painless approach with text clause:

from sqlalchemy import text

keys  = [('a1', 'b1'), ('a2', 'b2')]
statement = text(f'SELECT t.* FROM table t WHERE (t.a, t.b) in :keys')
session.execute(statement, {'keys': tuple(keys)})

Just required to be sure that keys is the array of tuple pairs.