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:root@127.0.0.1/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?

Checked out connections are connections still used by ongoing sessions etc., and dispose cannot dispose of those. All in all this question would benefit from a proper [mcve]. — Ilja Everilä, Jan 11 '18 at 21:14
@IljaEverilä thanks! I've tried hard to create a small example that shows the issue but without success ... strangely everything works fine in a small example and the issue only shows up in our big code base. I suspect there's something we are doing wrong regarding creating and closing session that ends up in this side effect. By this post, I'm actually looking for best practices (and gotchas) regarding sqlalchemy sessions. — Son, Jan 12 '18 at 09:02
you could use `AssertionPool` to debug checked out connection related issues. — georgexsh, Jan 23 '18 at 05:08

score 11 · Answer 1 · answered Jan 11 '18 at 18:11

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

Thanks for the decorator! Most of our methods already have `session` argument so we can test it later by injecting *fake* session or disabling the commit to make sure data isn't modified in the test. ( We cannot recreate database at each test as we test directly with mysql and the cost of creating a complex database is quite high) — Son, Jan 12 '18 at 09:04
This is just what I've been looking for to keep my database under control at Python Anywhere! However, I had to remove the parenthesis from the end of `**@provide_session**` decorator to get it working in my code. Thanks for putting your answer out here @Sebastian - this was a big help for me at least. — Jim Bray, Jun 16 '21 at 19:56
I'll dive into this solution as it may be handy for me too. Thanks! — marcin2x4, Nov 09 '22 at 16:30

score 1 · Accepted Answer · answered Jan 29 '18 at 16:42

1

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.

answered Jan 29 '18 at 16:42

Son

1,835
2
18
28

would you share more about how did you find out? – georgexsh Jan 29 '18 at 18:58
1

@georgexsh I have tried to use `AssertionPool` to debug but haven't seen anything suspicious ... And then somehow I remember there is a process forked from the main one at some point and after that the culprit is found :). – Son Feb 19 '18 at 09:24

How to properly close mysql connections in sqlalchemy?

2 Answers2