Unit tests for query in SQLAlchemy

Question

How to do tests in SQLAlchemy? For example, suppose we have this models.py

 from sqlalchemy import ( Column, Integer, String, ) from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() class Panel(Base): __tablename__ = 'Panels' id = Column(Integer, primary_key=True) category = Column(Integer, nullable=False) platform = Column(String, nullable=False) region = Column(String, nullable=False) def __init__(self, category, platform, region): self.category = category self.platform = platform self.region = region def __repr__(self): return ( "<Panel('{self.category}', '{self.platform}', " "'{self.region}')>".format(self=self) ) 

and this is tests.py

 import unittest from sqlalchemy import create_engine from sqlalchemy.orm import sessionmaker from models import Base, Panel class TestQuery(unittest.TestCase): engine = create_engine('sqlite:///:memory:') Session = sessionmaker(bind=engine) session = Session() def setUp(self): Base.metadata.create_all(self.engine) self.session.add(Panel(1, 'ion torrent', 'start')) self.session.commit() def tearDown(self): Base.metadata.drop_all(self.engine) def test_query_panel(self): expected = [Panel(1, 'ion torrent', 'start')] result = self.session.query(Panel).all() self.assertEqual(result, expected) 

When we try to run the test, it fails, although the two panels look the same.

 $ nosetests F ====================================================================== FAIL: test_query_panel (tests.TestQuery) ---------------------------------------------------------------------- Traceback (most recent call last): File "/Users/clasher/tmp/tests.py", line 31, in test_query_panel self.assertEqual(result, expected) AssertionError: Lists differ: [<Panel('1', 'ion torrent', 's... != [<Panel('1', 'ion torrent', 's... First differing element 0: <Panel('1', 'ion torrent', 'start')> <Panel('1', 'ion torrent', 'start')> [<Panel('1', 'ion torrent', 'start')>, <Panel('2', 'ion torrent', 'end')>] ---------------------------------------------------------------------- Ran 1 test in 0.063s FAILED (failures=1) 

One of the solutions I found is to make a request for each individual instance that I expect to find in the request:

 class TestQuery(unittest.TestCase): ... def test_query_panel(self): expected = [ (1, 'ion torrent', 'start'), (2, 'ion torrent', 'end') ] successful = True # Check to make sure every expected item is in the query try: for category, platform, region in expected: self.session.query(Panel).filter_by( category=category, platform=platform, region=region).one() except (NoResultFound, MultipleResultsFound): successful = False self.assertTrue(successful) # Check to make sure no unexpected items are in the query self.assertEqual(self.session.query(Panel).count(), len(expected)) 

This seems pretty ugly to me, but I didn't even get to the point that I have a complex filtered query that I am trying to verify. Is there a more elegant solution, or do I always have to manually create a bunch of separate requests?

Answer 1

your original test is on the right track, you just need to do one of two things: either make sure that two Panel objects with the same primary key identifier are compared as True :

 class Panel(Base): # ... def __eq__(self, other): return isinstance(other, Panel) and other.id == self.id 

or you can organize your test in such a way as to make sure that you are checking the same Panel instance (because here we use an identity card ):

 class TestQuery(unittest.TestCase): def setUp(self): self.engine = create_engine('sqlite:///:memory:') self.session = Session(engine) Base.metadata.create_all(self.engine) self.panel = Panel(1, 'ion torrent', 'start') self.session.add(self.panel) self.session.commit() def tearDown(self): Base.metadata.drop_all(self.engine) def test_query_panel(self): expected = [self.panel] result = self.session.query(Panel).all() self.assertEqual(result, expected) 

as for the installation / detachment of the engine / session, I would go for a template in which you use one engine for all tests, and provided that your circuit is correct, the only circuit for all tests, then you will make sure that the data with which you work, are executed in a transaction that can be canceled. Session can be made to work in such a way that the commit() call does not actually commit a “real” transaction, wrapping the entire test in an explicit Transaction . An example at https://docs.sqlalchemy.org/en/latest/orm/session_transaction.html#joining-a-session-into-an-external-transaction-such-as-for-test-suites illustrates this use. The presence of the ": memory:" engine on each test instrument will take up a lot of memory and will not scale in databases other than SQLite.