cx_Oracle and Exception Handling - Good practices?

Question

I'm trying to use cx_Oracle to connect to an Oracle instance and execute some DDL statements:

db = None
try:
    db = cx_Oracle.connect('username', 'password', 'hostname:port/SERVICENAME')
#print(db.version)
except cx_Oracle.DatabaseError as e:
    error, = e.args
    if error.code == 1017:
        print('Please check your credentials.')
        # sys.exit()?
    else:
        print('Database connection error: %s'.format(e))
cursor = db.cursor()
try:
    cursor.execute(ddl_statements)
except cx_Oracle.DatabaseError as e:
    error, = e.args
    if error.code == 955:
        print('Table already exists')
    if error.code == 1031:
        print("Insufficient privileges - are you sure you're using the owner account?")
    print(error.code)
    print(error.message)
    print(error.context)
cursor.close()
db.commit()
db.close()

However, I'm not quite sure what's the best design for exception handling here.

Firstly, I create the db object inside a try block, to catch any connection errors.

However, if it can't connect, then db won't exist further down - which is why I set db = None above. However, is that good practice?

Ideally, I need to catch errors with connecting, then errors with running the DDL statements, and so on.

Is nesting exceptions a good idea? Or is there a better way of dealing with dependent/cascaded exceptions like this?

Also, there are some parts (e.g. connection failures) where I'd like the script to just terminate - hence the commented out sys.exit() call. However, I've heard that using exception handling for flow control like this is bad practice. Thoughts?

Solution 1

However, if it can't connect, then db won't exist further down - which is why I set db = None above. However, is that good practice?

No, setting db = None is not best practice. There are two possibilities, either connecting to the database will work or it won't.

• Connecting to the database doesn't work:

  As the raised exception has been caught and not re-raised you continue until you reach cursor = db.Cursor().

  db == None, so, an exception that resembles TypeError: 'NoneType' object has no attribute 'Cursor' will be raised. As the exception generated when the database connection failed has already been caught, the reason for the failure is disguised.

  Personally, I'd always raise a connection exception unless you're going to try again shortly. How you catch it is up to you; if the error persists I e-mail to say "go and check the database".

• Connecting to the database does work:

  The variable db is assigned in your try:... except block. If the connect method does work then db is replaced with the connection object.

Either way the initial value of db is never used.

However, I've heard that using exception handling for flow control like this is bad practice.

Unlike other languages Python does use exception handling for flow control. At the end of my answer I've linked to several questions on Stack Overflow and Programmers that ask a similar question. In every example you'll see the words "but in Python".

That's not to say that you should go overboard but Python generally uses the mantra EAFP, "It's easier to ask forgiveness than permission." The top three voted examples in How do I check if a variable exists? are good examples of how you can both use flow control or not.

Is nesting exceptions a good idea? Or is there a better way of dealing with dependent/cascaded exceptions like this?

There's nothing wrong with nested exceptions, once again as long as you do it sanely. Consider your code. You could remove all exceptions and wrap the entire thing in a try:... except block. If an exception is raised then you know what it was, but it's a little harder to track down exactly what went wrong.

What then happens if you want to say e-mail yourself on the failure of cursor.execute? You should have an exception around cursor.execute in order to perform this one task. You then re-raise the exception so it's caught in your outer try:.... Not re-raising would result in your code continuing as if nothing had happened and whatever logic you had put in your outer try:... to deal with an exception would be ignored.

Ultimately all exceptions are inherited from BaseException.

Also, there are some parts (e.g. connection failures) where I'd like the script to just terminate - hence the commented out sys.exit() call.

I've added a simple class and how to call it, which is roughly how I would do what you're trying to do. If this is going to be run in the background then the printing of the errors isn't worthwhile - people won't be sitting there manually looking out for errors. They should be logged in whatever your standard way is and the appropriate people notified. I've removed the printing for this reason and replaced with a reminder to log.

As I've split the class out into multiple functions when the connect method fails and an exception is raised the execute call will not be run and the script will finish, after attempting to disconnect.

import cx_Oracle

