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

[Chris Angelico]

On Sat, Jun 3, 2017 at 7:29 AM, Dennis Lee Bieber <wlfraed at ix.netcom.com> wrote:
> On Sat, 3 Jun 2017 06:48:28 +1000, Chris Angelico <rosuav at gmail.com>
> declaimed the following:
>
>>
>>Wait, you have transactions with MyISAM now? I thought MySQL supported
>>transactions with InnoDB but not MyISAM, and the reason you didn't get
>>transactional DDL was that the system catalog tables are mandatorily
>>MyISAM, even if all your own tables are InnoDB.
>>
>
>         Not really transactions -- but locks on the "metadata" tables...
>
> http://www.chriscalender.com/tag/myisam-locks/

Oh. That's just the basic protection of "don't let anyone change the
table while we're using it". It doesn't mean you can roll back an
ALTER TABLE, much less take advantage of full transactional integrity.
In PostgreSQL, you can do something like this (pseudocode):

version = #select schema_version from metadata#
if version < 1:
    #create table foo (id serial primary key, bar text not null)#
if version < 2:
    #alter table foo add quux integer not null default 10#
if version < 3:
    #create table spam (id serial primary key, foo_id int not null
references foo)#

    #update metadata set schema_version = 3#
if version > 3:
    raise IntegrityError("Cannot backlevel database")
#commit#

Now, even if anything crashes out while you're migrating the database
(either because the power fails, or because of an error in your code,
or anything), you have an absolute guarantee that the version field
and the database will be consistent - that version 2 *always* has both
bar and quux columns, etc. There's no way to have half a schema
migration done, or finish the migration but fail to update the version
marker, or anything. You KNOW that it's safe, even against logic
errors.

That's what transactional DDL gives you.

ChrisA