SQLAlchemy session voes in unittest

Question

I've just started using SQLAlchemy a few days ago and right now I'm stuck with a problem that I hope anyone can shed some light on before I loose all my hair.

When I run a unittest, see snippet below, only the first test in the sequence is passing. The test testPhysicalPrint works just fine, but testRecordingItem fails with NoResultFound exception - No row was found for one(). But if I remove testPhysicalPrint from the test class, then testRecordingItem works.

I assume that the problem has something to do with the session, but I can't really get a grip of it.

In case anyone wonders, the setup is as follows:

• Python 3.1 (Ubuntu 10.04 package)
• SQLAlchemy 0.7.2 (easy_install:ed)
• PostgreSQL 8.4.8 (Ubuntu 10.04 package)
• PsycoPG2 2.4.2 (easy_installed:ed)

Exemple test:

class TestSchema(unittest.TestCase):

    test_items = [
        # Some parent class products
        PrintItem(key='p1', title='Possession', dimension='30x24'),
        PrintItem(key='p2', title='Andrzej Żuławski - a director', dimension='22x14'),
        DigitalItem(key='d1', title='Every Man His Own University', url='http://www.gutenberg.org/files/36955/36955-h/36955-h.htm'),
        DigitalItem(key='d2', title='City Ballads', url='http://www.gutenberg.org/files/36954/36954-h/36954-h.htm'),

    ]

    def testPrintItem(self):
        item = self.session.query(PrintItem).filter(PrintItem.key == 'p1').one()
        assert item.title == 'Possession', 'Title mismatch'

    def testDigitalItem(self):
        item2 = self.session.query(DigitalItem).filter(DigitalItem.key == 'd2').one()
        assert item2.title == 'City Ballads', 'Title mismatch'

    def setUp(self):
        Base.metadata.create_all()      
        self.session = DBSession()
        self.session.add_all(self.test_items)
        self.session.commit()

    def tearDown(self):
        self.session.close()
        Base.metadata.drop_all()

if __name__ == '__main__':
    unittest.main()

UPDATE

Here is the working code snippet.

# -*- coding: utf-8 -*-

import time
import unittest
from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import *

Base = declarative_base()
engine = create_engine('sqlite:///testdb', echo=False)
DBSession = sessionmaker(bind=engine)

class ItemMixin(object):
    """ 
    Commons attributes for items, ie books, DVD:s...    
    """
    __tablename__ = 'testitems'
    __table_args__ = {'extend_existing':True}

    id = Column(Integer, autoincrement=True, primary_key=True)
    key = Column(Unicode(16), unique=True, nullable=False)
    title = Column(UnicodeText, default=None)
    item_type = Column(Unicode(20), default=None)

    __mapper_args__ = {'polymorphic_on': item_type}

    def __init__(self, key, title=None):
        self.key = key
        self.title = title

class FooItem(Base, ItemMixin):
    foo = Column(UnicodeText, default=None)
    __mapper_args__ = {'polymorphic_identity':'foo'}

    def __init__(self, foo=None, **kwargs):
        ItemMixin.__init__(self, **kwargs)
        self.foo = foo

class BarItem(Base, ItemMixin):
    bar = Column(UnicodeText, default=None)
    __mapper_args__ = {'polymorphic_identity':'bar'}

    def __init__(self, bar=None, **kwargs):
        ItemMixin.__init__(self, **kwargs)
        self.bar = bar

# Tests
class TestSchema(unittest.TestCase):

    # Class variables
    is_setup = False
    session = None
    metadata = None

    test_items = [
        FooItem(key='f1', title='Possession', foo='Hello'),
        FooItem(key='f2', title='Andrzej Żuławsk', foo='World'),
        BarItem(key='b1', title='Wikipedia', bar='World'),
        BarItem(key='b2', title='City Ballads', bar='Hello'),
    ]

    def testFooItem(self):
        print ('Test Foo Item')
        item = self.__class__.session.query(FooItem).filter(FooItem.key == 'f1').first()
        assert item.title == 'Possession', 'Title mismatch'

    def testBarItem(self):
        print ('Test Bar Item')
        item = self.__class__.session.query(BarItem).filter(BarItem.key == 'b2').first()
        assert item.title == 'City Ballads', 'Title mismatch'

    def setUp(self):
        if not self.__class__.is_setup:
            self.__class__.session = DBSession()
            self.metadata = Base.metadata
            self.metadata.bind = engine
            self.metadata.drop_all()                # Drop table        
            self.metadata.create_all()              # Create tables
            self.__class__.session.add_all(self.test_items)   # Add data
            self.__class__.session.commit()                   # Commit
            self.__class__.is_setup = True

    def tearDown(self):
        if self.__class__.is_setup:
            self.__class__.session.close()

    # Just for Python >=2.7 or >=3.2 
    @classmethod
    def setUpClass(cls):
        pass

    #Just for Python >=2.7 or >=3.2 
    @classmethod
    def tearDownClass(cls):
        pass

if __name__ == '__main__':
    unittest.main()

Answer 1

I have this as my tearDown method and it does work fine for my tests:

def tearDown (self):
    """Cleans up after each test case."""
    sqlalchemy.orm.clear_mappers()

Answer 2

The most likely reason for this behavior is the fact that that data is not properly cleaned up between the tests. This explains why when you run only one test, it works.
setUp is called before every test, and tearDown - after.
Depending on what you would like to achieve, you have two options:

1. create data only once for all test.
   In this case you if you had Python-2.7+ or Python-3.2+, you could use tearDownClass method. In your case you can handle it with a boolean class variable to prevent the code you have in setUp running more then once.
2. re-create data before every test
   In this case you need to make sure that in the tearDown you delete all the data. This is what you are not doing right now, and I suspect that when the second test is ran, the call to one() fails not because it does not find an object, but because it finds more two objects matching the criteria.

---

Check the output of this code to understand the call sequence:

import unittest
class TestSchema(unittest.TestCase):
    def testOne(self):
        print '==testOne'
    def testTwo(self):
        print '==testTwo'
    def setUp(self):
        print '>>setUp'
    def tearDown(self):
        print '<<tearDown'
    @classmethod
    def setUpClass():
        print '>>setUpClass'
    @classmethod
    def tearDownClass():
        print '<<tearDownClass'
if __name__ == '__main__':
    unittest.main()

Output:

>>setUp
==testOne
<<tearDown
>>setUp
==testTwo
<<tearDown