Handling transactions in Python DBI module

[dieter]

Chris Angelico <rosuav at gmail.com> writes:
> ...
> When I advise my students on basic databasing concepts, I recommend
> this structure:
>
> conn = psycopg2.connect(...)
>
> with conn, conn.cursor() as cur:
>     cur.execute(...)
>
> The transaction block should always start at the 'with' block and end
> when it exits. As long as you never nest them (including calling other
> database-using functions from inside that block), it's easy to reason
> about the database units of work - they always correspond perfectly to
> the code blocks.

In my context (web applications), I strongly discourage this use - at least
when "conn" does not handle subtransactions properly.

In a web application, the main transaction should in general be controlled
at the request level: a request should either succeed as a whole or have no
side effects whatsoever. This prohibits local (somewhere
deep in a component) transaction control.