Error handling and SQLSTATE codes for mssql-python

The mssql-python driver defines a standard exception hierarchy, common error handling patterns, and SQLSTATE code mappings for SQL Server and Azure SQL.

Exception hierarchy

The mssql-python driver follows the DB-API 2.0 (PEP 249) exception hierarchy:

Exception (builtins)
├── Warning
└── Error
    ├── InterfaceError
    └── DatabaseError
        ├── DataError
        ├── OperationalError
        ├── IntegrityError
        ├── InternalError
        ├── ProgrammingError
        └── NotSupportedError

ConnectionStringParseError (standalone, not part of hierarchy)

Exception descriptions

Catch the most specific exception that matches your situation. For example, catch IntegrityError for constraint violations on INSERT/UPDATE operations, and ProgrammingError for SQL syntax issues during development. Catch the base Error class only as a fallback.

Basic error handling

Use try-except blocks to handle database errors:

import mssql_python

try:
    conn = mssql_python.connect(connection_string)
    cursor = conn.cursor()
    cursor.execute("INSERT INTO Production.Product (Name) VALUES (%(name)s)", {"name": "Test"})
    conn.commit()
except mssql_python.IntegrityError as e:
    print(f"Constraint violation: {e}")
    conn.rollback()
except mssql_python.ProgrammingError as e:
    print(f"SQL syntax error: {e}")
except mssql_python.OperationalError as e:
    print(f"Connection or operational error: {e}")
except mssql_python.Error as e:
    print(f"Database error: {e}")
finally:
    if 'conn' in locals():
        conn.close()

Access exceptions through the connection

You can catch exceptions through the connection instance:

try:
    cursor.execute("INVALID SQL")
except conn.ProgrammingError as e:
    print(f"Caught via connection: {e}")

Error message structure

mssql-python exception objects expose three attributes that come from the driver's Exception base class:

try:
    cursor.execute("SELECT * FROM no_such_table;")
except mssql_python.ProgrammingError as exc:
    print(exc.driver_error)  # Base table or view not found
    print(exc.ddbc_error)    # [Microsoft][SQL Server]Invalid object name 'no_such_table'.
    print(exc)               # Driver Error: Base table or view not found; DDBC Error: ...

The SQL Server engine error number (such as 208 or 40501) isn't exposed as an attribute and isn't reliably embedded in either string. Classify errors by exception subclass plus driver_error text. For Azure SQL throttling, see Retry logic.

SQLSTATE classification

mssql-python uses the SQLSTATE returned by ODBC to choose both the Python exception subclass and the driver_error text. The full SQLSTATE → exception mapping is in exceptions.py in the driver source. The next section lists the SQLSTATEs that appear most often with SQL Server and Azure SQL.

Connection errors

Connection failures from mssql_python.connect() raise mssql_python.OperationalError, the same as other connectivity failures:

import mssql_python

try:
    conn = mssql_python.connect(
        "Server=unreachable-server.database.windows.net;"
        "Database=<database>;"
        "Authentication=ActiveDirectoryDefault;"
        "Encrypt=yes"
    )
except mssql_python.OperationalError as e:
    print(f"Connection failed: {e.driver_error}")
    # e.driver_error: "Client unable to establish connection"

Connection string errors

Connection string parsing errors raise ConnectionStringParseError:

try:
    conn = mssql_python.connect("Servr=localhost;")  # Typo
except mssql_python.ConnectionStringParseError as e:
    print(f"Invalid connection string: {e}")
    # Output: Unknown keyword 'Servr'

SQLSTATE code reference

SQLSTATE codes are five-character codes that identify error conditions. The first two characters indicate the class, and the last three indicate the subclass. You rarely need to inspect these codes directly. Instead, catch the appropriate Python exception type (listed in the "Exception" column). Use SQLSTATE codes when you need to distinguish between specific error conditions within the same exception type, for example to differentiate a deadlock (40001) from a general connection failure (08S01).

Class 00 - Successful completion

Class 01 - Warning

Class 07 - Dynamic SQL error

Class 08 - Connection exception

Class 21 - Cardinality violation

Class 22 - Data exception

Class 23 - Integrity constraint violation

Class 24 - Invalid cursor state

Class 25 - Invalid transaction state

Class 28 - Invalid authorization specification

Class 34 - Invalid cursor name

Class 3C - Duplicate cursor name

Class 3D - Invalid catalog name

Class 3F - Invalid schema name

Class 40 - Transaction rollback

Class 42 - Syntax error or access rule violation

Class 44 - WITH CHECK OPTION violation

Class HY - CLI-specific condition

Class IM - Driver manager error

Common SQL Server error numbers

Beyond SQLSTATE, SQL Server provides native error numbers in parentheses. These are the errors you're most likely to encounter in application code. Build retry logic around error 1205 (deadlock) and transient connection errors (see Retry logic).

Symptom-to-exception quick reference

Use this table to map common symptoms to the exception type you should catch:

Best practices

• Catch specific exceptions before generic ones. Order from most specific (IntegrityError) to least specific (Error).
• Always handle IntegrityError for data modification operations. Constraint violations are expected in normal operation (for example, a user trying to create a duplicate username).
• Log the full error context for troubleshooting. The exception exposes driver_error (stable, SQLSTATE-derived text) and ddbc_error (server-side message). Log both; classify on driver_error.
• Implement retry logic for transient errors (connection failures, deadlocks). See Retry logic.
• Use rollback() in exception handlers to clean up failed transactions. Without explicit rollback, the connection remains in a failed transaction state.

Related content

• Troubleshooting guide
• Execute queries
• Connection management