What are the exceptions while connecting to SQL Server in Python?

When you are developing an application that uses some kind of database interaction, it is really important that all of the exceptions, while performing the database operations, are properly handled.

In this shot, we are going to learn about different exceptions that can occur while connecting to the SQL Server using pyodbc in Python.

Data error

This type of exception occurs when the data that you are processing is throwing an error (like division by zero, numeric value out of range, etc.) This exception should be a subclass of the DatabaseError class as you can handle the exception with pyodbc.

Take a look at the below code:

import pyodbc
try:
  
  # Perform database operations
except pyodbc.DataError as err:
  # Perform cleanup steps

Explanation

  • In line 1, we import the required package.

  • In line 3, we use a try-except block to write all our database operations inside the try block.

  • In line 7, we handle the DataError exception.

Operational error

This exception occurs when there is an unexpected error that is not caused by programmer (e.g., if the connection gets disconnected suddenly, there is an issue in memory allocation, or a particular transaction was not processed successfully.) This is also a subclass of the DatabaseError class, and can be handled with pyodbc.

Take a look at the code below:

import pyodbc
try:
  
  # Perform database operations
except pyodbc.OperationalError as err:
  # Perform cleanup steps

Explanation

  • The code is almost the same as the previous with just one difference while catching the exception.

Integrity error

This exception occurs when you are trying to insert some data that violates the integrity of the database (e.g., inserting the same value as the primary key twice, violating any constraints, etc.) This is also a subclass of the DatabaseError class, and you can handle the exception with pyodbc.

Take a look at the following code:

import pyodbc
try:
  
  # Perform database operations
except pyodbc.IntegrityError as err:
  # Perform cleanup steps

Explanation

  • The code is almost the same as above with just one difference while catching the exception.

Internal Error

This exception occurs when the database has some internal error (e.g., the cursor is not valid, a close cursor is used for performing the database operation, etc.) This is also a subclass of the DatabaseError class, and you can handle the exception with pyodbc.

Take a look at the code:

import pyodbc
try:
  
  # Perform database operations
except pyodbc.InternalError as err:
  # Perform cleanup steps

Explanation

  • The code is almost the same as the one above with just one difference while catching the exception.

Programming error

This exception occurs when there is a problem from the programming end, like the SQL statement is incorrect, the table is not found, etc. It is also a subclass of the DatabaseError class that handle with pyodbc.

Take a look at the code:

import pyodbc
try:
  
  # Perform database operations
except pyodbc.ProgrammingError as err:
  # Perform cleanup steps

Explanation

  • The code is almost the same as the one above, with just one difference while catching the exception.

Not supported error

This exception occurs when the database API that you are using does not support some operations (e.g., rollback() may not be supported by the database). This is also a subclass of the DatabaseError class; you can handle it with pyodbc.

Take a look at the code:

import pyodbc
try:
  
  # Perform database operations
except pyodbc.NotSupportedError as err:
  # Perform cleanup steps

Explanation

  • The code above is almost the same as the one above, with just one difference while catching the exception.

With the ways described, we can easily handle exceptions while performing database operations in our application to avoid any unexpected crashes.

Free Resources

License: Creative Commons-Attribution-ShareAlike 4.0 (CC-BY-SA 4.0)