/qmonitor/README.md

One of the new features in [SQLAlchemy] 0.9 is event removal API. Basically, if
you've added an event listener, this change makes you able to remove it.
Although, to be honest, I suspect there was a way to remove event listeners
before 0.9, but it was probably complicated or undocumented.

With this change you can now do a context manager that can watch queries
executed for an orbitrary (small) block of code without leaving any event
listeners behind or catching unrelated queries by accident.

Anyway, here's some code, and if you need a complete example, see example.py.

    :::python
    class QMonitor(object):
        def __init__(self, max_queries):
            self.queries = []
            self.max_queries = max_queries

        def assist_user(self):
            """ User requires assistance in query minimization.
            """
            qc = Counter(zip(*self.queries)[0])
            if qc.values().count(1) < len(qc):
                duplicates = [
                    '** Repeated {0} times: {1}'.format(c, q)
                    for q, c in qc.items()
                    if c > 1
                ]
                print (u'Too many queries, try eliminating duplicates:\n' +
                      u'\n'.join(duplicates))
            else:
                print u'Too many queries:\n' + u'\n'.join(zip(*self.queries)[0])

        def check(self):
            """ Check saved database queries.
            """
            if len(self.queries) > self.max_queries:
                self.assist_user()


    @contextmanager
    def qmonitor(max_queries=15):
        qm = QMonitor(max_queries)

        def before_cursor_execute(conn, cursor, statement, parameters, context, executemany):
            context._query_start_time = time.time()

        def after_cursor_execute(conn, cursor, statement, parameters, context, executemany):
            total = time.time() - context._query_start_time
            qm.queries.append((statement, total))

        event.listen(engine, 'before_cursor_execute', before_cursor_execute)
        event.listen(engine, 'after_cursor_execute', after_cursor_execute)
        yield qm
        event.remove(engine, 'before_cursor_execute', before_cursor_execute)
        event.remove(engine, 'after_cursor_execute', after_cursor_execute)


    print '== Naive query'
    q = session.query(Address)
    with qmonitor(max_queries=1) as qm:
        for address in q:
            print address.email_address + ' - ' + address.user.fullname

    qm.check()

QMonitor class will hold all related queries and, in general, will be a user
interface, since it is returned by the qmonitor context manager. The last thing
in the snippet is a basic query that will generate 4 database queries, which is
far from optimal. This is an easy case for QMonitor, since it will easily find
that the block of code generated 3 identical queries.

Such context manager can help checking performace (at least just count number
of DB queries) and assist in making the "managed" block of code as fast as
possible. I also found it to be really easy to use in integration tests for
[Tornado] without any need to modify actual handlers or monkey-patching
anything.  (It's easier to try yourself, but in case you're wondering it simply
works by wrapping `self.fetch(url)` in the context manager.)

[SQLAlchemy]: http://www.sqlalchemy.org/
[Tornado]: http://www.tornadoweb.org/