Sql Alchemy QueuePool limit overflow
Add following method to your code. It will automatically close all unused/hanging connections and prevent bottleneck in your code. Especially if you are using following syntax Model.query.filter_by(attribute=var).first() and relationships / lazy loading.
@app.teardown_appcontext
def shutdown_session(exception=None):
db.session.remove()
Documentation on this is available here: http://flask.pocoo.org/docs/1.0/appcontext/
You can manage pool size by adding parameters pool_size and max_overflow in function create_engine
engine = create_engine("mysql://" + loadConfigVar("user") + ":" + loadConfigVar("password") + "@" + loadConfigVar("host") + "/" + loadConfigVar("schema"),
pool_size=20, max_overflow=0)
Reference is here
You don't need to close the session, but the connection should be closed after your transaction has been done. Replace:
rescount = DBSession.connection().execute("select resource_id,count(resource_id) as total FROM resourcestats")
By:
connection = DBSession.connection()
try:
rescount = connection.execute("select resource_id,count(resource_id) as total FROM resourcestats")
#do something
finally:
connection.close()
Reference is here
Also, notice that mysql's connection that have been stale is closed after a particular period of time (this period can be configured in MySQL, I don't remember the default value), so you need passing pool_recycle value to your engine creation