zet zet - 1 year ago 172
MySQL Question

Calling exists() in sqlalchemy with multiple values in python

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()

Answer Source

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 1 FROM users WHERE users.name IN ('Jack', 'Bob', 'Sandy')
) AS anon_1

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download