2

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()
zet
  • 71
  • 2
  • 8
  • 1
    Use [`in_`](http://docs.sqlalchemy.org/en/latest/core/sqlelement.html#sqlalchemy.sql.expression.ColumnElement.in_). – univerio Aug 22 '16 at 21:44

1 Answers1

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

The Aelfinn
  • 13,649
  • 2
  • 54
  • 45
  • 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? – zet Aug 22 '16 at 22:11
  • @zet "all" instead of "any" requires a drastically different query. See [this question](http://stackoverflow.com/questions/11468572/postgresql-where-all-in-array). – univerio Aug 22 '16 at 22:15