Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Flask-SQLAlchemy check if row exists in table

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.

like image 977
Pav Sidhu Avatar asked Oct 04 '15 21:10

Pav Sidhu


3 Answers

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
like image 193
davidism Avatar answered Oct 28 '22 23:10

davidism


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.

like image 26
Anna Avatar answered Oct 29 '22 00:10

Anna


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.

like image 23
lyschoening Avatar answered Oct 29 '22 00:10

lyschoening