Btukfyl

I'm having a hard time implementing a "MySqlClient" class for my application. My application consists of several modules which have to make use of my database & some of the modules are running on other threads.

My intention is to make an instance for every module that needs to communicate with my MySql database. For example: every client connecting to a websocket server creates his own instance, a telegram bot client has its own instance, ..

I've been searching for days now, I've read the docs, searched the forums .. but somehow I'm missing something or I'm not implementing it the right way.

This is my class:

class MySqlClient():



    engine = None

    Session = None



    def __init__(self):     



        # create engine

        if MySqlClient.engine == None:

            MySqlClient.engine = sqlalchemy.create_engine("mysql+mysqlconnector://{0}:{1}@{2}/{3}".format(

                state.config["mysql"]["user"],

                state.config["mysql"]["password"],

                state.config["mysql"]["host"],

                state.config["mysql"]["database"]

            ))

            MySqlClient.Session = scoped_session(sessionmaker(bind=MySqlClient.engine))

            Base.metadata.create_all(MySqlClient.engine)



        self.session = MySqlClient.Session()



    def get_budget(self, budget_id):   

        try:



            q = self.session.query(

                (Budget.amount).label("budgetAmount"), 

                func.sum(BudgetRecord.amount).label("total")

                ).all().filter(Budget.id == budget_id).join(BudgetRecord).filter(extract("month", BudgetRecord.ts) == datetime.datetime.now().month)

            self.session.close()



            return { "budgetAmount": q[0].budgetAmount, "total": 0.0 if q[0].total == None else q[0].total }



        except Exception as ex:

            logging.error(ex)



        return None

When I start my application everything runs fine, I can execute the method "get_budget" returning the data. However, if after this I wait for 5 minutes, the method won't run again (if I don't wait, it still works). After about 15 minutes after I made the call, the query finally fails saying the MySql connection has dropped:

(mysql.connector.errors.OperationalError) MySQL Connection not available.

I also tried getting a new session before executing new queries. That didn't help either.

I've done things like this before but it's the first time I'm using an ORM & I'd like to keep the benefits of using ORM.

Any help would be greatly appreciated,

Regards