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.

Transcript

1. Database Testing with Dan Clark • Code can be found at https://github.com/danclark5/pytesting_db • Companion Article is at https://bruisedthumb.com/post/2018-07-29
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
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
12. CONFTEST.PY!
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 https://github.com/danclark5/pytesting_db • Companion Article is at https://bruisedthumb.com/post/2018-07-29 Twitter: @danclark Thanks!!!!!!!!!!!