Agree. The DBAPI choice to mandate that autocommit be off by default is a strange and unfortunate choice that in my experience leads to a never ending series of "surprises" such as this one.
My conclusion is that the only sane thing to do is:
1. Only ever create connections in autocommit mode.
2. Only ever use `with connection.transaction()` to control transactions.
3. Forget that `connection.commit()` and `connection.rollback()` exist, and never use them.
I'm in favour of recommending this as strongly as reasonably possible in the documentation.
It's a shame that 1 is not the default. So you still have to remember to not forget to do this explicitly, every time you create a connection.
Dani
I was going to disagree with this, as some things don't work in an ACID way in autocommit. Then I read point 2, now I'm going to try this and see how I like transactions vs commit/rollback.
Reuben Rissler