Put Checkout Behind One Command

The sample catalog begins with item 1 available and Riley below a limit of three. One complete command should produce this durable row:

checkout_item(connection, 9001, 1, 17, '2026-08-26') -> 9001
Fresh connection loan 9001: (9001, 1, 17, '2026-08-26', NULL)

Open catalog_db.py. The new checkout_item(connection, loan_id, item_id, member_id, checked_out_on) already has the exact four-placeholder insert and return value. Bring the two checkout decisions you already built into this function.

Inside one with connection: block, first query for an active loan whose item_id matches and whose returned_on is NULL. If fetchone() finds a row, raise the established error:

raise ValueError("That item is already checked out.")

Next, select loan_limit from members for member_id. If the member exists, count only that member’s active loans. Refuse when adding one would cross the stored limit:

if active_count + 1 > member["loan_limit"]:
    raise ValueError("That member has reached the loan limit.")

This comparison still gives a 2.5 limit room for two whole active loans, but not a third. If the member is missing, do not invent a limit error. Let the bound insert reach the foreign-key constraint.

Execute the supplied insert inside the same connection context. Its four values must remain a separate tuple, including SQL-looking date text. Let normal context exit commit, then return the explicit loan_id. Do not close the connection.

The function therefore owns one checkout outcome, while its caller still owns the live connection and may issue another command.

Press Run. Alongside the successful call above, a separate database always tries checkout_item(connection, 501, 4, 17, "2026-08-26"). Loan ID 501 already exists, so the call should display a raw IntegrityError. A fresh connection should find no loan for item 4, proving that rollback happened before control returned.

Submit also exercises both exact refusal messages, returned history, a 2.5 limit, missing parents, a duplicate loan ID, fresh-connection visibility, and continued use of the supplied connection.