Sqlalchemy complex in_ clause

Question

I'm trying to find a way to cause sqlalchemy to generate sql 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?

Thanks kindly!

Answer 1

Standard caveat: I'm no expert in the large and twisty ecosystem that is SQLAlchemy.

So let's say you have a Table named stocks and a Session named session. Then the query would just be something like

x = "(stocks.person, stocks.number) IN ((100, 100), (200, 200))"
session.query(stocks).filter(x).all()

A good rule of thumb is that SQLAlchemy will accept raw SQL in most places where it looks like it might be generated, such as the filter method.

However, I don't believe there's a way to do it without raw SQL. The in_ operator seems to be defined only on Columns rather than tuples of columns like you have in your example. (Also, this only works on SQL implementations that support it--SQLite in particular seems to not support this in the quick examples I ran. You also have to be careful in qualifying the columns in the left tuple, especially if SQLAlchemy kindly handled the table creation.)

Answer 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.

Answer 3

see the tuple_ construct in SQLAlchemy 0.6