class Oracle(object):

    def connect(self, username, password, hostname, port, servicename):
        """ Connect to the database. """

        try:
            self.db = cx_Oracle.connect(username, password
                                , hostname + ':' + port + '/' + servicename)
        except cx_Oracle.DatabaseError as e:
            # Log error as appropriate
            raise

        # If the database connection succeeded create the cursor
        # we-re going to use.
        self.cursor = self.db.cursor()

    def disconnect(self):
        """
        Disconnect from the database. If this fails, for instance
        if the connection instance doesn't exist, ignore the exception.
        """

        try:
            self.cursor.close()
            self.db.close()
        except cx_Oracle.DatabaseError:
            pass

    def execute(self, sql, bindvars=None, commit=False):
        """
        Execute whatever SQL statements are passed to the method;
        commit if specified. Do not specify fetchall() in here as
        the SQL statement may not be a select.
        bindvars is a dictionary of variables you pass to execute.
        """

        try:
            self.cursor.execute(sql, bindvars)
        except cx_Oracle.DatabaseError as e:
            # Log error as appropriate
            raise

        # Only commit if it-s necessary.
        if commit:
            self.db.commit()

Then call it:

if __name__ == "__main__":

    oracle = Oracle.connect('username', 'password', 'hostname'
                           , 'port', 'servicename')

    try:
        # No commit as you don-t need to commit DDL.
        oracle.execute('ddl_statements')

    # Ensure that we always disconnect from the database to avoid
    # ORA-00018: Maximum number of sessions exceeded. 
    finally:
        oracle.disconnect()

Further reading:

cx_Oracle documentation

Why not use exceptions as regular flow of control?
Is python exception handling more efficient than PHP and/or other languages?
Arguments for or against using try catch as logical operators

Solution 2

A different and possibly elegant solution is to use a decorator to your Database call functions. The decorator enables the ability to remediate the error and try the Database call again. For stale connections, the remediation is to reconnect and reissue the call. Here is the decorator that worked for me:

####### Decorator named dbReconnect ########
#Retry decorator
#Retries a database function twice when  the 1st fails on a stale connection
def dbReconnect():
    def real_decorator(function):
        def wrapper(*args, **kwargs):
            try:
                return function(*args, **kwargs)
            except  Exception as inst:
                print ("DB error({0}):".format(inst))
                print ("Reconnecting")
                #...Code for reconnection is to be placed here..
                ......
                #..end of code for reconnection
            return function(*args, **kwargs)
        return wrapper
    return real_decorator

###### Decorate the DB Call like this: #####
    @dbReconnect()
    def DB_FcnCall(...):
    ....

More details on Github: https://github.com/vvaradarajan/DecoratorForDBReconnect/wiki

Note: If you use connection pools, internal connection pool techniques which check a connection and refresh it if stale, will also solve the problem.

Comments

[sarath joseph]

I'm using the code snippet you suggested Ben but I'm bumping into the following error ORA-24550: signal received: Unhandled exception: Code=c0000005 Flags=0 Its not able to catch the exception on incorrect host or SID input and my python Django server crashes

[Ben]

I assume this is your question @sarath. This is an Oracle problem, nothing to do with the Python and I don't see how your Django server is relevant unless you're saying thatcx_Oracle.DatabaseError is not catching the exception. Can you add more details to your question?

[sarath joseph]

So the statement cx_Oracle.connect is not being caught by my exception blocks on wrong host and SID entry. How do I catch the exception I'm using cx.Oracle.connect in a try block with exception blocks that follow as except cx_Oracle.DatabaseError as e: error, = e.args if error.code == 1017: print('Please check your credentials.') else: print('Database connection error: %s' % (e,)) raise except cx_Oracle.Error as e: error=e.args print('Error.') raise

[Ben]

I'm not going to be able to answer a question in the comments @sarath, you need to ask one (and if you keep deleting them you'll get question banned so don't do that). Just edit your previous question with all the information. You haven't actually posted the exact code that you're using or the exact error message you're getting from Python. Don't catch the exception, let it be raised and see what it is.

[Beau Barker]

The connect shouldn't be inside the try block.

[Ben]

In __main__ @Beau? Thanks, I've moved it out. I've also removed print which has been annoying me for 6 years.

[Beau Barker]

Yep, much better @Ben 👍

[Superdooperhero]

How can one reconnect nicely in an infinite loop and keep on trying until it connects?

[Ben]

Put .connect() in a try: except block and ignore all errors @Superdooperhero; try: cx_Oracle.connect(...) except cx_Oracle.DatabaseError: pass. I'd strongly recommend against this though. You'd never find out if something was wrong.