Single Database Connection Throughout The Python Application (following Singleton Pattern)

My Question is what is the best way to maintain the single database connection in the entire application? Using Singleton Pattern? How? Conditions that are needed to be taken care

Solution 1:

For now, I am going ahead with the singleton class approach. Anyone seeing the potential flaws in this, feel to mention them :)

DBConnector class for creating a connection

classDBConnector(object):

   def__init__(self, driver, server, database, user, password):

        self.driver = driver
        self.server = server
        self.database = database
        self.user = user
        self.password = password
        self.dbconn = None# creats new connectiondefcreate_connection(self):
        return 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)

    # For explicitly opening database connectiondef__enter__(self):
        self.dbconn = self.create_connection()
        return self.dbconn

    def__exit__(self, exc_type, exc_val, exc_tb):
        self.dbconn.close()

DBConnection class for managing the connections

classDBConnection(object):
    connection = None    @classmethoddefget_connection(cls, new=False):
        """Creates return new Singleton database connection"""if new ornot cls.connection:
            cls.connection = DBConnector().create_connection()
        return cls.connection

    @classmethoddefexecute_query(cls, query):
        """execute query on singleton db connection"""
        connection = cls.get_connection()
        try:
            cursor = connection.cursor()
        except pyodbc.ProgrammingError:
            connection = cls.get_connection(new=True)  # Create new connection
            cursor = connection.cursor()
        cursor.execute(query)
        result = cursor.fetchall()
        cursor.close()
        return result

Solution 2:

class DBConnector(object):
    def __new__(cls):
        if not hasattr(cls, 'instance'):
            cls.instance = super(DBConnector, cls).__new__(cls)
        return cls.instance

    def __init__(self):
        #your db connection code in constructor

con = DBConnector()
con1 = DBConnector()
con is con1 # output is True

Hope, above code will helpful.