Python Database Exception Handling

Database Connection Exception Handling

Exception Handling

cx_Oracle module allows us to get details of the error code and error message that is generated by Oracle when the query executed has some errors. These details are stored in the cx_Oracle.DatabaseError class.

import cx_Oracle
	con = cx_Oracle.Connection('user/pswd@192.168.2.1/john')
	cur = cx_Oracle.Cursor(con)
	try:
		cur.execute(“INSERT INTO Computer VALUES (100)”)
		con.commit()
	except cx_Oracle.DatabaseError as e: #Handling the exception generated in line 5
		print(e)
	finally:
		con.close()

The above code has an error in the query. The output of the print statement is ORA-00947: not enough values. It is always a good practice to close connections in the finally block.

Apart from the Oracle exceptions that can occur while executing an improper query, errors can also occur due to wrong usage of cx_Oracle module such as:

• The cursor can be used only when the connection is established. Trying to use a cursor after closing the connection will lead to errors.

• Trying to perform a cursor operation after closing the cursor will lead to error

• Creating a cursor without a connection object will lead to an error.

Demo

The below code has error. Observe the output to understand what the error is and try to fix it.

import cx_Oracle
	con = cx_Oracle.Connection('user/pswd@192.168.2.1/john')
	cur = cx_Oracle.Cursor(con)
	try:
		cur.execute(“INSERT INTO Computer VALUES (100)”)
		con.commit()
	except cx_Oracle.DatabaseError as e:
		print(e)
	finally:
		con.close()

Modify the code to recreate other error scenarios previously discussed and observe the output.