Skip to content

Instantly share code, notes, and snippets.

@kissgyorgy
Last active June 26, 2024 20:01
Show Gist options
  • Save kissgyorgy/e2365f25a213de44b9a2 to your computer and use it in GitHub Desktop.
Save kissgyorgy/e2365f25a213de44b9a2 to your computer and use it in GitHub Desktop.
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()
@kissgyorgy
Copy link
Author

@pxu-mdsol

"nothing will be saved in the database after the test case, how about during the test case?"
is NO. Even during the test case, changes never commit to database. They all stay in transaction, and finally canceled by your line 32.

That is False. I'm not familiar with database implementation specifics, but I'm pretty sure you can find files on disk related to transactions for certain databases and engines. A concrete example is SQLite in WAL mode. Also as I mentioned, you can see the data committed during test based on isolation levels, so the data is definitely saved in the database somewhere.

I think the more precise way to put it is that "during test case, data will be saved in the database in a transaction and after the test case, everything will be deleted by rolling back the transaction."

@pxu-mdsol
Copy link

@kissgyorgy I agree you -- transaction is saved in the database. Thanks for the discussion!

@bashtian-fr
Copy link

its been a long time since this gist was posted, but I am having really inconsistent behaviors where one time it will succeed my tests, then another run it will complain that there is no such table. Like the create_all and drop_all weren't called or that the table already exists, Did anyone experience this behavior? I only have 3 tests in a class.

@kissgyorgy
Copy link
Author

The database and table creation fixtures are session scoped, so they are only created at the start of the first test which uses it and destroyed at the end of the test suite.
These SQLAlchemy APIs didn't change.
The only problem I can guess is that the test run was abrupted and the database tables were not deleted. You can try starting the tables fixture with drop_all() , so the tables are deleted BEFORE every test run.

@bashtian-fr
Copy link

Thanks for the answer, I tried that too but that would not help.
Altho I found the problematic part but I don't know how to solve it yet. The issue is that I have a lot of Core CPUs and I had pytest --numprocesses/-n on auto. My assumption is that 2 (or more) of the tests from the same class - which are using these db fixtures - were running on different processes which would create new test session. Setting it to 1 (or not adding it) solved the problem.

@kissgyorgy
Copy link
Author

With pytest-xdist, you should make a different test database for every process.

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