How to properly close mysql connections in sqlalchemy?

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?

1 Answers

Answers 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 

If You Enjoyed This, Take 5 Seconds To Share It

• Facebook
• Twitter
• Google+
• StumbleUpon
• Digg
• Delicious
• LinkedIn
• Reddit
• Technorati