Writting py test for sqlalchemy app

Question

I am trying convert unit test into py test. I am using the unit test example

class TestCase(unittest.TestCase):
    def setUp(self):
        app.config['TESTING'] = True
        app.config['CSRF_ENABLED'] = False
        app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///' + os.path.join(basedir, 
        'test.db')
        db.create_all()

    def tearDown(self):
        db.session.remove()
        db.drop_all()

I am not sure, What should be its py test version.

Solution 1

I searched high and low for a well explained solution to use SqlAlchemy without Flask-SQLAlchemy and run tests with Pytest, so here's how i have achieved this:

1. Set up your engine & Session objects as per the docs. (I have opted for sessionmaker as i want to check in my app if the session is still available in the Flask's request thread pool, see: https://dev.to/nestedsoftware/flask-and-sqlalchemy-without-the-flask-sqlalchemy-extension-3cf8

2. Import your Base object from wherever you've created it in your app. This will create all the tables in your database defined by the engine.

3. Now we want to Yield a Session back to your unit tests. The idea is to setup before calling Yield & teardown after. Now, in your test you can create a table and populate it with some rows of data etc.

4. Now we must close the Session, this is important!

5. Now by calling Base.metadata.drop_all(bind=engine) we drop all the tables in the database ( we can define a table(s) to drop if required, default is: tables=None)

    engine = create_engine(create_db_connection_str(config), echo=True)
    Session = scoped_session(sessionmaker(bind=engine))
   
    @pytest.fixture(scope="function") # or "module" (to teardown at a module level)
    def db_session():
        Base.metadata.create_all(engine)
        session = Session()
        yield session
        session.close()
        Base.metadata.drop_all(bind=engine)
   

6. Now we can pass the function scoped fixture to each unit test:

    class TestNotebookManager:
        """
            Using book1.mon for this test suite
        """
        book_name = "book1"
   
        def test_load(self, client: FlaskClient, db_session) -> None:
            notebook = Notebook(name=self.book_name)
            db_session.add(book)
            db_session.commit()
            rv = client.get(f"/api/v1/manager/load?name={self.name}")
            assert "200" in rv.status
   

Solution 2

First off, py.test should just run the existing unittest test case. However the native thing to do in py.test is use a fixture for the setup and teardown:

import pytest

@pytest.fixture
def some_db(request):
    app.config['TESTING'] = True
    app.config['CSRF_ENABLED'] = False
    app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///' + os.path.join(basedir, 'test.db')
    db.create_all()
    def fin():
        db.session.remove()
        db.drop_all()
    request.addfinalizer(fin)

def test_foo(some_db):
    pass

Note that I have no idea about SQLAlchemy and whether there are better ways of handling it's setup and teardown. All this example demonstrates is how to turn the setup/teardown methods into a fixture.

Comments

[user3526896]

I am getting AttributeError: 'function' object has no attribute 'config' at app.config['TESTING'] = True. It worked if I use unittest instead of py.test

[user3526896]

I already know what you explained above. My problem is pytest is not recognizing config, so how can I give db url?

[merwok]

The error message means that “app” is a function, while you seem to expect it’s something else (an instance of the Flask class I guess). What is app? Why isn’t it what you expect?

[Antoine Viscardi]

would it make sense to have yield Session() instead?

[Joe Gasewicz]

@AntoineViscardi thanks for the correction, in fact it would make more sense, as scoped_session returns a factory object that I haven't instanciated

[ppak10]

With this fixture having the scope of function, it seems that it would create_all and drop_all for each unit test. So for an example where multiple user features are tested in their own unit test functions, (i.e. test_follow_user, or test_report_user) would it need to also need to take the steps necessary to re-populate the users into the database during each unit test as this db_session fixture is only function scope? Would it be better to set the db_session to a module or class scope for this so that it doesn't have to recreate the database for each unit test?

[Joe Gasewicz]

@ppak10 A great question but I think that is a political decision outside the scope of this technical solution. I have updated the answer with your scope options, thank you!

[tupui]

A similar option is to separate the table creation from the session as shown here: gist.github.com/kissgyorgy/e2365f25a213de44b9a2.