I recently ran into the following error when adding some indices to a PostgreSQL database from within an Alembic migration:

CREATE INDEX CONCURRENTLY cannot run inside a transaction block

Which makes sense, particularly if you're creating the table with the index in the same migration (transaction block) -- how can `CREATE INDEX` know anything about a table that doesn't exist yet?

There are a variety of SQL schema transforms (a.k.a. DDL statements) that work this way. Alembic migrations are always run inside a transaction, and breaking out a given statement is not immediately obvious. Luckily, Alembic provides a way for us to end the transaction early and execute statements on their own ("autocommit"):

def upgrade():
    with op.get_context().autocommit_block():
        op.execute("ALTER TYPE mood ADD VALUE 'soso'")

It's important to note that any statements before the autocommit block will be committed! So it is probably best to do this last, at the end of your migration, in order to keep things simple.

And while this blog post speaks of PostgreSQL, this should apply to any database that SQLAlchemy supports!

More info: Alembic Documentation
H/T Stack Overflow

If you're experiencing a problem with Serato DJ, where MP3s purchased on Beatport cannot seem to save key, BPM, or track analyses and overviews, then perhaps a tool that I have written might help!

Check out MP3TagRebuilder, a simple Python script I wrote to address this issue with my own DJ library!

This tool addresses an issue I've been encountering somewhat frequently over the last few years, where my Beatport music purchases have a weird glitch in Serato where overviews and tag data won't save, even after using the "Analyze" feature. The only solution I have found, even after writing Serato support, is to rebuild the MP3 files' ID3 tags destructively.

However, every program that I know of that does this ends up dropping important tags, such as Album Art, because none of them provide a direct pathway to simply destroying the ID3 tags and then rebuilding them with a new datastructure; most of them only seem to support converting from ID3v2 to ID3v1 and back again. So I wrote my own!

If you are encountering this issue and are feeling bold enough to test my code on your own library (MAKE SURE YOU HAVE A BACKUP AND TEST IT!!!!), head on over to Github and check it out!

You will need a working Python environment and must be comfortable with a command prompt. Instructions for running this tool are included in README.md, and instructions for installing Python can be found here.

Get it from Github here: https://github.com/lyjia/mp3-tag-rebuilder