Test a Query by Its Public Result

list_available_items(connection) promises a useful Python result, not a particular spelling of its SQL. Test that promise by arranging rows whose ID order disagrees with their asset-tag order, then comparing the complete returned value.

Continue in tests/test_catalog.py. The first schema test and create_fresh_database are already there. Add the body of test_available_items_are_plain_ordered_records(tmp_path). Its database path is create_fresh_database(tmp_path, "available.db"), and its configured connection comes from catalog_db.open_database(database_path).

Insert three unfamiliar items with separate connection.execute(...) calls or a small loop that repeats that call. Give the lowest ID a late-sorting tag such as Z-310, give a higher ID an early-sorting tag such as A-205, and make the third item actively loaned. This arrangement prevents an accidental ID or insertion order from looking correct. Add the required member and active loan, commit the arrangement, and call exactly:

actual = catalog_db.list_available_items(connection)

Close the connection in finally. Compare actual with two dictionaries in asset-tag order. Each dictionary must contain exactly id, asset_tag, name, category, and loan_days. Also check that each result has plain dict type rather than sqlite3.Row. These assertions protect the boundary another Python module consumes while allowing the implementation to change its private organization.

Do not copy the availability query into the test or inspect a SQL constant. That would test your duplicate query, not the public function. The setup SQL in the test serves a different purpose: it creates the known state that the public call must interpret.

Press Run. The fixed entry again calls pytest.main(["-q", "-p", "no:cacheprovider", "tests/test_catalog.py"]), now collecting both tests:

Database tests passed: 2

Submit changes the program’s ordering while keeping collection healthy. A test that compares only len(actual), inserts rows already sorted, accepts missing keys, or leaves sqlite3.Row values intact will miss the visible regression. A complete public-result comparison catches it without requiring one exact SQL string.