sqlalchemy exists for query
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
use开发者_开发百科rs_query.count()
but how to check it with exists?
The following solution is a bit simpler:
from sqlalchemy.sql import exists
print session.query(exists().where(User.email == '...')).scalar()
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
.
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()
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
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 = '...'
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()
)
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
精彩评论