如何在SQLAlchemy，flask，pyhon中处理唯一数据 [英] How to handle unique data in SQLAlchemy, flask, pyhon

问题描述

通常如何在Flask中处理唯一的数据库条目?我的数据库模型中有以下专栏文章:

How do you usually handle unique database entries in Flask? I have the following column in my db model:

bank_address = db.Column(db.String(42), unique=True)

问题是，即使在我无法检查它是否已经存在于数据库中之前，我也会收到错误消息:

The problem is, that even before I can make a check whether it is already in the database or not, I get an error:

检查它是否唯一，然后将其写入db:

Check if it is unique and THEN write into db:

if request.method == 'POST':
    if user.bank_address != request.form['bank_address_field']:
        user.bank_address = request.form['bank_address_field']
        db.session.add(user)
        db.session.commit()

我得到的错误:

sqlalchemy.exc.IntegrityError:(sqlite3.IntegrityError)唯一约束失败:user.bank_address_field [SQL:'UPDATE user SETbank_address_field =?在哪里user.id =?']

sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) UNIQUE constraint failed: user.bank_address_field [SQL: 'UPDATE user SET bank_address_field=? WHERE user.id = ?']

推荐答案

您可以执行以下两项操作之一:

You could do one of two things:

• 对具有该字段的用户进行查询:

• Make a query for users with that field:

if User.query.filter(User.bank_address == request.form['bank_address_field']).first():
    # error, there already is a user using this bank address

这是一个大问题，但是，请参见下文.

This has a big problem, however, see below.

捕获异常:

from sqlalchemy.exc import IntegrityError

try:
    db.session.commit()
except IntegrityError:
    db.session.rollback()
    # error, there already is a user using this bank address or other
    # constraint failed

其中可以从 sqlalchemy.exc 导入 IntegrityError .一旦引发IntegrityError，无论您是否捕获到该错误，您正在使用的会话都会失效.要继续使用会话，您需要发出 db.session.rollback().

where IntegrityError can be imported from sqlalchemy.exc. As soon as the IntegrityError is raised, regardless of whether or not you've caught the error, the session you were working in is invalidated. To continue using the session you'll need to issue a db.session.rollback().

后者更好，因为它不受比赛条件的限制.想象两个用户试图同时注册相同的银行地址:

The latter is better, because it is not subject to race conditions. Imagine two users trying to register the same bank address, at the same time:

• 用户A提交后， User.query.filter().first()返回 None ，因为没有人正在使用该地址.
• 几乎同时，用户B提交了 User.query.filter().first()返回 None ，因为目前还没有人使用该地址
• 用户A的银行地址已成功写入数据库
• 用户B的银行地址无法写入数据库，因为完整性检查失败，因为用户A刚刚记录了该地址.

• User A submits, the User.query.filter().first() returns None because no-one is using the address yet.
• Almost at the same time, user B submits, the User.query.filter().first() returns None because no-one is using the address yet.
• User A's bank address is written to the database, successfully
• User B's bank address can't be written to the database, because the integrity check fails, as User A just recorded that address.

因此只需捕获异常，因为数据库事务保证在测试约束以及添加或更新用户之前，数据库首先锁定表.

So just catch the exception, because database transactions guarantee that the database locks the table first before testing the constraint and adding or updating the user.

您也可以将整个表锁定在Flask中，但是Python与数据库的通讯要慢得多.如果站点繁忙，则不希望数据库更新缓慢，最终将导致许多用户等待锁被清除.您希望将锁定保持在最小范围内，并且越短越好，并且越接近您锁定的实际数据，您就越早可以再次解除锁定.数据库非常擅长于这种锁定，并且(非常自然地)非常接近其数据，因此将锁定保留给数据库，而是依靠异常.

You could lock the whole table in Flask too, but Python talking to a database is a lot slower. If you have a busy site, you don't want database updates to be slow, you'll end up with a lot of users waiting for the lock to clear. You want to keep locking to a minimum, and as short as possible, and the closer to the actual data you lock, the sooner you can release the lock again. Databases are very good at this sort of locking, and are very close to their data (naturally), so leave locking to the database and rely on the exception instead.

这篇关于如何在SQLAlchemy，flask，pyhon中处理唯一数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！