Flask-SQLAlchemy 检查表中是否存在行 [英] Flask-SQLAlchemy check if row exists in table

问题描述

我有一个 Flask 应用程序，它使用 Flask-SQLAlchemy 连接到 MySQL 数据库.

我希望能够检查表中是否存在一行.我将如何修改这样的查询以检查该行是否存在:

db.session.query(User).filter_by(name='John Smith')

我在 this question 上找到了一个解决方案，它使用 SQLAlchemy 但似乎不适合 Flask-SQLAlchemy 的工作方式:

from sqlalchemy.sql import 存在打印 session.query(exists().where(User.email == '...')).scalar()

谢谢.

解决方案

由于您只想查看用户是否存在，因此您不想查询整个对象.只查询id，如果标量返回不为None则存在.

exists = db.session.query(User.id).filter_by(name='davidism').first() 不是 None

SELECT user.id AS user_id来自用户哪里用户名=?

如果您知道 name(或您查询的任何字段)是唯一的，您可以使用 scalar 而不是 first.>

您展示的第二个查询也可以正常工作，Flask-SQLAlchemy 不会阻止 SQLAlchemy 可以进行的任何类型的查询.这将返回 False 或 True 而不是 None 或类似上面的 id，但它稍微更昂贵，因为它使用子查询.

exists = db.session.query(db.exists().where(User.name == 'davidism')).scalar()

SELECT EXISTS (SELECT *来自用户WHERE user.name = ?) AS anon_1

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.

解决方案

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

这篇关于Flask-SQLAlchemy 检查表中是否存在行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！