pyodbc based dialect exception/error mapping does not seem to work properly

[Nicoretti]

Steps To Reproduce:

Add the tests below to test/test_suit.py and execute the integration tests or just the test itself.

from sqlalchemy.testing.suite import ExceptionTest as _ExceptionTest
from sqlalchemy import text
from sqlalchemy import create_engine

class ExceptionTest(_ExceptionTest):

    def test_integrity_error(self):
        e = create_engine(config.db.url, connect_args={'autocommit': True})
        with e.connect() as conn:

            trans = conn.begin()
            conn.execute(
                self.tables.manual_pk.insert(), {"id": 1, "data": "d1"}
            )

            assert_raises(
                exc.IntegrityError,
                conn.execute,
                self.tables.manual_pk.insert(),
                {"id": 1, "data": "d1"},
            )

            trans.rollback()

    def test_integrity_error_raw_sql(self):
        with config.db.connect() as conn:
            statement = text("INSERT INTO MANUAL_PK VALUES (1, 'd1')")
            conn.execute(statement)

            assert_raises(
                exc.IntegrityError,
                conn.execute,
                statement
            )

Expected Behavior

:heavy_check_mark: Test passes because an sqlalchemy.exec.IntegrityError is risen.

Actual Behavior

:boom: Test fails because an sqlalchemy.exc.ProgrammingError/sqlalchemy.exc.DBAPIError is risen.

# test_integrity_error
sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42X91', '[42X91] [EXASOL][EXASolution driver]Exception when finishing statement with handle 4 : constraint violation - primary key (SYS_1325145854795323310309632 on table MANUAL_PK) (Session: 1728273060234919936) (-6854869) (SQLExecDirectW)')
[SQL: INSERT INTO manual_pk (id, "data") VALUES (?, ?)]
[parameters: (1, 'd1')]

# test_integrity_error_raw_sql
sqlalchemy.exc.DBAPIError: (pyodbc.Error) ('27002', '[27002] [EXASOL][EXASolution driver]constraint violation - primary key (SYS_13251458158488892136007680 on table MANUAL_PK) (Session: 1727921460359004160) (-3685826) (SQLExecDirectW)')
[SQL: INSERT INTO MANUAL_PK VALUES (1, 'd1')]

Related Issues

• exasol/sqlalchemy-exasol#42

[Nicoretti]

:spiral_notepad: MySQL and MSSQL ODBC drivers report a different error code on an odbc level for the same test scenario.

• MySQL 23000
• MSSQL 23000

[Nicoretti]

This issue potentially can be addressed by configuring the exasol odbc driver with appropriate mappings see odbc-docs (SQLSTATEMAPPINGACTIVE and SQLSTATEMAPPINGS)