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

[Chris Angelico]

On Sat, Jun 3, 2017 at 2:45 AM, Jon Ribbens <jon+usenet at unequivocal.eu> wrote:
> On 2017-06-02, Dennis Lee Bieber <wlfraed at ix.netcom.com> wrote:
>>       Connector/Python (MySQL) [guess it is time for me to finally upgrade to
>> Python 3.x -- it was the delay in getting mysqldb ported that held me back]
>> does allow for turning on autocommit -- which is documented as issuing an
>> implicit commit after each SQL (which I take to mean each .execute() ), and
>> would likely cause problems with explicit BEGIN. Also not recommended for
>> InnoDB tables, but considered appropriate for MyISAM tables [no transaction
>> feature on those].
>
> Bewaare - MyISAM tables have no transactions for DML but they do have
> transactions for DDL. Insane but true.

Not insane; not all DBMSes have transactional DDL, and of the major
ones, several have only more recently added it (despite having had
rock-solid transactional DML for decades). It's an excellent feature
but not easy to implement. Personally, I like it enough that I choose
a DBMS based on features like that, but there are plenty of people who
aren't too bothered by it.

That said, though, MySQL is AFAIK the only full-scale DBMS that
doesn't support DDL rollback in 2017. I don't know whether you can
craft a transaction that mixes DDL and DML in all of them, but
certainly in most; and it's a great feature, because you can version
your schema trivially (putting a version number in a metadata table).
I love it. :)

ChrisA