python-mysql connection pool implementation

Question

This is a mysql connection pool class which i got from a tutorial

    import time
import mysql.connector.pooling
from Constants import dbconfig
from LoggerClass import appLog

class MySQLPool(object):
    """
    create a pool when connect mysql, which will decrease the time spent in 
    request connection, create connection and close connection.
    """
    def __init__(self, host="172.0.0.1", port="3306", user="root",
                 password="123456", database="test", pool_name="mypool",
                 pool_size=3):
        res = {}
    self._host = host
    self._port = port
    self._user = user
    self._password = password
    self._database = database

    res["host"] = self._host
    res["port"] = self._port
    res["user"] = self._user
    res["password"] = self._password
    res["database"] = self._database
    self.dbconfig = res
    self.pool = self.create_pool(pool_name=pool_name, pool_size=pool_size)

def create_pool(self, pool_name="mypool", pool_size=3):
    """
    Create a connection pool, after created, the request of connecting 
    MySQL could get a connection from this pool instead of request to 
    create a connection.
    :param pool_name: the name of pool, default is "mypool"
    :param pool_size: the size of pool, default is 3
    :return: connection pool
    """
    try:
        pool = mysql.connector.pooling.MySQLConnectionPool(
            pool_name=pool_name,
            pool_size=pool_size,
            pool_reset_session=True,
            **self.dbconfig)
        appLog('info','system','MysqlConnectorClass.py','create_pool','mysql connection established')
        return pool
    except Exception as exce:
        appLog('error','system','MysqlConnectorClass.py','create_pool','error in creating mysql connection'+str(exce))
        return None

def close(self, conn, cursor):
    """
    A method used to close connection of mysql.
    :param conn: 
    :param cursor: 
    :return: 
    """
    try:
        cursor.close()
        conn.close()
        appLog('info','system','MysqlConnectorClass.py','close connection','mysql pool connection closed')
    except Exception as exce:
        appLog('info','system','MysqlConnectorClass.py','Error in close connection','mysql pool connection was not closed '+str(exce))


def execute(self, sql, args, buff, fetchMode):
    """
    Execute a sql, it could be with args and with out args. The usage is 
    similar with execute() function in module pymysql.
    :param sql: sql clause
    :param args: args need by sql clause
    :param commit: whether to commit
    :return: if commit, return None, else, return result
    """
    # get connection form connection pool instead of create one.
    if self.pool!=None:
        conn = self.pool.get_connection()
        if conn!=None:
            if buff:
                cursor = conn.cursor(buffered=True)
            else:
                cursor = conn.cursor()
            if args:
                cursor.execute(sql, args)
            else:
                cursor.execute(sql)
        
            if fetchMode == 'all':
                res = cursor.fetchall()
            elif fetchMode == 'one':
                res = cursor.fetchone()

            return {"res":res,"cur":cursor, "conn":conn}
        else:
            return None
    else:
        return None

def executemany(self, sql, args, buff, fetchMode):
    """
    Execute with many args. Similar with executemany() function in pymysql.
    args should be a sequence.
    :param sql: sql clause
    :param args: args
    :param commit: commit or not.
    :return: if commit, return None, else, return result
    """
    # get connection form connection pool instead of create one.
    if self.pool!=None:
        conn = self.pool.get_connection()
        if conn!=None:
            conn = self.pool.get_connection()
            if buff:
                cursor = conn.cursor(buffered=True)
            else:
                cursor = conn.cursor()
            if args:
                cursor.executemany(sql, args)
            else:
                cursor.executemany(sql)
        
            if fetchMode == 'all':
                res = cursor.fetchall()
            elif fetchMode == 'one':
                res = cursor.fetchone()

            return {"res":res,"cur":cursor, "conn":conn}
        else:
            return None
    else:
        return None

and i have to call this class from another python file say pyPoolTest.py

import time
from Constants import dbconfig
from MysqlConnectorClass import MySQLPool


if __name__ == "__main__":
    
    sql = "select * from GTG_PROJECTS"
    mysql_pool = MySQLPool(**dbconfig)
    if mysql_pool!=None:
        sqlData = mysql_pool.execute(sql,None,False,'all')
        print(sqlData['res'])
        mysql_pool.close(sqlData['conn'],sqlData['cur'])
    else:
        print('Error in accesing db')

i was wondering in every python file i have to first initialize the first python class to get the pool object and access the DB, so in my understanding a new pool connection is created. So i cant use the already created connection pool object and reuse the connection again.

What am i missing or doing wrong?

I want to access db from every python file, so i have to initialize the class from those respective files.

Please share me the correct way to utilize the mysql connection pool class or concept so that i can access the pool from all my python files.

I hope my question is clear

I searched a lot in how to access python-mysql class from another file and couldnt find a suitable answer

Please help

Answer 1

Rather than having each use case import and use MySQLPool directly, you should make an object that initializes and stores a single shared MySQLPool instance, and have your application use cases talk to that.