84

How do I check whether data in a query exists?

For example:

users_query = User.query.filter_by(email='x@x.com')

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?

Nathaniel Jones
  • 939
  • 1
  • 14
  • 25
lestat
  • 1,565
  • 2
  • 14
  • 12

7 Answers7

99

The following solution is a bit simpler:

from sqlalchemy.sql import exists

print session.query(exists().where(User.email == '...')).scalar()
Cito
  • 5,365
  • 28
  • 30
  • 5
    Note that this would give wrong results for polymorphic types. When filtering on both parent and child attributes, the resulting query will select from cartesian product (outer join) of tables. To fix it, you should manually set `FROM` clause via `select_from`: `e = exists(select([1]).select_from(User).where(and_(User.email == '...', ...))).select()` – aikoven Aug 17 '16 at 05:46
  • 1
    @aikoven: your suggestion results in a `Every derived table must have its own alias` error. Adding `exists(…).select().alias('foo')` fixed it (see [alias()](http://docs.sqlalchemy.org/en/latest/core/selectable.html#sqlalchemy.sql.expression.alias) function). – Jens Jan 05 '18 at 10:38
42

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.

Eugene Kovalev
  • 3,407
  • 1
  • 15
  • 17
  • 2
    Also, note that some databases such as SQL Server don’t allow an EXISTS expression to be present in the columns clause of a SELECT. To select a simple boolean value based on the exists as a WHERE, use sqlalchemy.literal: `session.query(literal(True)).filter(q.exists()).scalar()` – kaka Jul 12 '17 at 07:50
  • 10
    This throws following exception: `AttributeError: type object 'User' has no attribute 'query'` – Fusion Sep 26 '19 at 12:15
  • 12
    The `.query` attribute is *specific to Flask-SQLAlchemy*. You'd use `session.query(session.query(User).filter(User.id == 1).exists()).scalar()`. – Martijn Pieters Sep 15 '20 at 10:10
16

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()
Tim Tisdall
  • 9,914
  • 3
  • 52
  • 82
Gary van der Merwe
  • 9,134
  • 3
  • 49
  • 80
12

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=="x@x.com").exists()

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

pcko1
  • 833
  • 12
  • 22
4

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 = '...'
MarredCheese
  • 17,541
  • 8
  • 92
  • 91
0

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

from sqlalchemy import exists


session.scalar(
    exists()
    .where(User.email == 'x@x.com')
    .select()
)
Theron Luhn
  • 3,974
  • 4
  • 37
  • 49
-2

it can be done:

from sqlalchemy import select

user = session.scalars(
    select(User).where(User.email=="x@x.com")
).first()

if user:
    pass
else:
    pass
Danniel Little
  • 746
  • 7
  • 12