How can I use SQLAlchemy to do "mysql explain"

Question

I have a sql like:

DBSession().query(Model).filter(***)

and I want to explain this sql using SQLAlchemy.

Answer 1

You want to compile your SQLAlchemy query into a string; use the correct dialect and use literal values for bind parameters

query = DBSession().query(Model).filter(***)
# you should have an engine reference used to create the DBSession object
sql = query.statement.compile(engine, compile_kwargs={"literal_binds": True})

You can then use that to ask for a MySQL explanation:

DBSession().execute(f'EXPLAIN {sql}')

Answer 2

You can prepare your explain sql string like this:

'EXPLAIN' + query.compile(
    compile_kwargs={"literal_binds": True},
    dialect=mysql.dialect()
)

Advantage is query has parameters filled in.