pythonsqlitedatabase-migrationalembicvacuum

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


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).


Solution

  • 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.