The error that you have posted in your question isn't the error that has been raised. The full error message is:
sqlalchemy.exc.IntegrityError: (psycopg2.errors.UniqueViolation) duplicate key value violates unique constraint "model_name_key"
The key part being the SQLAlchemy error which you've chosen to omit for some reason. SQLAlchemy catches the original error, wraps it in it's own error and raises that.
but in Python, this is much more obfuscated... Why does Python hide this?
This isn't obfuscation, nothing is hidden, the behavior is documented, specific to the frameworks that you are using and is not enforced by the Python language. SQLAlchemy is an abstraction library and if it were to raise exceptions specific to the underlying dpapi adapter, it would significantly reduce the portability of code written within it.
From the docs:
SQLAlchemy does not generate these exceptions directly. Instead, they
are intercepted from the database driver and wrapped by the
SQLAlchemy-provided exception DBAPIError, however the messaging within
the exception is generated by the driver, not SQLAlchemy.
Exceptions raised by the dbapi layer are wrapped in a subclass of the sqlalchemy.exc.DBAPIError, where it is noted:
The wrapped exception object is available in the orig attribute.
So it's very straightforward to catch the SQLAlchemy exception and inspect the original exception, which is an instance of psycopg2.errors.UniqueViolation, as you'd expect. However, unless your error handling is very specific to the type raised by the dbapi layer, I'd suggest that inspecting the underlying type might be unnecessary as the SQLAlchemy exception that is raised will provide enough runtime information to do what you have to do.
Here is an example script that raises a sqlalchemy.exc.IntegrityError, catches it, inspects the underlying exception through the orig attribute and raises an alternate, locally-defined exception.
from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.exc import IntegrityError
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from psycopg2.errors import UniqueViolation
engine = create_engine("postgresql+psycopg2://some-user:mysecretpassword@localhost:5432/some-user")
Base = declarative_base()
Session = sessionmaker(bind=engine)
class BadRequest(Exception):
pass
class Model(Base):
__tablename__ = "model"
id = Column(Integer, primary_key=True)
name = Column(String, unique=True)
if __name__ == "__main__":
Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)
s = Session()
s.add(Model(name="a"))
s.commit()
s.add(Model(name="a"))
try:
s.commit()
except IntegrityError as e:
assert isinstance(e.orig, UniqueViolation) # proves the original exception
raise BadRequest from e
And that raises:
sqlalchemy.exc.IntegrityError: (psycopg2.errors.UniqueViolation) duplicate key value violates unique constraint "model_name_key"
DETAIL: Key (name)=(a) already exists.
[SQL: INSERT INTO model (name) VALUES (%(name)s) RETURNING model.id]
[parameters: {'name': 'a'}]
(Background on this error at: http://sqlalche.me/e/gkpj)
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File ".\main.py", line 36, in <module>
raise BadRequest from e
__main__.BadRequest
