Upgraded to latest Cx_Oracle release (5.1.3 to 7.1.3) and now all my code is broken. Problem appears to be occurring when attempting to pass a cursor.
def select_query_remy(self,sql,params=None): with cx_Oracle.connect(self.connection) as con: cur = con.cursor() if params: cur.execute(sql,params) else: cur.execute(sql) #print cur.fetchall() return cur
I’m getting correct data in the fetchall so I know it is connecting properly and returning a valid result. So I comment it out.
In the other scripts I call it and rows should now store the cursor correct?
rows = con.select_query_remy(sql)
When I fetchall()
from the other script I get below and get the error.
print rows.fetchall()cx_Oracle.DatabaseError: DPI-1039: statement was already closed
Any ideas?
full db.py
import cx_Oracle class db: list ={} def __init__(self): ''' AMFM Python DB connectors ''' db.list["disprod"] = [999,"disprod.prod.com"] db.list["prodqry"] = [999,"prodqry.prod.com"] db.list["amfmprod"] = [999,"amfmprod.prod.com"] db.list["esriprod"] = [999,"esriprod.prod.com"] db.list["amfmtest"] = [999,"amfmtest.prod.com"] db.list["amfmdev"] = [999,"CCTdAMFM.prod.com"] db.list["amfmtran"] = [999,"amfmtran.prod.com"] def setup(self,env, username, password): self.username = username self.password = password self.env = env self.port = db.list[env][0] self.ip = db.list[env][1] if env == "prodqry": self.env = "prodqry.world" elif env == "amfmprod": self.env = "amfmprod.world" elif env == "esriprod": self.env = "esriprod.world" else: self.env = env self.connection = self.username+"/"+self.password+"@"+self.ip+":"+str(self.port)+"/"+self.env def select_query_remy(self,sql,params=None): with cx_Oracle.connect(self.connection) as con: cur = None cur = con.cursor() if params: cur.execute(sql,params) else: cur.execute(sql) return cur
In another python I create a new DB and try to get the cursor back.
con = db() con.setup("xxx","user","password") rows = con.select_query_remy(sql) print rows.fetchall()
Result:
Traceback (most recent call last): File "C:Python27ArcGIS10.5testtest.py", line 30, in <module> print rows.fetchall() cx_Oracle.DatabaseError: DPI-1039: statement was already closed
If I return fetchall() the data comes across. When I pass the cursor itself the error occurs.
def select_query_remy(self,sql,params=None): with cx_Oracle.connect(self.connection) as con: cur = None cur = con.cursor() if params: cur.execute(sql,params) else: cur.execute(sql) #return cur return cur.fetchall()
Any ideas why can’t I pass the cursor as it was done in the past?
Advertisement
Answer
Your error has to do with the with
-block (also known as a context manager). Let me add a couple comments to the select_query_remy
function:
def select_query_remy(self,sql,params=None): with cx_Oracle.connect(self.connection) as con: # connects to database cur = None cur = con.cursor() if params: cur.execute(sql,params) else: cur.execute(sql) return cur # closes connection (invalidates cursor too!)
Since the with
-block manages the context of the database connection, the connection will be closed after the block is exited.
To fix the error, I suggest performing fetchall
inside the function directly then working with that data outside the function. (You have already kinda figured this out.)
Further Reading: The Python Language Reference: The with
statement