title: Closing SQLite3 connections in Python (when connection is part of an object)
body: <p>I have been trying to find an answer to this question all night and I still haven't come across a definite answer. Normally when I work with sqlite3 i would use the following pattern:</p>
  
  <pre><code>import sqlite3
  
  db = sqlite3.connect('whatever.db')
  cursor = db.cursor()
  
  #do some stuff
  
  cursor.close()
  </code></pre>
  
  <p>Now I trying to evolve my understanding of OOP and databases so I thought I would create a controller object to interact with the database. I have come up with the following:</p>
  
  <p>A class which just defines the connection and cursor:</p>
  
  <pre><code>import sqlite3
  
  class coffee_shop_controller:
  
      def __init__(self):
          self.db = sqlite3.connect("coffeeshop.db")
          self.cursor = self.db.cursor()
  
      def close(self):
          self.cursor.close()
  </code></pre>
  
  <p>I subclass this for the various controllers that I need. For example:</p>
  
  <pre><code>class customer_controller(coffee_shop_controller):
  
      """creates a controller to add/delete/amend customer records in the
         coffee shop database"""
  
      def __init__(self):
          super().__init__()
  
      def add_customer(self,fn,ln,sa,t,pc,tn):
          sql = """insert into customer
                   (first_name,last_name,street_address,town,post_code,telephone_number)
                   values
                   ('{0}','{1}','{2}','{3}','{4}','{5}')""".format(fn,ln,sa,t,pc,tn)
          self.cursor.execute(sql)
          self.db.commit()
  </code></pre>
  
  <p>I appreciate that the design pattern may not be great (open to suggestions) and that I really should be preventing SQL injection but the closing the connection is interesting me at the moment.</p>
  
  <p>From searching around the <a href="http://docs.python.org/py3k/library/sqlite3.html?highlight=sqlite3#sqlite3" rel="nofollow noreferrer">Python docs</a> the comment line suggests that we <strong><em>can</em></strong> close the connection, not that we <strong><em>must</em></strong>. Is this right? Do I not really need to bother?</p>
  
  <p>If I do need to bother then there seems to be a split on what I should do:</p>
  
  <ul>
  <li>Manually close the connection</li>
  <li><a href="http://eli.thegreenplace.net/2009/06/12/safely-using-destructors-in-python/" rel="nofollow noreferrer">Use the <code>__del__</code> method</a></li>
  <li><a href="https://stackoverflow.com/questions/974813/cleaning-up-an-internal-pysqlite-connection-on-object-destruction">Use <code>with</code> or <code>atexit</code></a></li>
  </ul>
  
  <p>Is there anything definitive here? The <code>__del__</code> method makes the most sense to me but maybe that's my ignorance talking.</p>
  
  <p>Thanks for any suggestions you can offer.</p>
  
  <p>Adam.</p>