Closing a cx_Oracle Connection While Allowing for a Down Database

Art Metzer picture Art Metzer · Mar 9, 2011 · Viewed 16.5k times · Source

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

Answer

Mad Physicist picture Mad Physicist · Aug 28, 2014

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()