SQLAlchemy error handling - how is it done?

As far as I know, the only way is to parse the error string in the exception. I can't find any tuple specifying the error, the column with the violated uniqueness constraint and value separately.

Specifically, one can search the exception message using a substring search operator or a regular expression. For example:

    db.session.add(SupplierUser(supplier_id=supplier_id, username=username, password=password, creator=user))
    try:
        db.session.commit()
    except IntegrityError as err:
        db.session.rollback()
        if "UNIQUE constraint failed: user.username" in str(err):
            return False, "error, username already exists (%s)" % username
        elif "FOREIGN KEY constraint failed" in str(err):
            return False, "supplier does not exist"
        else:
            return False, "unknown error adding user"

However, these strings can be quite long because the SQL statement is included, for example:

(sqlite3.IntegrityError) UNIQUE constraint failed: user.username [SQL: 'INSERT INTO user (username, password_hash, created_time, creator_id, role, is_active, supplier_id) VALUES (?, ?, ?, ?, ?, ?, ?)'] [parameters: ('bob', ...

Thus, you will minimise the error handling latencies parsing exceptions if you search through the database error message, without the added information from sqlalchemy. This can be done by examining err.args, which should be smaller:

'(sqlite3.IntegrityError) UNIQUE constraint failed: supplier_user.username',)

The updated example:

    db.session.add(SupplierUser(supplier_id=supplier_id, username=username, password=password, creator=user))
    try:
        db.session.commit()
    except IntegrityError as err:
        db.session.rollback()
        err_msg = err.args[0]
        if "UNIQUE constraint failed: supplier_user.username" in err_msg:
            return False, "error, supplier username already exists (%s)" % username
        elif "FOREIGN KEY constraint failed" in err_msg:
            return False, "supplier does not exist"
        else:
            return False, "unknown error adding user"

Note the error syntax I used here is for sqlite3. Parsing an mysql exception error message like:

(1062, "Duplicate entry 'usr544' for key 'username'")

Can be done with an appropriate regular expression. Note that it looks like a tuple, but it is actually a string (sqlalchemy version 1.1.3 and mysql 5.5).

For example:

    except IntegrityError as err:
        db.session.rollback()
        if re.match("(.*)Duplicate entry(.*)for key 'username'(.*)", err.args[0]):
    .... etc .... 

I figured this out while writing the question by reading the documentation more carefully. I'm still going to post this though since it might be of help to someone.

In the documentation of the SQLAlchemy DBAPIError, from which the IntegrityError is subclassed, it explains that the exception is merely a wrapper for the underlying database API error and that the original error is saved as orig in the exception. Sure enough, calling e.orig.args I get a nicely organized tuple:

(1062, "Duplicate entry 'foobar' for key 'name'")