Exception Handling in Connector/Python

Error happens all the time when programming, so its better to equip yourself how to deal with them.

Errors and Warnings

There are two levels of error message severity in MySQL.

  1. Error
  2. Warning

Error

An Error indicates a problem with query or command which prevented it from being executed.

Warning

Warning tells you some thing unexpected has happened that could cause problem down the line, but it is not severe enough to stop the statement from being executed. We can display warnings using SHOW WARNINGS; command. For example:

Error Codes

Errors and warnings in MySQL contains three pieces of information:

  1. A unique MySQL specific error code (1146) that is not portable to other databases.
  2. A 5 character code (42S02) called SQLSTATE which is used to indicate success or failure of the operation. The SQLSTATE is portable across other databases. It consists of two parts: the first two character represents SQL error class and the next three represents subclass. Each class can belong to one of the following four categories.
    1. Success (class 00)
    2. Warning (class 01)
    3. No Data (class 02)
    4. Exception (all the others 07-HZ)
  3. A textual description of the error.

So why two error codes?

This is because SQLSTATE represents a group of errors. As a result, we can only use it to handle generic errors. If you want to handle some specific error use MySQL-specific error code.

There are hundreds of MySQL-specific error codes. Fortunately, you don’t need to memorize them as the mysql.connector.errorcode module contains all the MySQL-specific error codes.

Exceptions Classes

All the exception classes for dealing with error and warnings are defined in mysql.connector.errors module.

The mysql.connector.errors.Error is the base class for all the other exceptions. We can use it to catch any kind of exception.

Here is an example:

Expected Output:

When an exception is thrown you have access to error code, SQLSTATE and error message in the form of errno, sqlstate and msg attributes of the exception object.

The mysql.connector.errors.Error base class is further subclassed into the following three class:

  1. DatabaseError
  2. InterfaceError
  3.  PoolError

Let’s discuss them one by one.

1. DatabaseError: This exception is raised for errors related to the database. It can catch a variety of errors like problem in data processing, error in SQL syntax, MySQL internal problems etc. If a connection is made and a problem arises then DatabaseError will catch it. There are 6 types of DatabaseError:

  1. DataError
  2. InternalError
  3. IntegrityError
  4. OperationalError
  5. NotSupportedError
  6.  ProgrammingError

  1. DataError: This error indicates a problem with the data processing, like division by zero, numeric value out of range, invalid type etc.
  2. InternalError: This exception is raised when the database encounters an internal error. For e.g invalid cursor, transaction out of sync etc.
  3. IntegrityError: This exception is raised when the foreign key constraint fails.
  4. OperationalError: This exception is raised for things that are not in control of the programmer. For e.g unexpected disconnect, error in memory allocation etc, transaction failure, selected database not exists.
  5. NotSupportedError: This exception is raised when you invoke method or api that is not supported. For example, calling rollback() on a connection that doesn’t support the transaction.
  6. ProgrammingError: This exception is raised of programming errors. For e.g table not found or already exists, error in MySQL syntax, wrong number of parameters specified, wrong connection credentials etc.

2. InterfaceError: This exception is raised for errors related to the interface (in our case interface is MySQL Connector/Python) rather than the database itself.

3. PoolError: It is raised for errors related to connection pooling. We will learn about the connection pooling in the upcoming chapter.

The following figure shows the hierarchy of the exception classes:

All the exception classes are mapped to one or more SQLSTATE class, you can print this mapping by typing the following command.

Let’s look at some examples now.

Example 1: Handling the Generic Error

Remember that ProgrammingError can catch a variety of exceptions ranging from syntax error to table not found. If you want to catch some specific error use the errorcode module.

Example 2: Handling a specific error

Example 3: Handling multiple errors in the same way

Handling Warnings

By default, MySQL Connector/Python neither fetch warnings nor raise an exception on warnings. But, we can change that using the following arguments of the connect() function.

Argument Description
get_warnings If set to True warnings are fetched automatically after each query without having to manually execute SHOW WARNINGS query. Its default value is False.
raise_on_warnings If set to True, an exception is raised on warnings. It defaults to False. Setting raise_on_warnings=True, also sets get_warnings=True

The above two arguments are also available as properties of the connection object, which can be used to set and retrieve the current setting.

Once you have set get_warnings=True (or raise_on_warnings=True), to retrieve the actual warning use the fetchwarnings() method of the cursor object. The fetchwarnings() method returns a list of tuples containing message-level, error code and the message itself generated by the previously executed query.

The following example demonstrates how to fetch warnings produced by the queries:

Expected Output:

Note that, if the query returns result set (like the SELECT statement) then it is necessary for you to fetch all the rows before calling the fetchwarnings() method, otherwise, the fetchwarnings() method would return None.

Here is an example which shows how to raise exceptions on warnings.

Expected Output:

Note that setting raise_on_warnings=True implicitly sets get_warnings=True.

You should now have a pretty good idea of how to handle errors in the next few pages we will see some practical examples of SELECT, INSERT, UPDATE and DELETE statements:

Leave a Comment