Let the Connection Choose the Outcome

The manual version has two branches and two explicit transaction decisions. A SQLite connection can make those same decisions from how a with block ends.

The shape matters. Put the connection context inside the try, and keep the expected handler outside it:

try:
    with connection:
        connection.execute(INSERT_LOAN, first_loan)
        connection.execute(INSERT_LOAN, second_loan)
except sqlite3.IntegrityError:
    return False
return True

Now trace the two ways that block can end. The normal path reaches True only after the context commits. On the IntegrityError path, the context rolls back before the outer handler returns False:

When execution reaches the end of with connection: normally, the connection commits the pending work. When an exception leaves the block, it rolls back before the exception continues outward. The outer handler then recognizes sqlite3.IntegrityError and returns False.

Remove the manual commit() and rollback() calls as you make this refactor. Keep both inserts inside one context. A context around each insert would create two outcomes instead of one pair.

The handler must not move inside with connection:. If it caught the integrity error there and returned normally, the context would see a normal exit. It could commit the first valid insert, recreating the partial result you saw at the start.

Press Run. The public output remains the same as your manual solution: valid IDs reopen as [7001, 7002], while the missing-member attempt returns False and leaves no proposed ID on either connection. This time, the connection reached those outcomes from the context exit.

Submit also sends an unrelated error through the function and requires it to escape. Catch only the expected integrity case. It checks state through a fresh connection and runs another query through the original one.

This with connection: block is a transaction context. Unlike a file context, it does not mean “close this object when the block ends.” It decides the pending transaction’s outcome. Before adding more operations, let us make that lifetime distinction visible.