Reusing database connection object with pandas.read_sql for high frequency updates [Python/MariaDB/alive-db-connection]

  mariadb, pandas, python, read-sql

I’m trying to reuse the database connection object of MariaDB, and store it as a static variable of a Singleton class.

...import mariadb

class SingletonAux:
    __connection_string = None

    @staticmethod
    def get_connection_string():
        if not SingletonAux.__connection_string:   #<-- This part of the code never evaluates to false
            SingletonAux.__connection_string = mariadb.connect(
                user=f"{ENV-Variable}",
                password=f"{ENV-Variable}",
                host=f"{ENV-Variable}",
                database=f"{ENV-Variable}")
        return SingletonAux.__connection_string

    def __init__(self):

        if SingletonAux.__connection_string:
            print("Singleton cannot be instantiated more than once")
        else:
            self.get_connection_string()
            SingletonAux.__connection_string = self

...

Then, below I have my call to invoke the class’s connection:

...
pd.read_sql(compound_sql, con=con.get_connection_string(), etc-etc

But every time the above ‘read_sql’ executes, the new class object is created, and the connection_string is always None, hanse not a singleton at all. What am I doing wrong?

If one to ask a question why, I’m trying to query the data from my MariaDB database in short bursts at a high frequency (3-4 calls per second). It takes about .4 seconds for MariaDB connection string to initialize.
If I treat my SingletonAux class as a mariaDB connect string, such as:

pd.read_sql(compound_sql, con=SingletonAux, etc-etc

then Pandas complaining stating that SingletonAux does not have a cursor function, which is true. If I need to de-reference the maria object somehow, but I don’t know how.

If there’s a bug in my basic code, please help.

Source: Python Questions

LEAVE A COMMENT