CODEWITHSUNDEEP
pythondatabaseclass
Maik Hasler
Maik Hasler

Reputation: 1400

How to initialize a database connection only once and reuse it in run-time in python?

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!

Upvotes: 10

Views: 14823

Answers (5)

jackbravo
jackbravo

Reputation: 1369

You can also use a simple function with a global variable:

_db = None
def db(host="127.0.0.1", user="root", password="", database="db_test"):
  global _db
  if _db is None:
    _db = mysql.connector.connect(host, user, password, database)
  return _db

db().execute(...) # this will do a lazy init of the DB

Upvotes: 0

Maik Hasler
Maik Hasler

Reputation: 1400

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.

Upvotes: 19

E_K
E_K

Reputation: 2249

This too does work and you are guaranteed to always have one instance of the database

def singleton(class_):
    instances = {}

    def get_instance(*args, **kwargs):
        if class_ not in instances:
            instances[class_] = class_(*args, **kwargs)
        return instances[class_]
    return get_instance

@singleton
class SingletonDatabase:
    def __init__(self) -> None:
        print('Initializing singleton database connection... ', random.randint(1, 100))

Upvotes: 0

EOD-STOCK-API
EOD-STOCK-API

Reputation: 1

The Reason you have to do all this is if you just create a connection once and leave it at that you then will end up trying to use a connection which is dropped

so you create a connection and attach it to your app then whenever you get a new request check if the connection still exists, with before request hook if not then recreate the connection and proceeed.

on create_app

    def create_app(self):

        if not app.config.get('connection_created'):
            app.database_connection = Database()
            app.config['connection_created'] = True


on run app


    @app.before_request
    def check_database_connection(self):
        if not app.config.get('connection_created') or not app.database_connection:
            app.database_connection = Database()
            app.config['connection_created'] = True

this will insure that your application always runs with an active connection and that it gets created only once per app

if connection is dropped on any subsequent call then it gets recreated again...

Upvotes: -1

Tim Roberts
Tim Roberts

Reputation: 54733

Kind of like this. It's a cheap way of using a global.

class Database(object):
   connection = None

   def __init__(self):
      if not Database.connection:
         Database.connection = mysql.connector.connect(host="127.0.0.1", user="root", password="", database="db_test")

   def query(self,sql):
      cursor = Database.connection.cursor()
      cursor.execute(sql)
   
   # Do database stuff here

Upvotes: 2

Related Questions