22. Catching Exceptions
All exceptions raised by python-oracledb are inherited from oracledb.Error . See Oracledb Exceptions and Oracledb._Error Objects for information about attributes.
See Error Handling in Thin and Thick Modes for differences between the python-oracledb Thin and Thick modes.
Applications can catch exceptions as needed. For example, when trying to add a customer that already exists in the database, the following could be used to catch the exception:
try: cursor.execute("insert into customer values (101, 'Customer A')") except oracledb.IntegrityError: print("Customer ID already exists") else: print("Customer added")
If information about the exception needs to be processed instead, the following code can be used:
try: cursor.execute("insert into customer values (101, 'Customer A')") except oracledb.IntegrityError as e: error_obj, = e.args print("Customer ID already exists") print("Error Code:", error_obj.code) print("Error Full Code:", error_obj.full_code) print("Error Message:", error_obj.message) else: print("Customer added")
This will print output like:
Customer ID already exists Error Code: 1 Error Full Code: ORA-00001 Error Message: ORA-00001: unique constraint (CJ.PK) violated
22.1. Error Handling in Thin and Thick Modes
The Thin and Thick modes of python-oracledb return some errors differently.
The python-oracledb Thin mode code generates error messages with the prefix “DPY”.
- The Oracle Call Interface (OCI) libraries generate error messages with the prefix “ORA”.
- The ODPI-C code layer generates error messages with the prefix “DPI”.
- The python-oracledb Thick mode code generates error messages with the prefix “DPY”.
Errors generated by the Oracle Database itself commonly have the error prefix “ORA”.
Some differences between python-oracledb Thin and Thick mode errors are shown in the examples below:
- Binding: When binding is incorrect, the python-oracledb Thick mode may generate an Oracle Client library error such as:
ORA-01008: not all variables bound
DPY-4010: a bind variable replacement value for placeholder ":1" was not provided
DPY-6005: cannot connect to database. Connection failed with "[Errno 61] Connection refused"
DPY-4000: cannot connect to database. Unable to find " " in file_name>
ORA-12154: TNS:could not resolve the connect identifier specified
DPY-6001: cannot connect to database. Service " " is not registered with the listener at host " " port port>. (Similar to ORA-12514)
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
© Copyright 2016, 2023, Oracle and/or its affiliates. All rights reserved. Portions Copyright © 2007-2015, Anthony Tuininga. All rights reserved. Portions Copyright © 2001-2007, Computronix (Canada) Ltd., Edmonton, Alberta, Canada. All rights reserved. Revision 26f3d661 . Last updated on Jul 20, 2023.
Database Handling Errors in Python
There are many sources of errors. A few examples are a syntax error in an executed SQL statement, a connection failure, or calling the fetch method for an already canceled or finished statement handle.
The DB API defines a number of errors that must exist in each database module. The following table lists these exceptions.
| Sr.No. | Exception & Description |
|---|---|
| 1 | Warning Used for non-fatal issues. Must subclass StandardError. |
| 2 | Error Base class for errors. Must subclass StandardError. |
| 3 | InterfaceError Used for errors in the database module, not the database itself. Must subclass Error. |
| 4 | DatabaseError Used for errors in the database. Must subclass Error. |
| 5 | DataError Subclass of DatabaseError that refers to errors in the data. |
| 6 | OperationalError Subclass of DatabaseError that refers to errors such as the loss of a connection to the database. These errors are generally outside of the control of the Python scripter. |
| 7 | IntegrityError Subclass of DatabaseError for situations that would damage the relational integrity, such as uniqueness constraints or foreign keys. |
| 8 | InternalError Subclass of DatabaseError that refers to errors internal to the database module, such as a cursor no longer being active. |
| 9 | ProgrammingError Subclass of DatabaseError that refers to errors such as a bad table name and other things that can safely be blamed on you. |
| 10 | NotSupportedError Subclass of DatabaseError that refers to trying to call unsupported functionality. |
Your Python scripts should handle these errors, but before using any of the above exceptions, make sure your MySQLdb has support for that exception. You can get more information about them by reading the DB API 2.0 specification.