single database connection throughout the python application (following singleton pattern)
<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>