how to implement sql like "select a from table1 where (a,b) in ( (1,1),(2,2) )" in sqlalchemy

Question

like this way:

q = session.query(table1).filter(table1.a==1).all()

Answer 1

The query that you propose

SELECT a FROM table1 WHERE (a, b) IN ((1, 1), (2, 2))

does not seem to be valid in SQLite. From the docs on row-value comparisons:

For a row-value IN operator, the left-hand side (hereafter "LHS") can be either a parenthesized list of values or a subquery with multiple columns. But the right-hand side (hereafter "RHS") must be a subquery expression.

However, assuming you're happy to use a subquery on the RHS, this would work:

with Session() as s:
    subq = (
        s.query(Table1.a)
        .filter(
            sa.or_(
                sa.and_((Table1.a == 1), (Table1.b == 1)),
                sa.and_((Table1.a == 2), (Table1.b == 2)),
            )
        )
        .subquery('t1')
    )
    q = s.query(Table1).filter(sa.tuple_(Table1.a, Table1.b).in_(subq))

The key parts are

• using tuple_ to construct the row-value on the LHS
• using a subquery to construct the RHS