SQLAlchemy - Multithreading Best Practices - Packet sequence number wrong-CodePudding

I'm tring to make a clean Flask App which use SQLAlchemy and Multi-Threading.

I've read the doc : https://docs.sqlalchemy.org/en/14/orm/contextual.html#thread-local-scope but can't manage to make it work successfully.

SQL Alchemy is initate directly at the app init.py file with something like that :

db = SQLAlchemy(app)    
session_factory = sessionmaker( bind=db.engine, autocommit=False, autoflush=False)
DBSession = scoped_session( session_factory )

And in another file, which is not some Flask routes, on a Class :

from blabla import DBSession
class Worker(Thread):
    def __init__(self):
        Thread.__init__(self)
        self.dbsession = DBSession()
    def run(self):
        self.dbsession.query(...)

When I run my app, multiple Worker Class is running at the same time. Then I'm facing lot of errors like :

sqlalchemy.exc.InternalError: (pymysql.err.InternalError) Packet sequence number wrong - got 54 expected 1

What I am doing wrong ?

Thanks a lot in advance for your time !

CodePudding user response：

Looks like maybe you are duplicating the scoped session already provided by flask sqlalchemy. You can probably use db.session directly. Although you have to clean it up yourself when the thread ends or at regular intervals with db.session.remove() because it isn't within a request like flask expects. Why are you adding custom threading ?

https://flask-sqlalchemy.palletsprojects.com/en/2.x/quickstart/#road-to-enlightenment

a preconfigured scoped session called session

CodePudding user response：

Thanks to comments and answer, I've made it worky pretty smoothly. Just need to thinks it from scratch.

On my app.py :

db = SQLAlchemy(app, session_options={"autocommit":False, "autoflush":False, "expire_on_commit":False })

Ian highlighted that the Object db.session is already a scoped session. So no need to create another one manually. Moreover, I didn't know that it's possible to add session_options directly at this step. Obviously it's easier to directly configure the already integrated one.

On my others files :

dbsession = db.session
dbsession.query(...)
...
dbsession.commit()
dbsession.close()

Each new db.session seems to create a "real" session, I can see them directly on my MySQL instance. So, It's very important to close() and limit the number of concurent threads.

Did you see something wrong in this way ? The answer is YES.

EDIT : With Flask-SQLALchemy V3 comes a new way of deals whith context. The session scope to use the current app context instead of the thread. That is a problem when used out of context.

It's possible to add with app.app_context(): at some points. But it's not clear for me where it has to be done. And I feel like this will overload my code.

Maybe it's possible to come back to the previous behaviour directly from the params ? Don't know how to for the moment.