error capturing from MySQL query by using try except

In case of error in Query, MySQL returns error messages which can be shown. This error message helps in rectifying the problem. Here we have used a wrong table name while collecting the records. We used exception handling to capture and display the error message.

Add your userid, password and database name for database connection

import mysql.connector
try:
    my_connect = mysql.connector.connect(
      host="localhost",
      user="",
      passwd="",
      database="my_tutorial"
    )
except mysql.connector.Error as my_error:
    print(my_error.msg)  #  Error message
    print(my_error)      #  With error number 
####### end of connection ####

my_cursor = my_connect.cursor()
try:
    my_cursor.execute("SELECT * FROM  student1 Where class='Five'")

    print("Rows returned = ",my_cursor.rowcount)
    my_result = my_cursor.fetchone()
    print(my_result)
except mysql.connector.Error as my_error:
    print(my_error) # Output error details. 
my_connect.close()

Output is here

1146 (42S02): Table 'my_tutorial.student1' doesn't exist

Example 1: Handling Specific Error Codes

import mysql.connector
try:
    my_connect = mysql.connector.connect(host="localhost", user="root", passwd="", database="my_tutorial")
    my_cursor = my_connect.cursor()
    my_cursor.execute("SELECT * FROM unknown_table")
except mysql.connector.Error as err:
    if err.errno == 1146:  # Table doesn't exist
        print("Error: Table not found.")
    else:
        print(err)

Output:

Error: Table not found.

Example 2: Handling Connection Errors

try:
    my_connect = mysql.connector.connect(host="localhost", user="root", passwd="wrong_password")
except mysql.connector.Error as err:
    if err.errno == 1045:  # Access denied
        print("Error: Access denied due to invalid credentials.")
    else:
        print(err)

Output:

Error: Access denied due to invalid credentials.

Example 3: Handling Database Not Found Error

try:
    my_connect = mysql.connector.connect(host="localhost", user="root", passwd="", database="nonexistent_db")
except mysql.connector.Error as err:
    if err.errno == 1049:  # Unknown database
        print("Error: Database not found.")
    else:
        print(err)

Output:

Error: Database not found.

Example 4: Handling Syntax Errors in SQL Queries

try:
    my_cursor.execute("SELCT * FROM students")  # Typo in SELECT
except mysql.connector.Error as err:
    if err.errno == 1064:  # SQL syntax error
        print("Error: SQL syntax error.")
    else:
        print(err)

Output:

Error: SQL syntax error.

Example 5: Handling Duplicate Entry Error

try:
    my_cursor.execute("INSERT INTO students (id, name) VALUES (1, 'John')")
except mysql.connector.Error as err:
    if err.errno == 1062:  # Duplicate entry
        print("Error: Duplicate entry for primary key.")
    else:
        print(err)

Output:

Error: Duplicate entry for primary key.

Example 6: Handling Foreign Key Constraint Failure

try:
    my_cursor.execute("INSERT INTO orders (customer_id, order_total) VALUES (999, 250)")
except mysql.connector.Error as err:
    if err.errno == 1452:  # Foreign key constraint fails
        print("Error: Foreign key constraint violation.")
    else:
        print(err)

Output:

Error: Foreign key constraint violation.

Example 7: Handling Connection Timeout

try:
    my_connect = mysql.connector.connect(host="localhost", user="root", passwd="", database="my_tutorial", connect_timeout=1)
except mysql.connector.Error as err:
    if err.errno == 2003:  # Connection timeout error
        print("Error: Connection timeout.")
    else:
        print(err)

Output:

Error: Connection timeout.

Example 8: Handling Invalid Hostname

try:
    my_connect = mysql.connector.connect(host="invalid_host", user="root", passwd="")
except mysql.connector.Error as err:
    if err.errno == 2005:  # Unknown MySQL server host
        print("Error: Unknown MySQL server host.")
    else:
        print(err)

Output:

Error: Unknown MySQL server host.

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author Watch on YouTube

Connect: