Is there a python-alembic way to convert data between dropping and adding a column?

Question

I have a sqlite3 database accessing it with SQLAlchemy in python3. I want to add a new and drop an old column with the database-migation tool alembic. Simple example:

class Model(_Base):
  __tablename__ = 'Model'
  _oid = Column('oid', sa.Integer, primary_key=True)
  _number_int = sa.Column('number_int', sa.Integer)

Should be after migration like this:

class Model(_Base):
  __tablename__ = 'Model'
  _oid = Column('oid', sa.Integer, primary_key=True)
  _number_str = sa.Column('number_str', sa.String(length=30))

The relevant point here is that there is data in _number_int that should be converted into _number_str like this:

number_conv = {1: 'one', 2: 'two', 3: 'three'}
_number_str = number_conv[_number_int]

Is there an alembic way to take care of that? It means if alembic itself take care of cases like that in its concept/design? I want to know If I can use alembic tools for that or if I have to do my own extra code for that.

Of course the original data is a little bit more complex to convert. This is just an example here.

Like this https://julo.ch/blog/migrating-content-with-alembic/ ? — Yaroslav Admin, Aug 14 '15 at 13:01
Thanks. This is the way I would have done this, too. But it is not an *alembic way* in the meaning of fitting to the alembic concept. It might be that the alembic concept/design doesn't take care of cases like that? — buhtz, Aug 14 '15 at 19:28
Alembic has [method](https://alembic.readthedocs.org/en/latest/ops.html?highlight=bulk_insert#alembic.operations.Operations.bulk_insert) to insert data. But it doesn't have anything built-in for data updates. So I think do it yourself is the right way to go. — Yaroslav Admin, Aug 14 '15 at 20:54

score 4 · Accepted Answer · answered Aug 16 '15 at 21:06

Here is alembic operation reference. There is a method called bulk_insert() for bulk inserting content, but nothing for migrating existing content. It seems alembic doesn't have it built-in. But you can implement data migration yourself.

One possible approach is described in the article "Migrating content with alembic". You need to define intermediate table inside your migration file, which contains both columns (number_int and number_str):

import sqlalchemy as sa

model_helper = sa.Table(
    'Model',
    sa.MetaData(),
    sa.Column('oid', sa.Integer, primary_key=True),
    sa.Column('number_int', sa.Integer),
    sa.Column('number_str', sa.String(length=30)),
)

And use this intermediate table to migrate data from old column to the new one:

from alembic import op


def upgrade():
    # add the new column first
    op.add_column(
        'Model',
        sa.Column(
            'number_str',
            sa.String(length=30),
            nullable=True
        )
    )

    # build a quick link for the current connection of alembic
    connection = op.get_bind()

    # at this state right now, the old column is not deleted and the
    # new columns are present already. So now is the time to run the
    # content migration. We use the connection to grab all data from
    # the table, convert each number and update the row, which is 
    # identified by its id
    number_conv = {1: 'one', 2: 'two', 3: 'three'}
    for item in connection.execute(model_helper.select()):
        connection.execute(
            model_helper.update().where(
                model_helper.c.id == item.id
            ).values(
                number_str=number_conv[item.number_int]
            )
        )

    # now that all data is migrated we can just drop the old column
    # without having lost any data
    op.drop_column('Model', 'number_int')

This approach is a bit noisy (you need to define table manually), but it works.

Is there a python-alembic way to convert data between dropping and adding a column?

1 Answers1