I am currently working on a huge project, which constantly executes queries. My problem is, that my old code always created a new database connection and cursor, which decreased the speed immensivly. So I thought it's time to make a new database class, which looks like this at the moment:

class Database(object):

   _instance = None

   def __new__(cls):
      if cls._instance is None:
         cls._instance = object.__new__(cls)
         try:
            connection = Database._instance.connection = mysql.connector.connect(host="127.0.0.1", user="root", password="", database="db_test")
            cursor = Database._instance.cursor = connection.cursor()
         except Exception as error:
            print("Error: Connection not established {}".format(error))
         else:
            print("Connection established")
      return cls._instance
    
   def __init__(self):
      self.connection = self._instance.connection
      self.cursor = self._instance.cursor
   
   # Do database stuff here

The queries will use the class like so:

def foo():
   with Database() as cursor:
      cursor.execute("STATEMENT")

I am not absolutly sure, if this creates the connection only once regardless of how often the class is created. Maybe someone knows how to initialize a connection only once and how to make use of it in the class afterwards or maybe knows if my solution is correct. I am thankful for any help!

Explanation

The keyword here is clearly class variables. Taking a look in the official documentation, we can see that class variables, other than instance variables, are shared by all class instances regardless of how many class instances exists.

Generally speaking, instance variables are for data unique to each instance and class variables are for attributes and methods shared by all instances of the class:

So let us asume you have multiple instances of the class. The class itself is defined like below.

class Dog:
   
   kind = "canine"          # class variable shared by all instances

   def __init__(self, name):
      self.name = name      # instance variable unique to each instance

In order to better understand the differences between class variables and instance variables, I would like to include a small example here:

>>> d = Dog("Fido")
>>> e = Dog("Buddy")
>>> d.kind   # shared by all dogs
"canine"
>>> e.kind   # shared by all dogs
"canine"
>>> d.name   # unique to d
"Fido"
>>> e.name   # unique to e
"Buddy"

Solution

Now that we know that class variables are shared by all instances of the class, we can simply define the connection and cursor like shown below.

class Database(object):

   connection = None

   def __init__(self):
      if Database.connection is None:
         try:
            Database.connection = mysql.connector.connect(host="127.0.0.1", user="root", password="", database="db_test")
         except Exception as error:
            print("Error: Connection not established {}".format(error))
         else:
            print("Connection established")

   def execute_query(self, sql):
      cursor = Database.connection.cursor()
      cursor.execute(sql)

As a result, the connection to the database is created once at the beginning and can then be used by every further instance. Note that the cursor is not cached, since it takes essentially no time at all to create a cursor. However, creating a connection is quite expensive, so it is sensible to cache them.