I have this method on my class:
def exec_query(self, dbms, connection_string, sql):self.dbms = dbmsself.connection_string = connection_stringself.sql = sqlself._connection, self._cursor = self._mydb(self.dbms, self.connection_string)self._result = Noneself.query_result = []try:self._cursor.execute(self.sql)self.collected_data = self._cursorexcept Exception as e:raise eself._cursor.close()self._connection.close()return self.collected_dataThen I tried to get it's return value outside the class. And I got this error:
pyodbc.ProgrammingError: Attempt to use a closed cursor.Can't I assign the cursor to a variable? And why?
What I want to do is to process the cursor outside the class. Basically, I can just do .fetchall() and get the data then close the cursor. But .fetchall() eats the memory. So, I want to process the cursor outside.
Best Answer
self.collected_data is just another name for the exact same object as self._cursor. If you close self._cursor then self.collected_data is also closed.
You need to either use
self.collected_data = self.cursor.fetch_all()(or whatever) to save the actual data, or leave the connection and cursor open, process your data outside the class, and then call a close method.
You may want to look at making your class usable as a context manager, then use it something like this:
with db_connection.exec_query(dbms, connection_string, sql) as data:# process datawhile data:row in data.fetch_one()do_something_with_row()# on exit, cursor is closedI probably have the particulars on the while data wrong, but hopefully you get the idea. Your exec_query would look something like this:
class exec_query:def __init__(self, dbms, connection_string, sql):self.dbms = dbmsself.connection_string = connection_stringself.sql = sqlself._connection, self._cursor = self._mydb(self.dbms, self.connection_string)self._result = Noneself.query_result = []self._cursor.execute(self.sql)def __enter__(self):return self._cursordef __exit__(self, *args):self._cursor.close()self._connection.close()