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

Possible duplicate of stackoverflow.com/q/20461887/1632891

Tim
– Tim

2016-04-01 14:10:40 +00:00
Commented Apr 1, 2016 at 14:10 — Tim
– Tim, Commented Apr 1, 2016 at 14:10

scytale · Accepted Answer · 2017-03-09 14:09:01Z

20

You want the tuple_ construct:

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

edited Mar 9, 2017 at 14:09

scytale

12.7k4 gold badges35 silver badges46 bronze badges

answered Aug 21, 2014 at 23:45

univerio

20.7k3 gold badges73 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using sqlalchemy to query using multiple column where in clause

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related