CODEWITHSUNDEEP
pythonsqlalchemyexists
lestat
lestat

Reputation: 1615

sqlalchemy exists for query

How do I check whether data in a query exists?

For example:

users_query = User.query.filter_by(email='[email protected]')

How I can check whether users with that email exist?

I can check this with 

users_query.count()

but how to check it with exists?

Upvotes: 89

Views: 120079

Answers (7)

Theron Luhn
Theron Luhn

Reputation: 4082

The easiest way in SQLAlchemy 1.4/2.0 with the new unified API:

from sqlalchemy import exists


session.scalar(
    exists()
    .where(User.email == '[email protected]')
    .select()
)

Upvotes: 5

pcko1
pcko1

Reputation: 981

2021 Answer for SqlAlchemy 1.4

Refrain from calling .query and instead use select directly chained with .exists as follows:

from sqlalchemy import select

stmt = select(User).where(User.email=="[email protected]").exists()

Source: https://docs.sqlalchemy.org/en/14/core/selectable.html?highlight=exists#sqlalchemy.sql.expression.exists

Upvotes: 16

Danniel Little
Danniel Little

Reputation: 811

it can be done:

from sqlalchemy import select

user = session.scalars(
    select(User).where(User.email=="[email protected]")
).first()

if user:
    pass
else:
    pass

Upvotes: -4

MarredCheese
MarredCheese

Reputation: 20811

For SQL Server, I had to do this:

from sqlalchemy.sql.expression import literal

result = session.query(literal(True)).filter(
    session.query(User)
    .filter_by(email='...')
    .exists()
).scalar()
print(result is not None)

# Resulting query:
# SELECT 1
# WHERE EXISTS (SELECT 1 
# FROM User
# WHERE User.email = '...')

But it's much simpler without EXISTS:

result = (
    session.query(literal(True))
    .filter(User.email == '...')
    .first()
)
print(result is not None)

# Resulting query:
# SELECT TOP 1 1
# FROM User
# WHERE User.email = '...'

Upvotes: 4

Eugene Kovalev
Eugene Kovalev

Reputation: 3837

The most acceptable and readable option for me is

session.query(<Exists instance>).scalar()

like

session.query(User.query.filter(User.id == 1).exists()).scalar()

which returns True or False.

Upvotes: 47

Gary van der Merwe
Gary van der Merwe

Reputation: 9533

There is no way that I know of to do this using the orm query api. But you can drop to a level lower and use exists from sqlalchemy.sql.expression:

from sqlalchemy.sql.expression import select, exists

users_exists_select = select((exists(users_query.statement),)) 
print engine.execute(users_exists_select).scalar()

Upvotes: 17

Cito
Cito

Reputation: 5613

The following solution is a bit simpler:

from sqlalchemy.sql import exists

print session.query(exists().where(User.email == '...')).scalar()

Upvotes: 103

Related Questions