Setting up transactional tests with Pytest and SQLAlchemy

Coming from a Ruby on Rails background, I appreciate solutions that become community standards of solving problems. One example is DatabaseCleaner, which ensures that your software projects tests run in separation and there are no data leaks between them.

Recently, I was looking for a similar solution for Python, but was unable to find one. However, it’s easy to leverage what SQLAlchemy and Pytest offer to wrap tests in separate database transactions. Below you will find a take on the problem that you may find convenient to use.

Setting up a DB connection

Before we’re able to use a database with transactions in our tests, we need to set up a separate DB instance exclusively for tests. Then, from our test suite, we need to connect to the DB. Here’s an example of how to establish a connection with a MySQL database: [insert image]

If you use another database engine, head to SQLAlchemy documentation for information on how to build different connection strings.

Table creation and DB seeding

Next, we need to recreate our database structure. Let’s assume that all models in your app are declared in the models.py file.

SQLAlchemy offers methods to easily create and drop tables declared in the schema: create_all and drop_all. We will use them at the beginning of the test suite execution to ensure that all tables are in place. After a full test run, we will drop all tables so that the next execution can start with a clean slate.

If you need the database to be pre-configured with some data, you can run a method seeding the database.

Wrapping tests in transactions

As a final step, we need to establish a way to use transactions in our test suite by building a fixture that creates a new transaction for each test. You can then inject the fixture into your test cases. At the end of each test execution, all data created will be wiped out, ensuring test case separation.

Summary

It doesn’t take much time to set up working transactions with Pytest and SQLAlchemy once you know how to do it. I hope that you will find this solution convenient and easy to use, helping you to avoid software mistakes.

For future reference, below is a full code that you can reuse in your projects.

If you need any help building technology as you are scaling up, u2i.com is a tech company with over 50 developers that understand how businesses work. We have diverse skillsets: back-end, front-end, full stack, mobile, UX design, DevOps. Whether you are looking for healthcare software, education software or other solutions, we build applications with you, not just for you. Medtech, Edtech, Health tech and everything in between – we can do it all. Get in touch and tell us your story and let’s find a solution together.