I recently pulled together a project to demonstrate testing Python functions that interact with a Postgres database. I find that this sort of thing crops up occasionally and the project is an attempt to distil the approach. The main points include:

• Creating a fresh temporary database via testing.postgresql prior to running tests
• Setting up the temporary database via a script with required structure (schemas, tables, roles, functions etc.)
• Putting the database into a known state prior to running each individual test (populating tables with known data)
• Inspecting and making assertions about the state of the database once the function being tested has been executed
• Pausing tests using the debugger (pdb) to connect to the temporary database (using db.url() and psql <url>) to inspect it's current state while testing

View the project on GitHub: walkermatt/python-postgres-testing-demo.