Flask-SQLAlchemy check if row exists in table
Solution 1
Since you only want to see if the user exists, you don't want to query the entire object. Only query the id, it exists if the scalar return is not None.
exists = db.session.query(User.id).filter_by(name='davidism').first() is not None
SELECT user.id AS user_id
FROM user
WHERE user.name = ?
If you know name
(or whatever field you're querying) is unique, you can use scalar
instead of first
.
The second query you showed also works fine, Flask-SQLAlchemy does nothing to prevent any type of query that SQLAlchemy can make. This returns False
or True
instead of None
or an id like above, but it is slightly more expensive because it uses a subquery.
exists = db.session.query(db.exists().where(User.name == 'davidism')).scalar()
SELECT EXISTS (SELECT *
FROM user
WHERE user.name = ?) AS anon_1
Solution 2
bool(User.query.filter_by(name='John Smith').first())
It will return False
if objects with this name doesn't exist and True
if it exists.
Solution 3
Wrap a .exists()
query in another session.query()
with a scalar()
call at the end. SQLAlchemy will produce an optimized EXISTS
query that returns True
or False
.
exists = db.session.query(
db.session.query(User).filter_by(name='John Smith').exists()
).scalar()
SELECT EXISTS (SELECT 1
FROM user
WHERE user.name = ?) AS anon_1
While it's potentially more expensive due to the subquery, it's more clear about what's being queried. It may also be preferable over db.exists().where(...)
because it selects a constant instead of the full row.
Related videos on Youtube
Pav Sidhu
I like progressive web apps, neural networks and designing user experiences.
Updated on February 23, 2021Comments
-
Pav Sidhu about 3 years
I have a Flask application which uses Flask-SQLAlchemy to connect to a MySQL database.
I would like to be able to check whether a row is present in a table. How would I modify a query like so to check the row exists:
db.session.query(User).filter_by(name='John Smith')
I found a solution on this question which uses SQLAlchemy but does not seem to fit with the way Flask-SQLAlchemy works:
from sqlalchemy.sql import exists print session.query(exists().where(User.email == '...')).scalar()
Thanks.
-
Daniel Roseman over 8 yearsIn what way does the first query not check that the row exists?
-
Pav Sidhu over 8 yearsI would like the query to return
True
orFalse
if the row exists.
-
-
colorlace over 3 yearsI found this answer useful, but I had to change it to
bool(session.query(User).filter_by(name='John Smith').first())
-
Nuclear03020704 about 3 yearsI don't think adding asterisks in the
User
part helps. -
Peter Kilczuk almost 3 yearsIt's working, but there is no need to fetch the whole User just to find if it exists.