How do I properly set up a single SQLAlchemy session for each unit test?

Question

When testing my Pyramid application using WebTest, I have not been able to create/use a separate Session in my tests without getting warnings about a scoped session already being present.

Here is the main() function of the Pyramid application, which is where the database is configured.

# __init__.py of Pyramid application

from pyramid_sqlalchemy import init_sqlalchemy
from sqlalchemy import create_engine


def main(global_config, **settings):
    ...
    db_url = 'some-url'
    engine = create_engine(db_url)
    init_sqlalchemy(engine)  # Warning thrown here.

Here is the test code.

# test.py (Functional tests)

import transaction
from unittest import TestCase
from pyramid.paster import get_appsettings
from pyramid_sqlalchemy import init_sqlalchemy, Session
from sqlalchemy import create_engine
from webtest import TestApp

from app import main
from app.models.users import User


class BaseTestCase(TestCase):
    def base_set_up(self):
        # Create app using WebTest
        settings = get_appsettings('test.ini', name='main')
        app = main({}, **settings)
        self.test_app = TestApp(app)

        # Create session for tests.
        db_url = 'same-url-as-above'
        engine = create_engine(db_url)
        init_sqlalchemy(engine)
        # Note: I've tried both using pyramid_sqlalchemy approach here and 
        # creating a "plain, old" SQLAlchemy session here using sessionmaker.

    def base_tear_down(self):
        Session.remove()


class MyTests(BaseTestCase):
    def setUp(self):
        self.base_set_up()

        with transaction.manager:
            self.user = User('user@email.com', 'John', 'Smith')
            Session.add(self.user)
            Session.flush()

            Session.expunge_all()
        ...

    def tearDown(self):
        self.base_tear_down()

    def test_1(self):
        # This is a typical workflow on my tests.
        response = self.test_app.patch_json('/users/{0}'.format(self.user.id), {'email': 'new.email@email.com')
        self.assertEqual(response.status_code, 200)

        user = Session.query(User).filter_by(id=self.user.id).first()
        self.assertEqual(user.email, 'new.email@email.com')
    ...
    def test_8(self):
        ...

Running the tests gives me 8 passed, 7 warnings, where every test except the first one gives the following warning:

From Pyramid application: __init__.py -> main -> init_sqlalchemy(engine): sqlalchemy.exc.SAWarning: At least one scoped session is already present. configure() can not affect sessions that have already been created.

If this is of any use, I believe I am seeing the same issue here, except I am using pyramid_sqlalchemy rather than creating my own DBSession.

https://github.com/Pylons/webtest/issues/5

Can you give us the code of one of your test where the warning occurs — bboumend, Aug 27 '18 at 14:06
Updated the post to include more information that I've since found. — latetojoin, Aug 27 '18 at 17:31
I cant reproduce the warning with the code you have linked, you are clearly making a call to Session.configure() twice in a row but those call does not appear on what you showed us and there is no such call in init_sqlalchemy, i also does not understand why you call your main to setup an engine and a session then setup a second engine to the same db and a session in your base setup since your just overriding the first one, maybe try to remove the session configuration in your main or just dont call it in your test. — bboumend, Aug 28 '18 at 08:38
@bboumend Thanks for your effort on this. I believe to reproduce this problem you would've needed to use the session in the application as well as the tests. I had originally wanted to set up separate sessions to test for actual changes to the database, as the state within the session can be different than what's actually committed in some (unexpected) cases. However, I took your advice and am reusing the same session, and using `Session.expire_all()` to clear the state as needed. — latetojoin, Aug 30 '18 at 18:56

score 1 · Accepted Answer · answered Aug 30 '18 at 18:52

Answering my own question: I'm not sure if this is the best approach, but one that worked for me.

Instead of trying to create a separate session within my tests, I am instead using the pyramid_sqlalchemy Session factory, which is configured in the application. As far as I can tell, calls to Session in both test and application code return the same registered scoped_session.

My original intent with creating a separate session for my tests was to confirm that records were being written to the database, and not just updated in the active SQLAlchemy session. With this new approach, I've managed to avoid these "caching" issues by issuing Session.expire_all() at points in the tests where I transition between test transactions and application transactions.

# test.py (Functional tests)

import transaction
from unittest import TestCase
from pyramid.paster import get_appsettings
from pyramid_sqlalchemy import Session
from webtest import TestApp

from app import main
from app.models.users import User


class BaseTestCase(TestCase):
    def base_set_up(self):
        # Create app using WebTest
        settings = get_appsettings('test.ini', name='main')
        app = main({}, **settings)
        self.test_app = TestApp(app)

        # Don't set up an additional session in the tests. Instead import
        # and use pyramid_sqlalchemy.Session, which is set up in the application.

    def base_tear_down(self):
        Session.remove()


class MyTests(BaseTestCase):
    def setUp(self):
        self.base_set_up()

        with transaction.manager:
            self.user = User('user@email.com', 'John', 'Smith')
            Session.add(self.user)
            Session.flush()

            Session.expunge_all()
            Session.expire_all()  # "Reset" your session.

    def tearDown(self):
        self.base_tear_down()

How do I properly set up a single SQLAlchemy session for each unit test?

1 Answers1