title: single database connection throughout the python application (following singleton pattern)
body: <p><strong>My Question is what is the best way to maintain the single database connection in the entire application?</strong> <em>Using Singleton Pattern? How?</em></p>
  
  <p>Conditions that are needed to be taken care of:</p>
  
  <ol>
  <li>In case of multiple requests, I should be using the same connection</li>
  <li>In case connection is closed, create a new connection</li>
  <li>If the connection has timed-out, on new request my code should create a new connection.</li>
  </ol>
  
  <hr>
  
  <p>The driver to my Database is not supported by the Django ORM. And due to same driver related issues, I am using <code>pyodbc</code> to connect to the database. Right now I am having below class for creating and managing the DB connections:</p>
  
  <pre><code>class DBConnection(object):
      def __init__(self, driver, serve,
                   database, user, password):
  
          self.driver = driver
          self.server = server
          self.database = database
          self.user = user
          self.password = password
  
      def __enter__(self):
          self.dbconn = pyodbc.connect("DRIVER={};".format(self.driver) +\
                                       "SERVER={};".format(self.server) +\
                                       "DATABASE={};".format(self.database) +\
                                       "UID={};".format(self.user) +\
                                       "PWD={};".format(self.password) + \
                                       "CHARSET=UTF8",
                                       # "",
                                       ansi=True)
  
          return self.dbconn
  
      def __exit__(self, exc_type, exc_val, exc_tb):
          self.dbconn.close()
  </code></pre>
  
  <p>But the issue with this approach is that it will create new database connection for each query. What will be the better way to do it following <strong>singleton pattern</strong>? The way I can think of will hold the reference to the connection if the connection is closed. Something like:</p>
  
  <pre><code> def get_database_connection():
       conn = DBConnection.connection
       if not conn:
            conn = DBConnection.connection = DBConnection.create_connection()
       return conn
  </code></pre>
  
  <p><em>What will be the best way to achieve this? Any suggestion/ideas/examples?</em> </p>
  
  <p>PS: I was checking about using <a href="https://docs.python.org/2/library/weakref.html" rel="noreferrer"><code>weakref</code></a> which allows to create weak references to objects. I think it will be good idea to use <code>weakref</code> along with singleton pattern for storing the connection variable. This way I won't have to keep the connection <code>alive</code> when DB is not in use. What you guys say about this?</p>