Sqlalchemy query: select using list of tuples

Question

So I have a list of tuples like [('Alex', 20), ('Steve', 50)]

Now I need to fire a select query on Users table to get matching records.

I tried it like:-

users = [('Alex', 20), ('Steve', 50)]
names = [r[0] for r in users]
ages = [r[1] for r in users]

query = self.session.query(Users).filter(
                      and_(Users.name.in_(names),
                      Users.age.in_(ages)))

I want the records with Alex user of age 20 and Steve user of age 50, but this query would also give me Alex user of age 50.

So this query doesn't really work out. Need some help figuring out this query.

The query is fine - you need to explain what you expect and what's wrong. Could it be you actually want Alex users of age 20 and Steve users of age 50? — sebastian
– sebastian, Commented Sep 15, 2015 at 11:13
Yes right. Because this query will also give me Alex user of age 50. — Barun Sharma
– Barun Sharma, Commented Sep 15, 2015 at 11:18

sebastian · Accepted Answer · 2015-09-15 11:22:24Z

4

To get only users whose name and age matches your users list you'll have to combine the name and age filters with AND clauses and join these in an OR clause. E.g. like this:

user_filters = [and_(Users.name == name, Users.age == age)
                for name, age in users]
self.session.query(Users).filter(or_(*user_filters))

answered Sep 15, 2015 at 11:22

sebastian

9,71628 silver badges56 bronze badges

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

2 Comments

Barun Sharma Over a year ago

Perfect. Thankyou...But or seems not to be needed. Is that really required?

sebastian Over a year ago

It is needed - filter(*user_filters) would join all those filters with AND, which would most probably result in an empty results because something like users.name = "Alex" AND users.name = "Steve" can't be true.

Collectives™ on Stack Overflow

Sqlalchemy query: select using list of tuples

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related