Different database backend raise different exceptions on not null constraint violations

[Former user]

I noticed that different database backends may cause different exceptions to be raised when violating a not null constraint.

Sqlite, for example, seems to raise IntegrityError, while MySQL with mysql-python raises OperationalError and postgresql with pg8000 raises ProgrammingError.

This is a bit of a problem when developing software that is supposed to be backend-agnostic. You can still catch the exception by catching the parent class DatabaseError, but this is not helpful when you are trying to figure out what exactly went wrong.

[Mike Bayer]

unfortunately this is a situation of the DBAPI specification and is not within SQLAlchemy's power to fix - only if SQLAlchemy were to add massive translation layers for every exception possible raised by every database backend, which would be very error prone and make things a lot worse. We propagate DBAPI exceptions as is (with additional information about context) in order to maximize transparency about what went wrong.

[Mike Bayer]

as it is, the backends know how to detect exactly one type of error scenario, that of an exception that indicates the database connection is no longer connected. just that alone is a feature we are constantly getting new tickets for as new database conditions are discovered. Look at the awkwardness of the MySQL, Postgresql and Oracle backends for example:

https://github.com/zzzeek/sqlalchemy/blob/master/lib/sqlalchemy/dialects/postgresql/psycopg2.py#L460

https://github.com/zzzeek/sqlalchemy/blob/master/lib/sqlalchemy/dialects/mysql/base.py#L2132

https://github.com/zzzeek/sqlalchemy/blob/master/lib/sqlalchemy/dialects/oracle/cx_oracle.py#L813

note that two of those routines are not only database specific, but DBAPI specific - messages are sent out from various C drivers which aren't the same as those sent out from other interpretation layers. Multiply that by the number of things that can go wrong and I'm hoping the infeasibility of the task is apparent.