Dropping Columns in Alembic SQLite Migration

Written by Johannes on December 6, 2013 Categories: Alembic, Python, SQL, SQLAlchemy, Sqlite

Your ads will be inserted here by

Easy AdSense.

Please go to the plugin admin page to
Paste your ad code OR
Suppress this ad slot.

SQLite has no native support for the ALTER and DROP statement on columns (http://sqlite.org/lang_altertable.html). Migration tools, such as django south, therefore have to provide a number workaround for this lack of functionality for full support. Alembic, the migration tool for SQLAlchemy unfortunately does not provide those workarounds, as apparently Mike Bayer wants to keep the interface lean (an ambition for which I think he should be lauded).

Nevertheless in my daily developers practice I need ALTER and DROP column support in migrations. Therefore I decided to hack up DROP COLUMN support myself and stick it in a little repository (https://github.com/klugjohannes/alembic-sqlite). This is still rough around the edges and very limited, but check it out for yourself and feel free to add improvements.

Tests will hopefully be supplied some time, as soon as I figure out a good way to write tests for alembic migrations.

If you’re just looking for snippets, here’s the code. :)

def drop_column_sqlite(tablename, columns):
    """ column dropping functionality for SQLite """
 
    # we need copy to make a deep copy of the column attributes
    from copy import copy
 
    # get the db engine and reflect database tables
    engine = op_.get_bind()
    meta = sa.MetaData(bind=engine)
    meta.reflect()
 
    # create a select statement from the old table
    old_table = meta.tables[tablename]
    select = sa.sql.select([c for c in old_table.c if c.name not in columns])
 
    # get remaining columns without table attribute attached
    remaining_columns = [copy(c) for c in old_table.columns
            if c.name not in columns]
    for column in remaining_columns:
        column.table = None
 
    # create a temporary new table
    new_tablename = '{0}_new'.format(tablename)
    op_.create_table(new_tablename, *remaining_columns)
    meta.reflect()
    new_table = meta.tables[new_tablename]
 
    # copy data from old table
    insert = sa.sql.insert(new_table).from_select(
            [c.name for c in remaining_columns], select)
    engine.execute(insert)
 
    # drop the old table and rename the new table to take the old tables
    # position
    op_.drop_table(tablename)
    op_.rename_table(new_tablename, tablename)
No Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre user="" computer="" escaped="">