Flask-SQLAlchemy check if row exists in table

Question

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.

In what way does the first query not check that the row exists? — Daniel Roseman
– Daniel Roseman, Commented Oct 4, 2015 at 21:17
I would like the query to return True or False if the row exists. — Pav Sidhu
– Pav Sidhu, Commented Oct 4, 2015 at 21:18

davidism · Accepted Answer · 2021-02-22 14:55:17Z

112

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

edited Feb 22, 2021 at 14:55

answered Oct 5, 2015 at 15:35

davidism

128k31 gold badges417 silver badges348 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

davidism · Accepted Answer · 2018-08-16 17:42:31Z

35

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.

edited Aug 16, 2018 at 17:42

davidism

128k31 gold badges417 silver badges348 bronze badges

answered May 27, 2016 at 12:35

lyschoening

18.8k11 gold badges47 silver badges55 bronze badges

Comments

user5305519 · Accepted Answer · 2020-05-26 06:10:15Z

33

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.

edited May 26, 2020 at 6:10

user5305519

3,3065 gold badges31 silver badges48 bronze badges

answered Apr 15, 2019 at 9:42

Anna

5775 silver badges11 bronze badges

2 Comments

colorlace Over a year ago

I found this answer useful, but I had to change it to bool(session.query(User).filter_by(name='John Smith').first())

Peter Kilczuk Over a year ago

It's working, but there is no need to fetch the whole User just to find if it exists.

Marco Bonelli · Accepted Answer · 2017-04-24 22:41:15Z

-8

Think there is a typo in davidism's answer, this works for me:

exists = db.session.query(**User**).filter_by(name='davidism').scalar() is not None

edited Apr 24, 2017 at 22:41

Marco Bonelli

71.1k21 gold badges129 silver badges153 bronze badges

answered Apr 24, 2017 at 19:49

mobime

1252 silver badges5 bronze badges

2 Comments

Nuclear241 Over a year ago

I don't think adding asterisks in the User part helps.

bfontaine Over a year ago

Note that if you find a typo in an answer, you can suggest an edit rather than adding another answer.

Collectives™ on Stack Overflow

Flask-SQLAlchemy check if row exists in table

4 Answers 4

Comments

Comments

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related