How to handle unique data in SQLAlchemy, flask, pyhon

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 IntegrityErrortry:    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.

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:

• sqlalchemy.dialects.sqlite.Insert.on_conflict_do_nothing
• sqlalchemy.dialects.postgresql.Insert.on_conflict_do_nothing

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

# if you are using SQLitefrom sqlalchemy.dialects.sqlite import insert# if you are using PostgreSQLfrom sqlalchemy.dialects.postgresql import insertvalues = dict() # your values herestmt = (    insert(User)    .values(**values)    .on_conflict_do_nothing(index_elements=[User.bank_address]))db.session.execute(stmt)