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

[Chris Angelico]

On Fri, Jun 2, 2017 at 5:18 PM, Frank Millman <frank at chagford.com> wrote:
> As I said, I cannot prove this, but the theory fits the observed behaviour
> perfectly, so I have proceeded on the assumption that it is true. Therefore
> I now always run every SQL command or block of commands within a context
> manager, which always calls conn.commit() or conn.rollback() on exit, and I
> have not had any more problems. I use exactly the same code for sqlite3 and
> for Sql Server/pyodbc, and it has not caused any problems there either.

+1.

A bit more info: When you perform read-only queries against a
PostgreSQL database, you still have transactional integrity, just as
you would with mutating transactions. Two SELECT statements in the
same transaction will see a consistent view of the underlying
database. To accomplish this, the database creates low-grade locks, so
it knows which things you're using. (It's not quite that simple, since
Postgres uses MVCC, but broadly speaking it's so.) Thus transactions
are just as important for SELECT statements as they are for INSERT or
UPDATE... or, for that matter, ALTER TABLE (this is a point on which
not all DBMSes agree - transactional DDL is one of the features I love
about Postgres). Always using a context manager is good practice and
great for code clarity. I would be inclined to mandate it in a style
guide, if I were in charge of any good-sized psycopg2-based project.

ChrisA