Wiki

Clone wiki

sqlalchemy / UsageRecipes / Explain

Explain

This sample illustrates how to implement Postgresql's EXPLAIN and EXPLAIN ANALYZE. Additional syntaxes for MySQL, Oracle, etc. can be added easily:

from sqlalchemy import *
from sqlalchemy.ext.compiler import compiles
from sqlalchemy.sql.expression import Executable, ClauseElement, _literal_as_text

class explain(Executable, ClauseElement):
    def __init__(self, stmt, analyze=False):
        self.statement = _literal_as_text(stmt)
        self.analyze = analyze
        # helps with INSERT statements
        self.inline = getattr(stmt, 'inline', None)

@compiles(explain, 'postgresql')
def pg_explain(element, compiler, **kw):
    text = "EXPLAIN "
    if element.analyze:
        text += "ANALYZE "
    text += compiler.process(element.statement, **kw)
    return text

if __name__ == '__main__':
    from sqlalchemy.ext.declarative import declarative_base
    from sqlalchemy.orm import sessionmaker, aliased

    Base = declarative_base()

    class Foo(Base):
        __tablename__ = 'foo'
        id = Column(Integer, primary_key=True)

    engine = create_engine('postgresql://scott:tiger@localhost/test', echo=True)

    Base.metadata.create_all(engine)

    sess = sessionmaker(engine)()

    falias = aliased(Foo)
    q = sess.query(Foo).filter(Foo.id > 5).join((falias, Foo.id==falias.id))

    print sess.execute(explain(q)).fetchall()

    print sess.execute(explain("SELECT * FROM foo")).fetchall()

Updated