1

I have a Python database file, with MySQL Pooling setup like below

import mysql.connector
from mysql.connector import Error
from mysql.connector.connection import MySQLConnection
from mysql.connector import pooling


import pandas as pd
import datetime as dt
from contextlib import closing

#Outside of any function :
connection_pool = mysql.connector.pooling.MySQLConnectionPool(pool_name="database_pool",
                                                           pool_size=25,
                                                           pool_reset_session=True,
                                                           host='XXXXX',
                                                           database='XXXXX',
                                                           user='XXXXX',
                                                           password='XXXX')

In order to get a pooled connection I use the blow function located within the same file

def getDBConnection():
  try:
      connection_obj = connection_pool.get_connection()
      cursor = connection_obj.cursor()
      return connection_obj, cursor
  except Error as e:
     print(f"Error while connecting to MySQL using Connection pool : {e}")

Now lets say I want to preform a simple select function using a pooled connection (still within the same database file) - and then return the connection :

def dbGetDataHeadersForRunMenuBySubSet(strSubset):
    connection_object, cursor = getDBConnection() 
    if connection_object.is_connected(): 
        query = 'SELECT * FROM someTable'
        cursor.execute(query)
           
        #Now close the connection
        closeDBConnection(connection_object, cursor)

code to attempt to close the Pool :

def closeDBConnection(connection_obj,cursor):
    if connection_obj.is_connected():
        connection_obj.close()
        cursor.close()

However after 25 runs I get the error back saying

Error while connecting to MySQL using Connection pool : Failed getting connection; pool exhausted

Using the debugger I can see that the closeDBConnection is been run , and that it appears to hit every step with no errors.

So my question is : Why am I running out of pools if I am closing them each time ?

All in all, I am actually looking to make a persistent connection , but in Python I cant find any realy examples on persistence , and all the examples I have looked at seem to point towards pooling. I am new (ish) to Python - so I have no issues here syaing that I know I have made a mistake somewhere.

Having played with this further : adding "connection_object.close()" at the end of each individual function will free the connection_pool e.g

def dbGetDataHeadersForRunMenuBySubSet(strSubset):
    connection_object, cursor = getDBConnection() 
    if connection_object.is_connected(): 
        query = 'SELECT * FROM someTable'
        cursor.execute(query)
           
        #Now close the connection
        #closeDBConnection(connection_object, cursor) <--- Not working for me
        connection_object.close() <---- This WILL work instead. –

But the DB stuff is just so slow in comparrision to Excel mySQL Connecter (Excel is almost 3 times faster doing the same thing. I think this is because its easy to get a persistent connection in EXCEL - something which I cant do in python (I am new remember ;-) )

Daragh
  • 123
  • 3
  • 13
  • You shouldn't need to control the connection pool yourself - does it work up a level if you just use connections and dispose of them as soon as the immediate query is done? – Andrew Morton Jan 19 '21 at 20:22
  • I have called the connection from a different class - e.g called a function to create a connection string from a completly different class , and then just passed the param around via different functions to prevent having to reconnect. Other than that , I am not sure what you mean by "does it work up a level" – Daragh Jan 20 '21 at 00:10
  • Does this answer: [using one database connection across multiple functions in python](https://softwareengineering.stackexchange.com/a/291705/175972) help? – Andrew Morton Jan 22 '21 at 18:26
  • Thanks mate. I tried this pooling method (I am actually using it) BUT I need to call the functions from many different classes. I actually just got the connection once and "carried" it around from method to method. I would have hoped there was just some global connection variable I could use. I dont actually need many connections at once (in fact everything is in sequence) so just trying to keep the DB Connection open is all I need. Thanks for the help @AndrewMorton – Daragh Jan 30 '21 at 17:23

0 Answers0