Inconsistent error tuples

[nick-rossi]

trafficstars

I'm using pyodbc to perform tasks to insert data in sql server database. I wanted to catch certain errors and it looks like the error tuples generated do not follow a consistent structure. This makes it harder to handle errors dynamically in code.

For example:

pyodbc.IntegrityError: ('23000', "[23000] [Microsoft][ODBC SQL Server Driver][SQL Server]Violation of UNIQUE KEY constraint 'UC_EventId'. Cannot insert duplicate key in object 'table. (2627) (SQLExecDirectW); [23000] [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated. (3621)")

pyodbc.ProgrammingError: ('The SQL contains 16 parameter markers, but 15 parameters were supplied', 'HY000')

So IntegrityError has structure: (code, message) and ProgrammingError has structure (message, code) .

I wanted to do something in python like:

handle_errors = ["23000", "HY000"]
if ex.args[0] in recoverable_errors:
    do_something()

But this will not work as intended because of pyodbc's inconsistent error tuple structure. For the above example, the ProgrammingError code will be overlooked.

[mkleehammer]

That is a problem. Also, I'd rather be able to get at the SQLSTATE directly via ex.sqlstate.

I wanted to end the 4.x line, but I would consider swapped constructor parameters a serious enough error to put out another release. I'll try to complete it this weekend.

[RobertLD]

I'm having what I think to be related issue if only slightly. I'm handling errors returned from PYODBC dynamically as a way to map to HTTP Status in an API. In SQLSERVER I have a ID check as follows

  begin
        ; throw 51000, 'The Role does not exist.', 1;
  end;

Docs on throw: https://learn.microsoft.com/en-us/sql/t-sql/language-elements/throw-transact-sql?view=sql-server-ver16

and from this pyodbc returns [42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The Role does not exist. (51000) (SQLExecDirectW) in args[1].

I'd greatly appreciate some way to get the error-code (51000) out from the ProgrammingError object returned from pyodbc, seperate from the SQLSTATE itself. Currently I have a hack in place that relies on regex and that is less than ideal. What are your thoughts @mkleehammer?

Here is a link to the code that is related to this behavior I'm reporting

https://github.com/mkleehammer/pyodbc/blob/67162f0c4f0048de51e7cbd4eb511d6485def4f3/src/errors.cpp#L60