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.

Answer 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
   

Answer 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.