How to VACUUM a SQLite database from within an Alembic migration?

Question

I am using SqlAlchemy Alembic to perform DB migrations on a SQLite database. One of my migrations removes many redundant records and I would like to VACUUM the database after the deletion.

Here's how I'm trying to do this in my migration's upgrade() method:

def upgrade():

    # deleting records here using op.execute()...

    op.execute("VACUUM")

When the migration runs it fails with this error message:

E       sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) cannot VACUUM from within a transaction
E       [SQL: VACUUM]
E       (Background on this error at: https://sqlalche.me/e/14/e3q8)```

The link only provides a rather general description of what an OperationalError is.

How can I overcome this error and VACUUM my database from within my migration?

Is there a way to exclude this specific command or this specific migration from running in a transaction?

PS - In general I would like my migrations to run in transactions so I would prefer not to change Alembic's default behavior (as set in env.py).

Answer 1

I was able to successfully execute the VACUUM command in my migration by wrapping it like so:

    with op.get_context().autocommit_block():
        op.execute("VACUUM")

This did not require any changes to env.py.