How to catch error 1062 "duplicate entry" independent from used database/engine?

Question

In a project I started out with MySQL as database. Instead of checking first, I just do an insert and if I get an IntegrityError exception with code 1062, I know that there is a duplicate entry and warn the user, to do this and that.

that looks basically like this:

try:
    # add duplicate, nothing bad happens yet, is only in sqla session
    db.session.add(User(email='already_used_email@address_that_has_to_be_unique.com'))
    # commit, now the IntegrityError is raised, when sqla inserts
    db.session.commit()
except IntegrityError as e:
    db.session.rollback()
    # this is what i do with mysql, check the exception for code 1062
    # how can i replace this with something db independent?
    code, msg = e.orig
    if code == 1062:
        # send warning
        pass

Now, for one this makes it already impossible to test with eg. in-memory sqlite. Not nice, but I could live with that.

Second however, I might (have to/want to for other out of scope of this question issues) switch to Postgres. Of course I could just change the code to (also) check for Postgres error codes, but I was hoping there is a way to have SQLALchemy tell me, that a duplicate happened independent from the database. A database dialect abstraction...?

Answer 1

What you want to search for is something called "SQLSTATE" - a set of standard error codes that cover most common RDBMS error states. They don't necessarily provide enough detail for all purposes though, and I don't know if sqlite supports them.

• http://www.postgresql.org/docs/9.1/static/errcodes-appendix.html
• http://dev.mysql.com/doc/refman/5.0/en/error-handling.html