How to handle unique data in SQLAlchemy, flask, pyhon

Question

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:

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()

The error I get:

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

user_100297 · Answer

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.

Catch the exception:
```
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
```
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:

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.

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.

user_49143 · Answer

You should not catch and suppress IntegrityError because it can be raised when other types of constraints fail--such as foreign key constraints.

Nowadays, there is a much better way to handle this error. Both SQLite and PostgreSQL have support for ON CONFLICT DO NOTHING and ON CONFLICT DO UPDATE. Here are their respective SQLAlchemy docs:

Instead of using session.add(), use the insert() function from the SQLAlchemy dialect. It should look roughly like:

# if you are using SQLite
from sqlalchemy.dialects.sqlite import insert

# if you are using PostgreSQL
from sqlalchemy.dialects.postgresql import insert

values = dict() # your values here

stmt = (
    insert(User)
    .values(**values)
    .on_conflict_do_nothing(index_elements=[User.bank_address])
)

db.session.execute(stmt)

How to handle unique data in SQLAlchemy, flask, pyhon

2 Answers