How to disable MySQL query cache when using SQLAlchemy?

Question

I work with a fairly large MySQL database through the SQLAlchemy library, and I would like to disable MySQL query caching in order to debug session-based performance issues. It is difficult to debug slow queries when their repetition leads to much faster execution. With the MySQL CLI client, I can do SET SESSION query_cache_type = OFF; to achieve the result that I am looking for and I would like to run it in every SQLAlchemy session (when I am debugging).

But I can't figure out how to configure SQLAlchemy so that it starts SET SESSION query_cache_type = OFF when it starts a new database session.

I looked through the “Engine and Connection Documentation” but it seems that it can’t find anything.

Is there something obvious that I'm missing, or is there a better way to do this?

Answer 1

Use event hook immediately after defining your engine:

 from sqlalchemy import event def disable_query_cache(conn, record): conn.cursor().execute("SET SESSION query_cache_type = OFF") # this is probably in your Pyramid setup code engine = create_engine(...) if DEBUGGING: event.listen(engine, 'connect', disable_query_cache) 

You can do this globally by adding a hook to the Pool class, but (a) you probably want the Pyramid settings to be available anyway, so you can decide whether to add or not a hook, and (b) a global state )