Closing a cx_Oracle Connection While Allowing for a Down Database

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:
        c开发者_Go百科urs.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

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

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.

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