I am working on testing my flask app model. I'm using mysql 5.7, sqlalchemy and pytest.

Within my model, I have a CRUD mixin that I used to manage creating, updating and deleting. Whenever I try to access the object in the Mixin before returning the object to the test function, SQLAlchemy hangs at db.drop_all in my tear down. When I look in mysql at PROCESSLIST, it shows 1 sleep query and 1 query waiting for table metadata lock.

I can fix this by calling db.session.commit in the create method in the mixin before returning the object. However, if I call it at the test teardown (or in the main test function), it doesn't work. I'd prefer not to add an extra commit just to make my tests work as it doesn't feel correct. Does anyone know why this is happening or have any suggested fixes?

models.py

class CRUDMixin(object):

    @classmethod
    def create(cls, **kwargs):
        instance = cls(**kwargs)
        saved_instance = instance.save()
        # do stuff with saved_instance (i.e. add to full text search engine)
        # db.drop_all in teardown works if add db.session.commit() here
        return saved_instance

    def save(self, commit=True):
        db.session.add(self)
        if commit:
            try:
                db.session.commit()
            except Exception:
                db.session.rollback()
                raise
        return self

class User(CRUDMixin, db.model):

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50))

conftest.py

@pytest.fixture(scope='session')
def app():
    app = create_app(TestConfig)
    ctx = app.app_context()
    ctx.push()
    yield app
    ctx.pop()

@pytest.fixture(scope='session')
def test_db(app):
    db.drop_all()
    db.create_all()
    # add test db information
    yield db
    db.session.remove()
    db.drop_all()  # test hangs within drop all

@pytest.fixture(scope='function')
def db_session(test_db):
    connection = db.engine.connect()
    transaction = connection.begin()
    options = dict(bind=connection, binds={})
    session = db.create_scoped_session(options)
    db.session = session
    yield db
    db.session.remove() # tables won't drop if I put db.session.commit before the remove call
    transaction.rollback()
    connection.close() # even though connection closes, mysql still shows process

test_models.py

class TestUser(object):
 
    def test_add_new(self, db_session):
        u = User.create(name='test_name')
        assert u.name == 'test_name'
        # if I put db.session.commit() here, tables won't drop