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 = ?']

Answer 1

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.