Python Flask MySQL: How to persistently store connection pool object?

480 views Asked by At

After a long search, I could not find an answer to my question and if what I desire is even possible. My question concerns a MySQL connection implementation for a Flask API. What I desire to implement is as follows:

  1. When the Flask app is started, a create_db_connection method is called, which created a number of mysql connections in a pooling object.
  2. For each incoming request, a get_connection method is called, to get one connection from the poule
  3. And of course when the request is ended, a method close_connection is called to close the connection and mark it available in the pool.

The problem I'm having concerns persistently storing the connection pool such that it can be re-used for each request.

create_db_connection method:

def create_db_connection():
    print("-----INITIALISING-----")
    db_pool = mysql.connector.pooling.MySQLConnectionPool(pool_name = "BestLiar_Public_API",
                                                        pool_size = 10,
                                                        autocommit = True,
                                                        pool_reset_session = True,
                                                        user = 'user',
                                                        password = 'pass',
                                                        host = 'hostel',
                                                        database =' db')
    print("-----DB POOL INITIALISED-----")
    
    // SOLUTION TO PERSISTENTLY SAVE db_pool OBJECT

get_connection method:

def __enter__(self):
    try:

        // SOLUTION TO FETCH THE db_pool OBJECT > NAMED AS db_pool IN LINE BELOW

        self.con = db_pool.get_connection()
        self.cur = self.con.cursor(dictionary=True)
        if self.con.is_connected():
            return {'cur': self.cur, 'con': self.con}
        else:
            raise NoConnectionError("No database connection", "Pool connection not connected")
    except mysql.connector.PoolError:
        raise SystemOverload("Too many requests, could not process","No pool connection available")
    except:
        raise NoConnectionError("No database connection", "Unknown reason")

close_connection method:

def __exit__(self, type, value, traceback):
    if self.con: 
        self.cur.close()
        self.con.close()

I have tried storing the db_pool object as a global variable (undesirable) and have tried the flask global object (only works for one request). Anyone who has the key to the solution?

0

There are 0 answers