How to catch specific exceptions on sqlalchemy?

You can reraise the original exception from the except block and catch whatever specific type you are interested in:

import sqlalchemy
import psycopg2
from psycopg2 import errors

try:
    raise sqlalchemy.exc.IntegrityError("INSERT INTO table (col1) VALUES (?)", (1,), errors.IntegrityConstraintViolation)
except sqlalchemy.exc.IntegrityError as sqla_error:
    try:
        raise sqla_error.orig
    except (errors.UniqueViolation, errors.ForeignKeyViolation):
        pass

This will raise all subclasses of psycopg2.IntegrityError other than psycopg2.error.UniqueViolation and psycopg2.errors.ForeignKeyViolation.

As stated in SuperShoot's answer, this will result in a nested exception. You can suppress the exception contex via:

raise sqla_error.orig from None

However, that might take away from the expressiveness of the traceback.

If you want to fall back to the SQLAlchemy IntegrityError if .orig is not of a type you are interested in, you can raise it again by adding this to the above:

    except psycopg2.IntegrityError:
        raise sqla_error from None

This is just an idea - I haven't tested it at all - but perhaps a context manager can do what you want. For example, if you want a particular query to raise the original exception, then use with raise_orig(): query().

from contextlib import contextmanager
from sqlalchemy.exc import StatementError

@contextmanager
def raise_orig():
    try:
        yield
    except StatementError as stmt_exc:
        raise stmt_exc.orig


def query():
    raise StatementError(
        message="Message from SQLAlchemy",
        statement="SELECT 1",
        params=(),
        orig=RuntimeError("The original exception instance"),
    )


if __name__ == "__main__":
    with raise_orig():
        query()

here is the traceback that this raises:

Traceback (most recent call last):
  File "raise_orig.py", line 7, in raise_orig
    yield
  File "raise_orig.py", line 23, in <module>
    query()
  File "raise_orig.py", line 17, in query
    orig=RuntimeError("The original exception instance"),
sqlalchemy.exc.StatementError: Message from SQLAlchemy
[SQL: SELECT 1]

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "raise_orig.py", line 23, in <module>
    query()
  File "C:\Users\peter_000\AppData\Local\Programs\Python\Python36\Lib\contextlib.py", line 99, in __exit__
    self.gen.throw(type, value, traceback)
  File "raise_orig.py", line 9, in raise_orig
    raise stmt_exc.orig
RuntimeError: The original exception instance

I chose to test for StatementError as that is the most primary exception type that has the orig attribute.

Not perfect as the tracebacks will be even more complicated than they already are, but should at least allow you to catch the specific type.

You could also just use the context manager pattern to abstract away the exception type checking and handling, so you don't need to repeat it everywhere (then it only needs to look ugly in one place!):

@contextmanager
def handle_orig():
    try:
        yield
    except StatementError as stmt_exc:
        if isinstance(stmt_exc.orig, SomeError):
            do_this()
        elif isinstance(stmt_exc.orig, SomeOtherError):
            do_that()
        ...
        raise  # or return True to suppress exc.

with handle_orig():
    query()