CODEWITHSUNDEEP
exceptionsqlalchemy
Hubro
Hubro

Reputation: 59313

SQLAlchemy error handling - how is it done?

I want to handle a case where there's a primary key or unique key conflict, a.k.a. a duplicate entry. For this I'm catching the IntegrityError, which catches the error just fine. The problem is, I can't seem to find any simple error message or error code to check for. All I'm getting is the IntegrityError.message property which is a string that looks like this:

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

That's not very helpful. Using that I'm going to have to start parsing error messages for their code and message. Calling dir on the exception shows only the following properties:

'args', 'connection_invalidated', 'instance', 'message', 'orig', 'params', 'statement'

args is simply a single-item tuple with the aforementioned string inside it and params is the data I tried to insert. I can't seem to find any way of determining that this actually is a duplicate key error without having to start parsing the error message using regex or something.

Can anyone shed some light on this issue?

Upvotes: 30

Views: 22783

Answers (2)

Angelos
Angelos

Reputation: 1640

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

Upvotes: 4

Hubro
Hubro

Reputation: 59313

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'")

Upvotes: 54

Related Questions