“set session” in a SQLAlchemy session object

I’m using SQLAlchemy for a project, and need to be able to specify a session variable/setting for one specific call for performance reasons:

set session max_heap_table_size = 1024 * 1024 * 64;

I can of course do this in MySQL directly (on the shell), but how do I set this session variable in a SQLAlchemy session?

Answer

Use a session event to execute an arbitrary SQL statement on each new transaction. You can also use events on the connection level, it depends on your use case.

Here is how I would do it on the session level:

Session = sessionmaker()
@event.listens_for(Session, 'before_flush')
def set_max_heap_table_size(session, transaction, connection):
    session.execute('SET max_heap_table_size = 1024 * 1024 * 64')

If you are unsure which way works for you, just try them, write some test cases and find out if that works for you.

There may be one caveat (unsure): Since the connection is not dropped but returned to the pool, the setting might persist. In this case you might also want to attach something to restore the default, e.g. on the after_flush event. I am not entirely sure on this one, you might want to experiment. If this is unnecessary, you could also use the after_begin event, but there is no real before_close event that wraps it, so it could create issues.