alembic_with_data_manip.py

from alembic import op
import sqlalchemy as sa

def upgrade():
    t_files = sa.table('files', sa.column('id', sa.String(32)), sa.column('content', sa.Blob()))
    # we have to use get_bind() because op.execute() doesn't have a return value
    # but by using get_bind() we can no longer generate SQL scripts for the migration; we have to run it live
    connection = op.get_bind()
    my_id = '14c3e928ed9d4ecaa57226bc5b628132'
    data = connection.execute(sa.select([t_files.c.content]).where(t_files.c.id == my_id)).scalar()
    new_data = transform_data(data)
    connection.execute(t_files.update().where(t_files.c.id == my_id).values(content=new_data))

def downgrade():
    pass

I noticed that you’re using sa.table but I assume it’s either sa.Table or using import sqlalchemy.sql. What’s the advantage of one over the other? Since the auto-generated migration uses the capitalized Column etc, perhaps I ought to stick with that?

You also mentioned that MySQL doesn’t implement transactions for ALTER TABLE. If I use sa.execute() then is that wrapped into a transaction (and Alembic commits the transaction upon successful upgrade() or downgrade()?), or is that execute issued immediately?

no, it's not. it's sqlalchemy.table, in lowercase. (in older sqlalchemy versions, you had to use it as sqlalchemy.sql.table, but 1.x made it simpler)

you should use the lowercase table and column for lightweight table constructs like this.

Alembic runs the migrations inside a transaction. However, MySQL implicitly instantly commits the transaction whenever you use ALTER TABLE.

I need to update my Alchemy then, yes.

And after the instant commit, will there be a new transaction or instant commits from thereon?

I’ve finally updated the SO answer here.