Give Every Test a Fresh Database

A database test becomes hard to trust when yesterday’s rows can leak into today’s result. Your first job is to give one test its own catalog.db, build the current schema there, close it, and reopen it before making the final assertion.

The project tree has four roles. Edit only tests/test_catalog.py. catalog_setup.py prepares and checks schemas, catalog_db.py contains the reviewed query and command functions, and catalog_import.py contains the reviewed CSV import. Leave those three program modules unchanged. The visible run_tests.py file is the project entry that runs your test file.

Pytest supplies a fresh tmp_path argument for this test. A path such as tmp_path / "catalog.db" belongs only to that test run, so another test cannot inherit its database. Complete create_fresh_database(tmp_path, filename="catalog.db"): derive the path from tmp_path, call catalog_setup.prepare_database(database_path), and return that same path.

Then complete test_fresh_database_has_the_current_schema(tmp_path). Use create_fresh_database, open the resulting file with sqlite3.connect, and read the user-table names plus the ordered columns of all three tables. The exact version 2 shape is:

• items: id, asset_tag, name, category, loan_days

• members: id, member_code, name, loan_limit, email

• loans: id, item_id, member_id, checked_out_on, returned_on

Close that connection in finally. Open the same path again, read PRAGMA user_version, and close again. Comparing the complete shape catches a database that is partly current. Reopening matters too: it proves you are reading the file, not state that exists only on one connection.

Press Run. run_tests.py changes to the project directory and calls exactly pytest.main(["-q", "-p", "no:cacheprovider", "tests/test_catalog.py"]). A failure shows pytest’s useful report. Success is summarized as:

Database tests passed: 1

Use Run for this chapter’s pytest tests. The separate Run project tests toolbar action uses Python Land’s small test runner, which does not supply pytest fixtures such as tmp_path.

Submit also runs your test with a fresh temporary path and checks that it notices a setup function that writes somewhere else. A checked-in database, an in-memory substitute, or an assertion that checks only one table cannot provide that evidence. Once this passes, every later test will follow the same isolation rule.