OverIQ.com

  1. Home
  2. MySQL Connector/Python Tutorial
  3. Exception Handling in Connector/Python

Exception Handling in Connector/Python

Last updated on July 27, 2020

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. 

1
2
mysql> select * from earth;
ERROR 1146 (42S02): Table 'world.earth' doesn't exist

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:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
mysql> 
mysql> CREATE database if not exists world;
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+------------------------------------------------+
| Level | Code | Message                                        |
+-------+------+------------------------------------------------+
| Note  | 1007 | Can't create database 'world'; database exists |
+-------+------+------------------------------------------------+
1 row in set (0.00 sec)

mysql> 
mysql> 
mysql> SELECT 1/0;
+------+
| 1/0  |
+------+
| NULL |
+------+
1 row in set, 1 warning (0.12 sec)

mysql> show warnings;
+---------+------+---------------+
| Level   | Code | Message       |
+---------+------+---------------+
| Warning | 1365 | Division by 0 |
+---------+------+---------------+
1 row in set (0.00 sec)
mysql>

Error Codes #

Errors and warnings in MySQL contains three pieces of information:

1
2
mysql> select * from earth;
ERROR 1146 (42S02): Table 'world.earth' doesn't exist

  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.

1
2
3
4
5
>>> 
>>> from mysql.connector import errorcode
>>> errorcode.ER_BAD_TABLE_ERROR
1051
>>>

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:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
import mysql.connector

db = mysql.connector.connect(option_files='my.conf', use_pure=True)

cursor = db.cursor()

sql = """
CREATE TABLE `city` (
  `ID` int(11) AUTO_INCREMENT PRIMARY KEY,
  `Name` char(35) NOT NULL DEFAULT '',
  `CountryCode` char(3) NOT NULL DEFAULT ''  
)
"""

try:
    cursor.execute(sql)
    db.commit()

except mysql.connector.Error as err:
    print(err)
    print("Error Code:", err.errno)
    print("SQLSTATE", err.sqlstate)
    print("Message", err.msg)

cursor.close()
db.close()

Expected Output:

1
2
3
4
1050 (42S01): Table 'city' already exists
Error Code: 1050
SQLSTATE 42S01
Message Table 'city' already exists

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:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
StandardError
|__ Error
    |__ InterfaceError
    |__ PoolError
    |__ DatabaseError
        |__ DataError
        |__ InternalError 
        |__ IntegrityError
        |__ OperationalError        
        |__ NotSupportedError
        |__ ProgrammingError

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
>>>
>>> from mysql.connector import errors
>>> from pprint import pprint
>>> 
>>> pprint(errors._SQLSTATE_CLASS_EXCEPTION)
{'02': <class 'mysql.connector.errors.DataError'>,
 '07': <class 'mysql.connector.errors.DatabaseError'>,
 '08': <class 'mysql.connector.errors.OperationalError'>,
 '0A': <class 'mysql.connector.errors.NotSupportedError'>,
 '0K': <class 'mysql.connector.errors.OperationalError'>,
...
 'HY': <class 'mysql.connector.errors.DatabaseError'>,
 'HZ': <class 'mysql.connector.errors.OperationalError'>,
 'XA': <class 'mysql.connector.errors.IntegrityError'>}
>>> 
>>>

Let's look at some examples now.

Example 1: Handling the Generic Error

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
import mysql.connector

db = mysql.connector.connect(option_files='my.conf', use_pure=True)

cursor = db.cursor()

sql = """
select * from town limit 5
"""

try:
    cursor.execute(sql)

    for row in cursor:
        print(row)


    cursor.close()
    db.close()

except mysql.connector.ProgrammingError as err:
    print(err.errno)
    print(err.sqlstate)
    print(err.msg)

except mysql.connector.Error as err:
    print(err)

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
import mysql.connector
from mysql.connector import errorcode

db = mysql.connector.connect(option_files='my.conf', use_pure=True)

cursor = db.cursor()

sql = """
select * from town limit 5
"""

try:
    cursor.execute(sql)

    for row in cursor:
        print(row)

    db.close()

except mysql.connector.ProgrammingError as err:
    if errorcode.ER_NO_SUCH_TABLE == err.errno:
        print("No table exists")
    else:
        print("Table exists")
        print(err)

except mysql.connector.Error as err:
    print("Some other error")
    print(err)

Example 3: Handling multiple errors in the same way

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
import mysql.connector

db = mysql.connector.connect(option_files='my.conf', use_pure=True)

cursor = db.cursor()

sql = """
insert into city(Name, CountryCode, District, Population)
VALUES ('Kabul', 'AFGANISTAN', 'Kabol', 1780000)
"""

try:
    cursor.execute(sql)

    db.commit()
    db.close()

except (mysql.connector.IntegrityError, mysql.connector.DataError) as err:
    print("DataError or IntegrityError")
    print(err)

except mysql.connector.ProgrammingError as err:
    print("Programming Error")
    print(err)

except mysql.connector.Error as err:
    print(err)

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.

1
2
3
4
5
6
7
import mysql.connector
from mysql.connector import errors

db = mysql.connector.connect(option_files='my.conf')

db.get_warnings = True  # this is not required if raise_on_warnings=True
db.raise_on_warnings = True

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:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
import mysql.connector

db = mysql.connector.connect(option_files='my.conf',
                             get_warnings=True)

# db.get_warnings = True  # we could have set get_warnings like this too

cursor = db.cursor()

cursor.execute("CREATE database if not exists world;")
print(cursor.fetchwarnings())

cursor.execute("select 1/0;")
cursor.fetchall() # fetch all the rows from the result set
print(cursor.fetchwarnings())

Expected Output:

1
2
[('Note', 1007, "Can't create database 'world'; database exists")]
[('Warning', 1365, 'Division by 0')]

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.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
import mysql.connector
from mysql.connector import errors

db = mysql.connector.connect(option_files='my.conf',
                             raise_on_warnings=True)

# db.raise_on_warnings = True  # we could have set raise_on_warnings like this

cursor = db.cursor()

try:

    cursor.execute("CREATE database if not exists world;")
    print(cursor.fetchwarnings())

    cursor.execute("select 1/0;")
    print(cursor.fetchall()) # fetch all the rows from the result set
    print(cursor.fetchwarnings())

except errors.DatabaseError as e:
    print(e)

except:
    print('some error occurred')

Expected Output:

1007: Can't create database 'world'; database exists

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 DELTETE statements:

MySQL Connector/Python Tutorial

Recent Posts