* All statements start a transaction automatically if one isn't already open.
* You need to call .commit() or .rollback() on the connection object to do the commit or rollback.
* The driver can optionally support an "autocommit mode" where, instead of the above rules, every individual statement is wrapped in its own transaction and committed immediately.
That's the theory. Many database drivers, including the built-in SQLite driver, differ in practice (for example, it doesn't automatically start a transaction for a SELECT statement).
The biggest common practical difference is an implementation detail of so-called "autocommit mode". Often that is NOT implemented by actually issuing a "BEGIN TRANSACTION" before and "COMMIT" after every statement. Instead, it often just issues the statement without any automatic transaction statements whatsoever, relying on the fact that the underlying database engine will immediately commit any statements outside an explicit transaction (i.e. has its own autocommit mode).
That means if you do want complete manual control of transactions in a Python DB-API driver (including sqlite), then - confusingly - you need to set the autocommit option to true!
It's not clear to me whether/how this applies to pg8000 driver in the OP. There is a section on autocommit but it doesn't explicitly say what it does.