title: Python - Manage cursor connection outside context manager
body: <p>I'm new at Python and I'm trying to build a start project to get into this language.</p>
  
  <p>I created a SQLite3 DB and managed to make transactions with it.
  Everything works fine. </p>
  
  <p>I wanted to get deeper in Python so I've been searching and discovered <strong>Decorators</strong> and <strong>Context Manager</strong> and I was trying to implement these concepts on my Query Execution's functions. However, I'm stuck with a problem.</p>
  
  <p>I've created a class that handles the open and close connection tasks.</p>
  
  <p><strong>DB_ContextManager.py</strong> class:</p>
  
  <pre class="lang-py prettyprint-override"><code>class DB_ContextManager():
      def __init__(self, db_connection):
          self.db_connection = db_connection
  
      def __enter__(self):
          self.conn = sqlite3.connect(self.db_connection)        
          return self.conn
  
      def __exit__(self, exc_type, exc_val, exc_tb): # obligatory params
          self.conn.close()
  </code></pre>
  
  <p>And also created <strong>ConnectionDB.py</strong> which is responsible for executing queries.</p>
  
  <pre><code>from Database.DB_ContextManager import DB_ContextManager as DB_CM
  
  # Handles SELECT queries
  def ExecuteSelectQuery(self, pQuery):
      try:
          with DB_CM(db_connection_string) as conn:
              cur = conn.cursor()
              cur.execute(pQuery)
              result = cur.fetchall()        
              return result
  
      except Exception as e:
          LH.Handler(log_folder, 'ConnectionDB', 'Queries', 'ExecuteSelectQuery', e)            
          raise DE.ConnectionDB_Exception()
  
  # Handles INSERTs, UPDATEs, DELETEs queries
  def ExecuteNonQuery(self, pQuery):
      try:            
          with DB_CM(db_connection_string) as conn:
              cur = conn.cursor()
              cur.execute(pQuery)
  
      except Exception as e:
          LH.Handler(log_folder, 'ConnectionDB', 'Queries', 'ExecuteSelectNonQuery', e)            
          raise DE.ConnectionDB_Exception()
  </code></pre>
  
  <p>As you can see </p>
  
  <pre><code>with DB_CM(db_connection_string) as conn:
              cur = conn.cursor()
              cur.execute(pQuery)
  </code></pre>
  
  <p>is repeated in each function</p>
  
  <p>To avoid this situation, <strong>I'd like to create a Decorator function that encapsulates this piece of code</strong>.
  My problem is that the cursor 'dies' inside the ContextManager and, for example, ExecuteSelectQuery needs the cursor to fetch the return data after the query was executed.</p>
  
  <p>I know it's a small project and thinking so long term in future may not be necessary. But, remember, it's a start project and I'm learning to apply new concepts.</p>
  
  <hr>
  
  <p><strong>SOLUTION</strong></p>
  
  <p>As @blhsing suggested, I return the connection object instead of the cursor in the ContextManager.</p>
  
  <p>Also I handles <code>commit()</code> and <code>rollback()</code> in it.</p>
  
  <p>So, summarizing: </p>
  
  <p><strong>ConnectionDB.py</strong></p>
  
  <pre class="lang-py prettyprint-override"><code>def ExecuteSelectQuery(self, pQuery):
      with DB_CM(db_connection_string, pQuery) as cur:
          result = cur.fetchall()
          return result
  
  def ExecuteSelectNonQuery(self, pQuery):
      with DB_CM(db_connection_string, pQuery) as cur: 
          pass
  </code></pre>
  
  <p>and <strong>ConnectionDB.py</strong></p>
  
  <pre><code>class DB_ContextManager():
      def __init__(self, db_connection, pQuery):
          self.db_connection = db_connection
          self.query = pQuery
  
      def __enter__(self):
          try:
              self.conn = sqlite3.connect(self.db_connection)
              cur = self.conn.cursor()
              cur.execute(self.query)
              self.conn.commit()
              return cur            
  
          except Exception as e:
              LH.Handler(log_folder, 'DB_ContextManager', 'DB_ContextManager', '__enter__', e)
              self.conn.rollback()
              raise DE.ConnectionDB_Exception()
  
      def __exit__(self, exc_type, exc_val, exc_tb): # obligatory params
          self.conn.close()
  </code></pre>