How use pytest to unit test sqlalchemy orm classes

I usually do that this way:

1. I do not instantiate engine and session with the model declarations, instead I only declare a Base with no bind:

   Base = declarative_base()

   and I only create a session when needed with

   engine = create_engine('<the db url>')db_session = sessionmaker(bind=engine)

   You can do the same by not using the intern_session in your add_book method but rather use a session parameter.

   def add_book(self, session, title):    b = Book(Title=title, AuthorId=self.AuthorId)    session.add(b)    session.commit()

   It makes your code more testable since you can now pass the session of your choice when you call the method.And you are no more stuck with a session bound to a hardcoded database url.

2. Add a custom --dburl option to pytest using its pytest_addoption hook.

   Simply add this to your top-level conftest.py:

   def pytest_addoption(parser):    parser.addoption('--dburl',                     action='store',                     default='<if needed, whatever your want>',                     help='url of the database to use for tests')

   Now you can run pytest --dburl <url of the test database>

3. Then you can retrieve the dburl option from the request fixture

   • From a custom fixture:

     @pytest.fixture()def db_url(request):    return request.config.getoption("--dburl")    # ...

   • Inside a test:

     def test_something(request):    db_url = request.config.getoption("--dburl")    # ...

At this point you are able to:

• get the test db_url in any test or fixture
• use it to create an engine
• create a session bound to the engine
• pass the session to a tested method

It is quite a mess to do this in every test, so you can make a usefull usage of pytest fixtures to ease the process.

Below are some fixtures I use:

from sqlalchemy import create_enginefrom sqlalchemy.orm import scoped_session, sessionmaker@pytest.fixture(scope='session')def db_engine(request):    """yields a SQLAlchemy engine which is suppressed after the test session"""    db_url = request.config.getoption("--dburl")    engine_ = create_engine(db_url, echo=True)    yield engine_    engine_.dispose()@pytest.fixture(scope='session')def db_session_factory(db_engine):    """returns a SQLAlchemy scoped session factory"""    return scoped_session(sessionmaker(bind=db_engine))@pytest.fixture(scope='function')def db_session(db_session_factory):    """yields a SQLAlchemy connection which is rollbacked after the test"""    session_ = db_session_factory()    yield session_    session_.rollback()    session_.close()

Using the db_session fixture you can get a fresh and clean db_session for each single test.When the test ends, the db_session is rollbacked, keeping the database clean.