How to write multi column in clause with sqlalchemy

mysql, python, sqlalchemy

Solution

I ended up using the test() based solution: generated "(a,b) in ((:a1, :b1), (:a2,:b2), ...)" with named bind vars and generating dictionary with bind vars' values.

params = {}
for counter, r in enumerate(records):
    a_param = "a%s" % counter
    params[a_param] = r['a']
    b_param = "b%s" % counter
    params[b_param] = r['b']
    pair_text = "(:%s,:%s)" % (a_param, b_param)
    enum_pairs.append(pair_text)
multicol_in_enumeration = ','.join(enum_pairs)
multicol_in_clause = text(
    " (a,b) in (" + multicol_in_enumeration + ")")
q = session.query(Table.id, Table.a,
                            Table.b).filter(multicol_in_clause).params(params)

Another option I thought about using mysql upserts but this would make whole included even less portable for the other db engine then using multicolumn in clause.

Update SQLAlchemy has sqlalchemy.sql.expression.tuple_(*clauses, **kw) construct that can be used for the same purpose. (I haven't tried it yet)

Problem

Please suggest is there way to write query multi-column in clause using SQLAlchemy? Here is example of the actual query: ``` SELECT url FROM pages WHERE (url_crc, url) IN ((2752937066, 'http://members.aye.net/~gharris/blog/'), (3799762538, 'http://www.coxandforkum.com/')); ``` I have a table that has two columns primary key and I'm hoping to avoid adding one more key just to be used as an index. PS I'm using mysql DB. Update: This query will be used for batch processing - so I would need to put few hundreds pairs into the in clause. With IN clause approach I hope to know fixed limit of how many pairs I can stick into one query. Like Oracle has 1000 enum limit by default. Using AND/OR combination might be limited by the length of the query in chars. Which would be variable and less predictable.

Original source