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

Does [this](https://stackoverflow.com/questions/44417335/alembic-post-migration-commands) answer your question? — snakecharmerb, Jun 16 '22 at 15:46
@snakecharmerb thanks but no, as I prefer not to customize `env.py` for this. — urig, Jun 19 '22 at 18:56

score 0 · Accepted Answer · answered Jun 16 '22 at 18:41

0

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.

answered Jun 16 '22 at 18:41

urig

16,016
26
115
184

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

1 Answers1