Calling exists() in sqlalchemy with multiple values in python

Question

I am trying to find out if there is any nicer way I can check if a table of users contains a group of names instead of checking them one at a time

This is what I am using to currently check the User table one at a time which gives me True or False if the user exists in the table:

ret = session.query(exists().where(Users.name == 'Jack')).scalar()

So is there a way to do this:

ret = session.query(exists().where(Users.name == 'Jack', 'Bob', 'Sandy')).scalar()

Rather than this:

ret1 = session.query(exists().where(Users.name == 'Jack')).scalar()  
ret2 = session.query(exists().where(Users.name == 'Bob')).scalar()  
ret3 = session.query(exists().where(Users.name == 'Sandy')).scalar()

Use in_.

univerio
– univerio

2016-08-22 21:44:37 +00:00
Commented Aug 22, 2016 at 21:44 — univerio
– univerio, Commented Aug 22, 2016 at 21:44

The Aelfinn · Accepted Answer · 2016-08-22 21:55:56Z

2

You are correct to use the exists() expression, but combine it with a subquery that leverages the in_() expression.

q = session.query(Users).filter(Users.name.in_(['Jack', 'Bob', 'Sandy']))
# Below will return True or False
at_least_one_user_exists = session.query(q.exists()).scalar()

This translates to the following SQL:

SELECT EXISTS (
    SELECT 1 FROM users WHERE users.name IN ('Jack', 'Bob', 'Sandy')
) AS anon_1

...only the SQLAlchemy described above query will return True or False

answered Aug 22, 2016 at 21:55

The Aelfinn

17.9k3 gold badges57 silver badges46 bronze badges

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

2 Comments

zet Over a year ago

thanks! this made a lot of sense! One more question, is there a way for exists to return true or false based off if all users existed?

univerio Over a year ago

@zet "all" instead of "any" requires a drastically different query. See this question.

Collectives™ on Stack Overflow

Calling exists() in sqlalchemy with multiple values in python

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related