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:
- When the Flask app is started, a
create_db_connection
method is called, which created a number of mysql connections in a pooling object. - For each incoming request, a
get_connection
method is called, to get one connection from the poule - 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?