Recognize a Constraint Failure

The failing checkout_pair call stopped on its second insert. Its two views did not agree:

Acting connection pending new loan IDs: [7101]
Fresh connection new loan IDs: []
Transaction still open: yes

The first row is pending on the connection that attempted it. It was never committed, so a fresh connection cannot see it. The failed second statement did not decide what should happen to that pending first row.

SQLite reports a broken primary-key or foreign-key rule with sqlite3.IntegrityError. A short observing caller could identify that exact case like this:

try:
    checkout_pair(connection, first_loan, missing_member_loan)
except sqlite3.IntegrityError:
    print("The database rejected a catalog rule conflict.")

The exception tells the caller why the statement stopped. Catching or naming it does not itself commit or undo earlier work. The fixed demonstration rolls back only after printing the pending state so its temporary database can be cleaned up. Your function does not have that failure behavior yet.

The exception type also gives us a useful boundary. A missing table raises OperationalError. Passing an unusable argument can raise TypeError. Those failures do not mean “a known catalog constraint rejected this row,” so a handler for this operation should not catch Exception or every sqlite3.Error.

Now you can give that expected exception one explicit outcome: undo the pending part of the pair and return False.