[Tutor] The case of the missing close

[Branimir Petrovic]

> I get the message  
> 
> Traceback (most recent call last):
>   File 
> "C:\python_code\oracle\maestrodev_oracle_connector.py", line 34,
> in ?
>     dbobj = db_utils.db('maestro_d','MAESTRO_OWNER','joecool')
>   File "C:\python_code\oracle\db_utils.py", line 9, in __init__
>     class db:
> NameError: global name 'cx_Oracle' is not defined
> 
> The main script imports this module on load so I am a little 
> confused as
> to why it is balking.
> 
> Any thoughts?
> 

Import of cx_Oracle should happen in your module that is using it i.e. 
module db_utils.py should do it.

Following snippet is from "my way" of dealing with cursor/connection
"situation" (you'd have to rip out all references to 'ermpap' if you
want to run it):

# FileName:	oraUtils.py

import cx_Oracle, onErrorActionMapper as ermap


def openCursor(userName=None, userPWD=None, sqlnetName=None,
as_sysdba=False,
    errorActionFnct=ermap.quitInFlames, echoToSTDOUT=True, loggerObj=None):
    """Open cursor or 'quitInFlames' if can not.

    Echo to STDOUT or not, log to logfile or not."""

    def __SQLPlusInput(sqlStatement):
        """Wrap up actual sql statement in SQL*Plus look-alike line"""
        return "%s%s%s" % ("SQL> ", sqlStatement, ";")

    try:
        if as_sysdba:
            sqlStatement = __SQLPlusInput('CONNECT %s/%s@%s AS SYSDBA' % \
                            (userName, userPWD, sqlnetName))
        else:
            sqlStatement = __SQLPlusInput('CONNECT %s/%s@%s' % \
                            (userName, userPWD, sqlnetName))

        if echoToSTDOUT: print sqlStatement
        if loggerObj: loggerObj.writeLine(sqlStatement)

        if as_sysdba:
            __connection = cx_Oracle.connect(userName, userPWD, sqlnetName, 
                                            cx_Oracle.SYSDBA)
        else :
            __connection = cx_Oracle.connect(userName, userPWD, sqlnetName)

        cursor = __connection.cursor()
        cursor.arraysize = 1000
        if echoToSTDOUT: print 'Connected.\n'
        if loggerObj: loggerObj.writeLine('Connected.\n')
        return cursor
    except cx_Oracle.DatabaseError, err:
        errMsg=str(err)
        if callable(errorActionFnct): 
            errorActionFnct(ermap.OnOpenCursorError(errMsg), 
                                                        loggerObj=loggerObj)
        return False


def closeCursor(cursor):
    """Closes opened cursor 'softly' returning True if cursor got closed.
    (silently fails returning False if cursor was already closed)"""

    if cursor:
        try: 
            cursor.close()
            return True
        except cx_Oracle.InterfaceError: 
            return False
    else:   return False


def closeCursorAndConnection(cursor):
    """Close opened cursor AND the connetction that opened this cursor.
    Return True if both got closed.
    Return False if attempt to close both fails (for any reason)."""

    if cursor:
        connection = cursor.connection
        cursor_close_result = closeCursor(cursor)
        try:
            connection.close()
            return cursor_close_result
        except cx_Oracle.InterfaceError:
            return False
    else :  return False