Python DB API - commit() v. execute("commit transaction")?

[Chris Angelico]

On Sat, Jun 3, 2017 at 11:46 AM, Gregory Ewing
<greg.ewing at canterbury.ac.nz> wrote:
> Chris Angelico wrote:
>>
>> with psycopg2.connect(...) as conn:
>>     with conn.trans() as trn:
>>         for row in trn.execute("select ..."):
>>             print(row)
>>
>> The outer context manager is optional, but not the inner one
>
>
> While I fully support making the use of transactions mandatory,
> I wouldn't like to be forced to use them in a with statement.
>
> In the application that I originally built my Firebird interface
> for, I had a mechanism where a user could open up a piece of
> data for editing, and then choose to save or cancel the edits.
> I implemented it by keeping a transaction around for the
> duration and then committing it or rolling it back. If a
> with statement were required around all transactions, I
> wouldn't have been able to do that.

You wouldn't be FORCED to, but it would be strongly recommended. You
could simply:

trn = conn.trans()

and then use it that way, but then you're responsible for calling
trn.commit() or trn.rollback(). You would also be responsible for the
longevity of your locks; if you hold a transaction waiting for a
human, you potentially keep some things locked for a long time. Which
is probably intentional as regards the primary record being edited,
but you'd also hold locks on anything else you touch too.

BTW, it should be possible to do:

with trn.trans() as subtrn:

on DBMSes that support subtransactions (eg PostgreSQL). For what that's worth.

ChrisA