python - Using sqlalchemy to query using multiple column where in clause

Question

I'm looking to execute this query using sqlalchemy.

SELECT name,
       age,
       favorite_color,
       favorite_food
FROM kindergarten_classroom
WHERE (favorite_color,
       favorite_food) IN (('lavender','lentil soup'),('black','carrot juice'));

I only want kids that like (lavender AND lentil soup) OR (black and carrot juice). Also, this will probably be a huge list of favorite colors and foods (likely > 10K), so I'll want to do these in large batches.

This is similar, but doesn't get me all of the way there: Sqlalchemy in clause

Answer 1

You want the tuple_ construct:

session.query(...).filter(
    tuple_(favorite_color, favorite_food).in_(
        [('lavender', 'lentil soup'), ('black', 'carrot juice')]
    )
)