How to count sqlalchemy queries in unit tests

Question

In Django I often assert the number of queries that should be made so that unit tests catch new N+1 query problems

from django import db from django.conf import settings settings.DEBUG=True  class SendData(TestCase):     def test_send(self):         db.connection.queries = []         event = Events.objects.all()[1:]         s = str(event) # QuerySet is lazy, force retrieval         self.assertEquals(len(db.connection.queries), 2) 

In in SQLAlchemy tracing to STDOUT is enabled by setting the echo flag on engine

engine.echo=True 

What is the best way to write tests that count the number of queries made by SQLAlchemy?

class SendData(TestCase):     def test_send(self):         event = session.query(Events).first()         s = str(event)         self.assertEquals( ... , 2) 

Answer 1

I've created a context manager class for this purpose:

class DBStatementCounter(object):     """     Use as a context manager to count the number of execute()'s performed     against the given sqlalchemy connection.      Usage:         with DBStatementCounter(conn) as ctr:             conn.execute("SELECT 1")             conn.execute("SELECT 1")         assert ctr.get_count() == 2     """     def __init__(self, conn):         self.conn = conn         self.count = 0         # Will have to rely on this since sqlalchemy 0.8 does not support         # removing event listeners         self.do_count = False         sqlalchemy.event.listen(conn, 'after_execute', self.callback)      def __enter__(self):         self.do_count = True         return self      def __exit__(self, *_):         self.do_count = False      def get_count(self):         return self.count      def callback(self, *_):         if self.do_count:             self.count += 1