Different behaviour of Flask-SQLAlchemy transaction rollback using session.add or session.execute

Question

I'm trying to apply the recipe described here: http://alexmic.net/flask-sqlalchemy-pytest/ to use pytest to test an application using Flask-SQLAlchemy.

In the section Writing tests, it is stated:

Notice that we are free to commit the session as we would do normally. This is achieved because the session “joins” the external transaction created by the connection we explicitly created in the session fixture, hence only the outermost BEGIN/COMMIT pair has any effect.

But this does not work for me when if I use the session.add method in my test. It works however when I'm using session.execute instead.

Here is an example which shows the issue:

database.py

from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()

models.py

from database import db


class Employee(db.Model):
    __tablename__ = "employee"
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String, nullable=False)

test_employee.py

from models import Employee


def test_employee(session):
    my_employee = Employee()
    my_employee.name = "Toto"
    session.add(my_employee)
    # session.execute("INSERT INTO employee (id, name) VALUES (1, 'Tata');")
    session.commit()

conftest.py (Slightly adapted from the example)

import os

import pytest
from flask import Flask

from database import db as _db

TESTDB = 'test_utte.db'
TESTDB_PATH = "/tmp/{}".format(TESTDB)
TEST_DATABASE_URI = 'sqlite:///' + TESTDB_PATH


@pytest.fixture(scope='session')
def app(request):
    """Session-wide test `Flask` application."""
    settings_override = {
        'TESTING': True,
        'SQLALCHEMY_DATABASE_URI': TEST_DATABASE_URI,
        'SQLALCHEMY_TRACK_MODIFICATIONS': False  # Disabled to remove warning
    }
    app = Flask(__name__)
    app.config.update(settings_override)

    # Establish an application context before running the tests.
    ctx = app.app_context()
    ctx.push()

    def teardown():
        ctx.pop()

    request.addfinalizer(teardown)
    return app


@pytest.fixture(scope='session')
def db(app, request):
    """Session-wide test database."""
    if os.path.exists(TESTDB_PATH):
        os.unlink(TESTDB_PATH)

    def teardown():
        pass  # Commented to access database after test is run
        # _db.drop_all()
        # os.unlink(TESTDB_PATH)

    _db.init_app(app)
    _db.create_all()

    request.addfinalizer(teardown)
    return _db


@pytest.fixture(scope='function')
def session(db, request):
    """Creates a new database session for a test."""
    db.engine.echo = True
    connection = db.engine.connect()
    transaction = connection.begin()

    options = dict(bind=connection, binds={})
    session = db.create_scoped_session(options=options)
    db.session = session

    def teardown():
        transaction.rollback()
        connection.close()
        session.remove()

    request.addfinalizer(teardown)
    return session

Running pytest -s gives the following logs:

test_employee.py 2018-07-10 13:22:48,645 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2018-07-10 13:22:48,648 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2018-07-10 13:22:48,650 INFO sqlalchemy.engine.base.Engine INSERT INTO employee (name) VALUES (?)
2018-07-10 13:22:48,650 INFO sqlalchemy.engine.base.Engine ('Toto',)
2018-07-10 13:22:48,651 INFO sqlalchemy.engine.base.Engine COMMIT
.2018-07-10 13:22:48,657 INFO sqlalchemy.engine.base.Engine ROLLBACK

And if I check in the database, the entry is here

sqlite> select * from employee;
1|Toto

Instead, if I replace in test_employee.py with the session.execute part:

from models import Employee


def test_employee(session):
    # my_employee = Employee()
    # my_employee.name = "Toto"
    # session.add(my_employee)
    session.execute("INSERT INTO employee (id, name) VALUES (1, 'Tata');")
    session.commit()

I get the following logs:

test_employee.py 2018-07-10 13:28:27,093 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2018-07-10 13:28:27,095 INFO sqlalchemy.engine.base.Engine INSERT INTO employee (id, name) VALUES (1, 'Tata');
2018-07-10 13:28:27,096 INFO sqlalchemy.engine.base.Engine ()
.2018-07-10 13:28:27,098 INFO sqlalchemy.engine.base.Engine ROLLBACK

and the entry is not in the database:

sqlite> select * from employee;
sqlite>

and finally if I put both in the test:

from models import Employee

def test_employee(session):
    my_employee = Employee()
    my_employee.name = "Toto"
    session.add(my_employee)
    session.execute("INSERT INTO employee (id, name) VALUES (1, 'Tata');")
    session.commit()

then I have something similar to when there is only the session.execute

test_employee.py 2018-07-10 13:31:57,179 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2018-07-10 13:31:57,180 INFO sqlalchemy.engine.base.Engine INSERT INTO employee (id, name) VALUES (1, 'Tata');
2018-07-10 13:31:57,181 INFO sqlalchemy.engine.base.Engine ()
2018-07-10 13:31:57,182 INFO sqlalchemy.engine.base.Engine INSERT INTO employee (name) VALUES (?)
2018-07-10 13:31:57,182 INFO sqlalchemy.engine.base.Engine ('Toto',)
.2018-07-10 13:31:57,183 INFO sqlalchemy.engine.base.Engine ROLLBACK

and the table remains empty:

sqlite> select * from employee;
sqlite>

My guess is this has something to do with the inner BEGIN/COMMIT that shows up in the log in my first example, but I don't really understand why it's here or why it overrides the outermost BEGIN/ROLLBACK ?

jeancochrane jeancochrane · Accepted Answer · 2018-07-20T01:53:20

This is a pretty thorny issue, and I wonder if it has to do with the implementation of session.execute in sqlite. I've never seen anything like it working in Postgres.

While it doesn't get to the root of your problem, I'm wondering if using pytest-flask-sqlalchemy-transactions will solve your problem. The plugin is designed to address this exact use case, and exposes a db_session fixture that does what you're looking for.

You'll need to set up a _db fixture in order to give the plugin access to your database:

@pytest.fixture
def _db(db):
    return db

Check the docs for installation instructions and let me know if it works for you. It hasn't been extensively tested with sqlite so at the very least it'll help figure out if this is a sqlite-specific problem.

Different behaviour of Flask-SQLAlchemy transaction rollback using session.add or session.execute

2 Answers