Tuple matching using SQLAlchemy

Let's say I have a db of Customers. I want to fetch data and filter rows by exact pairs.

I want to do the following using sqlalchemy:

SELECT first_name, age
FROM Customers
WHERE (first_name, age) in (('John', '31'),('Robert', '22'));

I know I can do this:


SELECT first_name, age
FROM Customers
WHERE first_name in ('John', 'Robert') and age in ('31','22');

like this:

with get_mariadb_session() as session:
    query = select(
        Customers.name, 
        Customers.age, 
        Customers.email
    ).select_from(
        Customers
    ).where(
        and_(Customers.name.in_(names), Customers.age.in_(ages))
    )
    res = session.execute(query)

But this might return a result set with a record ('John','22') too.

Is it possible to do tuple matching in sqlalchemy?

Solution

You can do this by wrapping the LHS with sqlalchemy.tuple_

import sqlalchemy as sa
...
with Session() as s:
    q = (
        sa.select(Customer.name, Customer.age)
        .select_from(Customer)
        .where(
            sa.tuple_(Customer.name, Customer.age).in_(
                [("John", "31"), ("Robert", "22")]
            )
        )
    )
    for customer in s.execute(q):
        print(f"{customer.name = } {customer.age = }")