Handling transactions in Python DBI module

[Chris Angelico]

On Thu, Feb 11, 2016 at 4:28 PM, Frank Millman <frank at chagford.com> wrote:
> "Chris Angelico"  wrote in message
> news:CAPTjJmphJvtCKUB6Qr-vp_1epEWxBgQxmfKEPMOhQp3pAPGG+A at mail.gmail.com...
>>
>>
>> When I advise my students on basic databasing concepts, I recommend
>> this structure:
>>
>> conn = psycopg2.connect(...)
>>
>> with conn, conn.cursor() as cur:
>>     cur.execute(...)
>>
>
> Does this automatically issue a 'conn.commit()' on exit?

If there was no exception raised, yes. If an exception starts to
bubble across that boundary, the transaction's rolled back instead
(and then the exception continues).

> I have found that this is important, for the following reason.
>
> If you issue a bare SELECT to PostgreSQL, it executes it without creating
> any locks. However, if it is inside a transaction, it does create a lock (I
> forget exactly which one).

That's not strictly true. A SELECT query always acquires some locks,
albeit weak ones; but if you're not currently inside a transaction,
you get an implicit transaction that gets immediately ended. So by the
time you get to the next line of Python code, the locks have been
released.

> Because psycopg2 silently executes 'BEGIN TRANSACTION', your SELECTs always
> happen inside a transaction whether you specify it or not. If you do not
> issue a conn.commit(), the locks do not get cleared.

Correct. And that's how you should _always_ work. When I started
databasing seriously, it was with DB2 on OS/2, and if you didn't
explicitly BEGIN TRANSACTION, one would be begun - and not ended. So
the model was: Do your stuff, then commit/rollback. Then do more
stuff. I never used BEGIN TRANSACTION unless I needed to add
parameters to it (picking an isolation level or something).

With Postgres, it's pretty similar, only you execute BEGIN TRANSACTION
more explicitly. But that can be buried in the interface library,
giving you a nice tidy system of "here, this is how we do some work...
and now we're done". Which I strongly recommend.

ChrisA