Forums

I have several endpoints that import a SQLAlchemy Sessionmaker I've created, which is included in a global package, create a session, interact with my database, and then close the session. Each time I hit the endpoint, a new process is created (when I enter "show processlist;" in MYSQL) with a "command" of "sleep" and "NULL" info. Therefore, I quickly hit max_user_connections. I've read a lot about SQLAlchemy connection pooling, and tried several different solutions to no avail. I tried setting "pool_recycle=280", "poolclass=NullPool", as well as setting "pre_ping=True". Regardless of the settings I've tried so far, the functionality has seemed to be the same, with processes being created and seemingly being recycled after ~5 minutes.

What am I missing?

I don't personally use SQLAlchemy directly (I prefer the Flask-SQLAlchemy plugin), but from the docs it looks like perhaps the session.invalidate call might close things down fully?

I appreciate your quick response! Invalidate didn't fix the problem, I didn't notice any change in behavior. Interestingly enough, all processes close when I reload the webapp from the web dashboard.

I need to interact with my database (using SQLAlchemy) outside of Flask as well, which is why I wasn't using Flask-SQLAlchemy. I have the engine and all my models already defined.

I'll spend some time reading more about Flask-SQLAlchemy to try to understand the problem better, or see if I can easily refer to existing models/engines. Do you have any other recommendations in the mean time?

Edit: I read more about Flask-SQLAlchemy, it seems like it'd be significantly better to use in my web app instead of sqlalchemy, however it seems like I'd have to recreate my models to do so, which doesn't seem right.

Edit: Trying the solution found at the top of the accepted answer here: https://stackoverflow.com/questions/28789063/associate-external-class-model-with-flask-sqlalchemy. This doesn't change anything about the models, so trying to figure out how to update my queries to utilize Flask-SQLAlchemy.

Resolved! My solution was as follows: 1. Register my base model when initializing Flask-SQLAlchemy as described here. 2. Modify my queries to match the style of Flask-SQLAlchemy, totally removing the need to create and manage sqlalchemy sessions.

Excellent, glad you found a solution!