Python Flask-SQLAlchemy 检查表中是否存在行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/32938475/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Flask-SQLAlchemy check if row exists in table
提问by Pav Sidhu
I have a Flask application which uses Flask-SQLAlchemy to connect to a MySQL database.
我有一个 Flask 应用程序，它使用 Flask-SQLAlchemy 连接到 MySQL 数据库。
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 questionwhich uses SQLAlchemy but does not seem to fit with the way Flask-SQLAlchemy works:
我在这个问题上找到了一个使用 SQLAlchemy的解决方案，但似乎不符合 Flask-SQLAlchemy 的工作方式：
from sqlalchemy.sql import exists
print session.query(exists().where(User.email == '...')).scalar()
Thanks.
谢谢。
采纳答案by davidism
Since you only want to see if the user exists, you don't want to query the entire object. Just query the id, it exists if the scalar return is not None.
由于您只想查看用户是否存在，因此您不想查询整个对象。只需查询id，如果标量返回不是None，它就存在。
exists = db.session.query(User.id).filter_by(name='davidism').scalar() is not None
SELECT user.id AS user_id
FROM user
WHERE user.name = ?
The second query you showed also works fine, Flask-SQLAlchemy does nothing to prevent any type of query that SQLAlchemy can make. This returns Falseor Trueinstead of Noneor an id like above, but it is slightlymore expensive because it uses a subquery.
您展示的第二个查询也可以正常工作，Flask-SQLAlchemy 不会阻止 SQLAlchemy 可以进行的任何类型的查询。这将返回FalseorTrue而不是Noneor 像上面一样的 id，但它稍微贵一些，因为它使用子查询。
exists = db.session.query(db.exists().where(User.name == 'davidism')).scalar()
SELECT EXISTS (SELECT *
FROM user
WHERE user.name = ?) AS anon_1
回答by lyschoening
Wrap a .exists()query in another session.query()with a scalar()call at the end. SQLAlchemy will produce an optimized EXISTSquery that returns Trueor False.
将.exists()查询包装在另一个查询中session.query()，scalar()并在最后进行调用。SQLAlchemy 将生成一个优化的EXISTS查询，返回True或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 potentiallymore 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.
虽然由于子查询，它可能更昂贵，但它更清楚查询的是什么。它也可能更好，db.exists().where(...)因为它选择一个常量而不是整行。
回答by kblomqvist
Forgive the hiHymaning but ... with the instructions given here I made the following WTForm validator to check uniqueness of the field
原谅劫持，但是...按照此处给出的说明，我制作了以下 WTForm 验证器来检查该字段的唯一性
class Unique(object):
def __init__(self, column, session, message="Already exists."):
self.column = column
self.session = session
self.message = message
def __call__(self, form, field):
if field.data == field.object_data:
return # Field value equals to existing value. That's ok.
model = self.column.class_
query = model.query.filter(self.column == field.data).exists()
if self.session.query(query).scalar():
raise ValidationError(self.message)
It would be used like this
它会像这样使用
class Register(Form):
email = EmailField('Email', [Unique(User.email, db.session)])
However, I would like to have API which does not need db session as a second param
但是，我想拥有不需要 db session 作为第二个参数的 API
class Register(Form):
email = EmailField('Email', [Unique(User.email)])
Is there any way to get db session from model? Without session it seems to be impossible to avoid loading the entire object to check its existence.
有没有办法从模型中获取数据库会话？没有会话似乎不可能避免加载整个对象以检查其存在。
回答by mobime
Think there is a typo in davidism's answer, this works for me:
认为 davidism 的答案有错别字，这对我有用：
exists = db.session.query(**User**).filter_by(name='davidism').scalar() is not None
回答by Anna
bool(User.query.filter_by(name='John Smith').first())
It will return Falseif objects with this name doesn't exist and Trueif it exists.
False如果具有此名称的对象不存在并且True存在，它将返回。
