How to handle errors or exceptions with "mysql.connector" module?

While you are using "mysql.connector" module, an error or exception could occur on any MySQL database related statements. There are 3 basic ways to handle those errors or exceptions.

1. Default behavior - Whenever "mysql.connector" module encounters an error, it will raise an exception to the caller. If the caller is catching the exception, exception will be terminated with the exception dumped to the console.

2. Catch all exceptions with a single "try ... except" statement - You can all MySQL database logic in a single "try ... except" statement block, and catch the base exception of mysql.connector.Error or individual exceptions.

3. Catch exceptions with each MySQL database statement - You can write each MySQL database statement in a "try ... except" statement block, and decide what to do depending on the nature of the statement.

Below is an example the default behavior. If you run insert_data.py second time, the INSERT statement will fail, because the table already exists.

fyicenter> python3 insert_data.py 

Traceback (most recent call last):
  File "insert_data.py", line 11, in <module>
    con.cmd_query(sql)
  File ".../mysql/connector/connection.py", line 695, in cmd_query
    result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
  File ".../mysql/connector/connection.py", line 582, in _handle_result
    raise errors.get_exception(packet)
mysql.connector.errors.ProgrammingError: 1050 (42S01): 
  Table 'fyi_links' already exists

Here is example catching all exceptions with a single "try ... except" statement, insert_data_2.py:

fyicenter> more insert_data_2.py
# insert_data_2.py
# Copyright (c) FYIcenter.com 

from mysql.connector import connect, Error

try:
    con = connect(host="127.0.0.1", port=3306, \
        user="guest", password="retneciyf")
    con.cmd_init_db("test")

    sql = "CREATE TABLE fyi_links (id INTEGER PRIMARY KEY, \
        url VARCHAR(80) NOT NULL, title VARCHAR(1024))"
    con.cmd_query(sql)

    sql = "INSERT INTO fyi_links (id, url, title) \
        VALUES (101, 'dev.fyicenter.com', 'Developer FYI')"
    con.cmd_query(sql)

    con.commit()
    con.close()

except Error as err:
    print(err.errno)
    print(err)
    print("The script failed...")

fyicenter> python3 insert_data_2.py
1050
1050 (42S01): Table 'fyi_links' already exists
The script failed...

Here is example catching exception with each MySQL statement and make smart decisions, insert_data_3.py:

# insert_data_3.py
# Copyright (c) FYIcenter.com 

from mysql.connector import connect, Error

try:
    con = connect(host="127.0.0.1", port=3306, \
        user="guest", password="retneciyf")
    con.cmd_init_db("test")
except Error as err:
    print(err)
    print("Stopped - No connection...")
    exit()

try:
    sql = "CREATE TABLE fyi_links (id INTEGER PRIMARY KEY, \
        url VARCHAR(80) NOT NULL, title VARCHAR(1024))"
    con.cmd_query(sql)
except Error as err:
    print(err)
    if err.errno == 1050:
        print("Table exists, continue to run...")
    else:
        print("Unknown exception, stop here")
        exit()

try:
    sql = "INSERT INTO fyi_links (id, url, title) \
        VALUES (101, 'dev.fyicenter.com', 'Developer FYI')"
    con.cmd_query(sql)
    con.commit()
except Error as err:
    print(err)
    print("Failed to insert data...")

try:
    con.close()
except Error as err:
    print(err)
    print("Failed to close the connection...")

fyicenter$ python3 insert_data_3.py 
1050 (42S01): Table 'fyi_links' already exists
Table exists, continue to run...
1062 (23000): Duplicate entry '101' for key 'PRIMARY'
Failed to insert data...

⇒ MySQLConnection.cursor() and MySQLCursor.execute()

⇐ Change Data with "mysql.connector"

⇑ Python Modules for MySQL Database

⇑⇑ Python Tutorials