Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Python: py.test fixture for SQLAlchemy test in a transaction, create tables only once!
from sqlalchemy import create_engine
from sqlalchemy.orm import Session
from myapp.models import BaseModel
import pytest
@pytest.fixture(scope="session")
def engine():
return create_engine("postgresql://localhost/test_database")
@pytest.fixture(scope="session")
def tables(engine):
BaseModel.metadata.create_all(engine)
yield
BaseModel.metadata.drop_all(engine)
@pytest.fixture
def dbsession(engine, tables):
"""Returns an sqlalchemy session, and after the test tears down everything properly."""
connection = engine.connect()
# begin the nested transaction
transaction = connection.begin()
# use the connection with the already started transaction
session = Session(bind=connection)
yield session
session.close()
# roll back the broader transaction
transaction.rollback()
# put back the connection to the connection pool
connection.close()
@omrihar

This comment has been minimized.

Copy link

@omrihar omrihar commented May 20, 2019

Nice snippet, just notice you're missing a closing quote on line 9...

@lorey

This comment has been minimized.

Copy link

@lorey lorey commented Mar 12, 2020

Had the problem that commit would actually persist data between tests but your solution fixed that as expected. Thanks for sharing.

@KarlDavidson

This comment has been minimized.

Copy link

@KarlDavidson KarlDavidson commented Oct 20, 2020

Can I ask what your basemodel is here? Is it an ORM model?

@kissgyorgy

This comment has been minimized.

Copy link
Owner Author

@kissgyorgy kissgyorgy commented Oct 20, 2020

Can I ask what your basemodel is here? Is it an ORM model?

Yes, an ORM model, where SQLAlchemy fills metadata. To be more specific, a subclass of sqlalchemy.ext.declarative.declarative_base: https://docs.sqlalchemy.org/en/13/orm/extensions/declarative/basic_use.html

@KarlDavidson

This comment has been minimized.

Copy link

@KarlDavidson KarlDavidson commented Oct 20, 2020

Can I ask what your basemodel is here? Is it an ORM model?

Yes, an ORM model, where SQLAlchemy fills metadata. To be more specific, a subclass of sqlalchemy.ext.declarative.declarative_base: https://docs.sqlalchemy.org/en/13/orm/extensions/declarative/basic_use.html

Thanks! I have my ORM model, but when I attempt to run the pytest-flask-alchemy plugin, it requires a _db fixture. How was it that you worked around this?

@kissgyorgy

This comment has been minimized.

Copy link
Owner Author

@kissgyorgy kissgyorgy commented Oct 20, 2020

...when I attempt to run the pytest-flask-alchemy plugin...

This snippet has nothing to do with Flask at all.
Flask-SQLAlchemy works it's own way, I think it monkeypatches the ORM, I don't know, I did not looked into how that works.

@treehouse-networks-uk

This comment has been minimized.

Copy link

@treehouse-networks-uk treehouse-networks-uk commented Mar 10, 2021

Hey, I've been using this and it's pretty darned slick.
However, I have hit a bit of a snag when testing for duplicates and scope. With the dbsession scope using the default of "function" any thing I add gets rolled back. So I change the scope to session, my duplicate test works. The test uses "pytest.raises(sqlalchemy.exc.IntegrityError)" ). However the next test fails as the session needs rolling back.
Any ideas of a good way to get around this. Something simple like setting the scope to module and just putting one duplicate test at the end of the module. Or could I try a try/except block in the fixture, or a "sub" fixture. Feel like I'm turning an elegant piece of code in to a dropped lasagne.

Cheers,

not to worry I just added a new fixture with a "function" scope that uses the sqlalchemy session.begin_nested() and rolls back after the yield. This works with the " with.pytest.raises(blah blah):"

@martinburchell

This comment has been minimized.

Copy link

@martinburchell martinburchell commented Apr 13, 2021

@kissgyorgy thanks for this. We'd like to use a modified version in https://github.com/RudolfCardinal/camcops which is GPLV3.
Do your gists have a license?

@kissgyorgy

This comment has been minimized.

Copy link
Owner Author

@kissgyorgy kissgyorgy commented Apr 13, 2021

No, feel free to use it.

@glaucouri

This comment has been minimized.

Copy link

@glaucouri glaucouri commented Apr 20, 2021

I suggest a more robust approach at tables function

@pytest.fixture(scope="session")
def tables(engine):
    BaseModel.metadata.create_all(engine)
    try:
        yield
    finally:
        BaseModel.metadata.drop_all(engine)
@kissgyorgy

This comment has been minimized.

Copy link
Owner Author

@kissgyorgy kissgyorgy commented Apr 20, 2021

I suggest a more robust approach at tables function

You don't need to do that. Pytest is doing it for you: https://docs.pytest.org/en/stable/fixture.html#yield-fixtures-recommended

@glaucouri

This comment has been minimized.

Copy link

@glaucouri glaucouri commented Apr 20, 2021

you are right, thank's for point out

@nzewail

This comment has been minimized.

Copy link

@nzewail nzewail commented Jun 9, 2021

Am I the only one who finds this taking a while to drop the tables at the end? When I remove that step it seems to run quite quickly but for some reason dropping the tables at the end of the session is taking a while?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment