Tools, FAQ, Tutorials:
Handle Exceptions with "mysql.connector"
How to handle errors or exceptions with "mysql.connector" module?
✍: FYIcenter.com
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"
2021-09-09, 1257🔥, 0💬
Popular Posts:
How to use urllib.parse.urlencode() function to encode HTTP POST data? My form data has special char...
How to add request URL Template Parameters to my Azure API operation to make it more user friendly? ...
How To Avoid the Undefined Index Error in PHP? If you don't want your PHP page to give out errors as...
How to use the urllib.request.Request object to build more complex HTTP request? The urllib.request....
How to use the "forward-request" Policy Statement to call the backend service for an Azure API servi...