Closing a cx_Oracle Connection While Allowing for a Down Database

Question

The following cx_Oracle code works fine when the database is up:

#!C:\Python27
import cx_Oracle

try:
    conn = cx_Oracle.connect("scott/tiger@oracle")

    try:
        curs = conn.cursor()
        curs.execute("SELECT dummy FROM sys.dual")
        print curs.fetchone()[0]
    finally:
        curs.close()
finally:
    conn.close()

But if the database happens to be down when I run this script, a NameError is raised:

Traceback (most recent call last):
  File "C:\Users\ArtMetzer\Documents\Code\Python\db_conn_test.py", line 14, in <module>
    conn.close()
NameError: name 'conn' is not defined

This makes sense to me: cx_Oracle wasn't able to instantiate a connection, so the variable conn never got set, and hence has no close() method.

In Python, what's the best way to ensure your database connection closes, while still gracefully handling the condition of a down database?

Doing something like the following seems like a massive kludge to me:

finally:
    try:
        conn.close()
    except NameError:
        pass

Spaniel

Answer 1

You can try initializing conn to something like None before-hand and testing that in the finally block. This works because the only place the connection is set to something else is when it is opened. So opened implies non-None and None implies not-opened:

#!C:\Python27
import cx_Oracle

conn = None
try:
    conn = cx_Oracle.connect("scott/tiger@oracle")

    try:
        curs = conn.cursor()
        curs.execute("SELECT dummy FROM sys.dual")
        print curs.fetchone()[0]
    finally:
        curs.close()
finally:
    if conn is not None:
        conn.close()

Leader

Answer 2

According to the docs, you don't really need to bother closing the connection: https://cx-oracle.readthedocs.io/en/latest/user_guide/connection_handling.html#closing-connections

The important line being:

Alternatively, you may prefer to let connections be automatically cleaned up when references to them go out of scope. This lets cx_Oracle close dependent resources in the correct order.

Ranie

In a long running scope that does a lot of non-DB work after finishing with the connection, it's generally more efficient to explicitly close the connection (and LOBs and cursors) ASAP if it isn't going to be used again for some time. And use a connection pool, otherwise there are costs of reopening connections. – Orpha

Answer 3

(not exactly an answer, but comments don't have nice formatting)

Try this:

#!C:\Python27
import cx_Oracle

try:
    conn = cx_Oracle.connect("scott/tiger@oracle")

    try:
        curs = conn.cursor()
        curs.execute("SELECT dummy FROM sys.dual")
        print curs.fetchone()[0]
    finally:
        curs.close()
        conn.close()
except Exception as e:
    print e

Not ideal, but should work better. I'm also wondering why so much nesting. Why not do this:

#!C:\Python27
import cx_Oracle

try:
    conn = cx_Oracle.connect("scott/tiger@oracle")
    curs = conn.cursor()
    curs.execute("SELECT dummy FROM sys.dual")
    print curs.fetchone()[0]
    curs.close()
    conn.close()
except Exception as e:
    print e

BTW, I have this assumption that the connection and the cursor will close automatically on exit, removing the need to close them explicitly.

Augustaugusta

In the last example, curs.close() and conn.close() would never be called if curs.execute() failed. However, I bet that everything would get cleaned up during the exit. One would have to check for active connections in Oracle to see if the connection did get cleaned up. – Propel