The case of the missing connection close

I am trying to create a generic db_utils.py module that I will then generically import into programs as needed.

The first function I am trying to implement iis to generically open an Oracle database connection based on passed database/authentication parameters.

So far, I have been able to:

- import the custom module db_utils
- pass the parameters function dbopen in the custom module
- open the connection to the requested database
- pass back the cursor object to the calling program for later processing.

The problem is in closing the connection.

No matter what I try, I receive an error stating that the name is not defined.

I receive an error such as:

Traceback (most recent call last):
File "M:\My Documents\python_code\oracle\maestrodev_oracle_connector.py", line 55, in ?
db_utils.close()
AttributeError: 'module' object has no attribute 'close'

Does anyone have any ideas on how to properly close the connection?



The db_utils module code is a rather short


def dbopen(db,uname,passwd):
#
# Import required system modules needed specifically for function
#
import cx_Oracle

#
# Connect to remote database
#
connection = cx_Oracle.connect(dsn=db,user=uname,password=passwd)

#
# Return cursor object to calling program
#
return(connection.cursor())


The code that calls the db_utils module is

#!c:\Python23\python
#
# File name: maestrodev_oracle_connector.py
# Author: Andrew Robert
# Date: 11/26/04
#
#
# Modification History
#
# Version Programmer Description
# 1.0 AAR Creation
# 1.1 AAR Shift database opens to called module
# 1.2 AAR Fixed database link close - now works
#
#
# Note on modules
#
# The imported db_utils module was designed by AAR to make standard database
# routines available to all python programs as callable functions.
#
# Called functions are prefaced with the module name and then the function
# within the module.
#


import sys,db_utils

#
# Make connection to Oracle development database and assign to object
#

print 'Establishing connection to remote database\n'
cursobj = db_utils.dbopen('test_d','FOO','foo')

#
# Formulate sample querry
#
cursobj.execute('SELECT userid, name, role, desk_phone, pager FROM contacts')

#
# Extract querry results
#
results=cursobj.fetchall()

for row in results:
print row

#
# Break connection to Oracle development database
#
print '\n\n\nDisconnecting from remote database'

db_utils.close()

raw_input("\n\n\t\tPress Enter To Continue")


Any help you can provide on this would be greatly appreciated.

You can't use db_utils.close() because you haven't written a close() function in db_utils yet...

Hi,

The close function I called db_utils.close() was the last stab at trying to close the connection.

I knew it was wrong but I figured I'd give it a whirl anyway.

The problem is that I do not see how to close the connection once the original dbopen function passes the cursor object back to the main calling program.

I tried returning the connection itself back to the calling program
like below but that fails too.

def dbopen(db,uname,passwd):

#
# Import required system modules needed specifically for function
#
import cx_Oracle

#
# Connect to remote database
#
connection = cx_Oracle.connect(dsn=db,user=uname,password=passwd)

#
# Return connection
#
return(connection)

How does that fail?

As I see it (and I don't have Oracle or the cx_Oracle to test it), if you keep access to the connection object then you can call connection.close() when you need to...

I am at a loss as to why passing the connection back failed.

Even so, I was able to come up with a better solution using classes.

A sample of the class definitions are:




import cx_Oracle



class db:

def __init__(self, db, uname, passwd):

self.connection = cx_Oracle.connect(dsn=db,user=uname,password=passwd)

def execute(self, sql):

cursor = self.connection.cursor()

cursor.execute(sql)

return cursor

def close(self):

self.connection.close()

self.connection = None # Prevent reusing a closed connection