Database Testing with pytest

3:15pm - 3:45pm on Friday, October 5 in Madison

Dan Clark

Audience Level:


Testing relational database assests such as stored procedures, functions, and views can be awkward. However, Python can come to the rescue with pytest. We’ll dive into an example or two so that you too can leverage Python to test your own obtuse database structures.


Options for testing relational databases aren’t as renown as what’s available for application testing. We’ve all heard of unittest and pytest, but testing database objects are sometimes forgotten about, or tested through the application. However, pytest’s flexibility along with Python’s rich library of modules means that we can accomplish database testing in a way familar to Python developers.

We’ll be exploring how to use PyTest to create a suite of tests for database objects. This will include setting up our testing environment, populating with our fixtures, and using transactions to our advantage. As we’ll be testing against a real live Microsoft SQL Server database, we’ll see how to use pyodbc to set up a connection to it. Then we’ll dive into a few tests to show how we can gain confidence in our DB functions and stored procedures. Of course there are alternative ways to do this, and I’ll share a few of them along with the benefits and drawbacks of using pytest.

If time allows we’ll show an Postgres equivalent to illustrate that this approach is not limited to a single product.

Want to edit this page?