How to turn off MySQL query cache while using SQLAlchemy?

Question

I am working with a fairly large MySQL database via the SQLAlchemy library, and I'd love to turn off MySQL's query caching to debug performance issues on a per-session basis. It's difficult to debug slow queries when repeating them results in much faster execution. With the CLI MySQL client, I can execute SET SESSION query_cache_type = OFF; to achieve the result I'm looking for, and I would like to run this on every SQLAlchemy session (when I'm debugging).

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

I've looked at the Engine and Connection docs, but can't seem to find anything.

Is there something obvious that I'm missing, or a better way of doing this?

Answer 1

Use an event hook immediately after you define 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 the hook to the Pool class itself, but (a) you probably want the Pyramid settings available anyway so you can decide whether or not to add the hook, and (b) global state is bad :)