Sitting around the campfire
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!
Selected tracks from my debut album, Listen E.P., are now available for your enjoyment on streaming platforms worldwide! It's Technological / The Dream can be found on Spotify, Apple Music, and many others! Give em a listen today!
Do you have an HSTORE column on your PostgreSQL database that you don't want to be `null` but need to have a default value? The syntax for this is a little irregular; so I'm posting it here for my own reference and yours:
my_column HSTORE DEFAULT '' NOT NULL
is the line in your `CREATE TABLE`command that you want.
Additionally, if you want your SQLAlchemy model object to initialize this column with said empty dictionary (instead of `None`), per this StackOverflow post you need take a couple of extra steps in your model:
from sqlalchemy.dialects.postgresql import HSTORE
from sqlalchemy.ext.mutable import MutableDict
class Item(db.Model):
my_column = db.Column(MutableDict.as_mutable(HSTORE), nullable=False, default={}, server_default='')
def __init__(self, **kwargs):
kwargs.setdefault('my_column', {})
super(Item, self).__init__(**kwargs)
Girl with a Pearl Earring by Johannes Vermeer (Mauritshuis)
Every so often I find myself looking for art for some reason or another -- maybe a blog post, or referencing something I saw in a museum, or maybe just for plain enjoyment -- and oftentimes it can be found in an open image collection! I've kept a small working list of these places in my head over the years, and I realized it might be useful to write them down and figure out what else is out there. As it turns out, there is a lot!
This is a collection of high-resolution, open-access, free-download, free-to-use image libraries, focusing on the visual arts like painting or photography. There are other styles of art (like sculptures, audio, or archaeological artifacts) in some of these links but that is not the focus. Most of these links are free to use in any application -- commercial or non-commercial -- but not all. Please check the license before using anything you find here commercially. The images in these links should be a mixture of public domain and Creative Commons-licensed content.
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.