Database Testing with pytest

2faaf4dfb77c2b0e17bf1504887e512f?s=47 Dan Clark
October 05, 2018

Database Testing with pytest

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.


Dan Clark

October 05, 2018


  1. Database Testing with Dan Clark • Code can be found

    at • Companion Article is at
  2. Who? What? Where?

  3. Unit Testing “Explosion” by Denis De Mesmaeker is licensed under

    CC BY 2.0
  4. Tables, Views, Stored Procedures, and Functions. Oh My! “Immersed in

    Numbers” by Chris Khamken is licensed under CC BY 2.0
  5. None
  6. A few caveats We’re starting with an empty database Build

    it up, tear it down! Database transactions for the win!
  7. Fixtures

  8. Tests

  9. Some helpful pytest options • -s: sets the capture method

    to "no". • -m: runs tests that are marked with a certain string. • --pdb: runs the debugger when an error is encountered • --lf: runs only the tests that failed on the last attempt. • -v: make pytest verbose
  10. Setting up our fixtures

  11. A word about scope “Scope” by Tim Lieu is licensed

    under CC BY 2.0

  13. Setting up our fixtures

  14. Let’s make a test • Keep it simple Socrates •

    Make sure that tests can connect to the database. • Confirms pytests is working.
  15. What are we testing

  16. What are we testing

  17. Let’s make better tests

  18. run_sproc???

  19. Let’s try it

  20. Let’s try it

  21. Let’s try it

  22. Benefits and Drawbacks • Can be migrated to another product

    • Fixtures are reusable • Tests and fixtures can be grouped together • We can leverage Python’s vast module ecosystem • Using assert instead of specialized functions • Command line based. However Pycharm to the rescue • Code coverage is useless • No AssertEqualsTable (tSQLt)
  23. • Code can be found at • Companion Article

    is at Twitter: @danclark Thanks!!!!!!!!!!!