How to properly close mysql connections in sqlalchemy?

Question

I would like to know what is proper way to close all mysql connections in sqlalchemy. For the context, it is a Flask application and all the views share the same session object.

engine = create_engine("mysql+pymysql://root:[email protected]/my_database")

make_session = sessionmaker(bind=engine, autocommit=False)

session = ScopedSession(make_session)()

And when the app is teared down, the session is closed and engine is disposed

session.close()
engine.dispose()

But according to database log, I still have a lot of errors like [Warning] Aborted connection 940 to db: 'master' user: 'root' host: '172.19.0.7' (Got an error reading communication packets).

I have tried some solutions, including calling gc.collect() and engine.pool.dispose() but without success ...

I suspect there are still some connections opened by the engine behind the scene and they need to be closed. Is there anyway to list all the sessions/connections opened by the engine?

After spending a lot of time on this, any advice/help/pointer will be much appreciated! Thanks.

P.S: the dispose and close calls are inspired from How to close sqlalchemy connection in MySQL. Btw, what is a 'checked out' connection?

Answer 1

This may not answer your question completely, but I've been using this method to make sure all my sessions are closed. Every function that uses a session gets the provide_session decorator. Note the session=None argument must be present.

e.g

@provide_session()
def my_func(session=None):
    do some stuff
    session.commit()

I saw it used in the Incubator-Airflow project and really liked it.

import contextlib
from functools import wraps

...
Session = ScopedSession(make_session)

@contextlib.contextmanager
def create_session():
    """
    Contextmanager that will create and teardown a session.
    """
    session = Session()
    try:
        yield session
        session.expunge_all()
        session.commit()
    except:
        session.rollback()
        raise
    finally:
        session.close()


def provide_session(func):
    """
    Function decorator that provides a session if it isn't provided.
    If you want to reuse a session or run the function as part of a
    database transaction, you pass it to the function, if not this wrapper
    will create one and close it for you.
    """
    @wraps(func)
    def wrapper(*args, **kwargs):
        arg_session = 'session'

        func_params = func.__code__.co_varnames
        session_in_args = arg_session in func_params and \
            func_params.index(arg_session) < len(args)
        session_in_kwargs = arg_session in kwargs

        if session_in_kwargs or session_in_args:
            return func(*args, **kwargs)
        else:
            with create_session() as session:
                kwargs[arg_session] = session
                return func(*args, **kwargs)

    return wrapper

Answer 2

Finally I found the culprit: the apscheduler that is launched in a background process uses a session connected to the database and doesn't seem to release it properly.

Also the best practice we found for handling sqlalchemy sessions in a Flask app is to use scoped_session and make sure to call remove() on it at the end of the request (i.e. appcontext_teardown). This is also used in flask-sqlalchemy extension.