sqlalchemy exists for query

Question

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?

Rubenrubens

Answer 1

The following solution is a bit simpler:

from sqlalchemy.sql import exists

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

Jernigan

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

@aikoven: your suggestion results in a Every derived table must have its own alias error. Adding exists(…).select().alias('foo') fixed it (see alias() function). – Avunculate

Answer 2

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.

Fitts

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

This throws following exception: AttributeError: type object 'User' has no attribute 'query' – Nilgai

The .query attribute is specific to Flask-SQLAlchemy. You'd use session.query(session.query(User).filter(User.id == 1).exists()).scalar(). – Rosebud

Answer 3

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

Agentive

Answer 4

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

Triiodomethane

This is SqlAlchemy 1.4 – Cilicia

Attempting to execute this will result in an error because Exists is a clause, not a statement. You need to wrap it in select(). – Jd

Answer 5

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 = '...'

Gogetter

Same for Oracle 12c. Thank you for the solution – Soggy

session.query(cls).filter_by(**kwargs).first() is not None would also work, without importing literal, at least for MySQL 8.0 – Glottochronology

Answer 6

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

Jd

Answer 7

it can be done:

from sqlalchemy import select

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

if user:
    pass
else:
    pass

Elodiaelodie