Alembic migration that moves data from one column to another table.

farmos_aggregator_farm_info_migration.py

"""Move farm info to separate table.

Revision ID: cbc5e5092520
Revises: d2422b5a6859
Create Date: 2020-06-02 23:36:50.018420

"""
from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import postgresql
from sqlalchemy.sql import table
from sqlalchemy.dialects.postgresql import JSONB

# revision identifiers, used by Alembic.
revision = 'cbc5e5092520'
down_revision = 'd2422b5a6859'
branch_labels = None
depends_on = None


def upgrade():
    new_table = op.create_table('farminfo',
    sa.Column('farm_id', sa.Integer(), nullable=False),
    sa.Column('info', postgresql.JSONB(astext_type=sa.Text()), nullable=True),
    sa.ForeignKeyConstraint(['farm_id'], ['farm.id'], ),
    sa.PrimaryKeyConstraint('farm_id')
    )
    op.create_index(op.f('ix_farminfo_farm_id'), 'farminfo', ['farm_id'], unique=False)

    # Migrate existing farm info to the new farminfo table.
    # Request all of the old info.
    conn = op.get_bind()
    res = conn.execute("select id, info from farm")
    results = res.fetchall()

    # Prepare an old_info object to insert into the new farminfo table.
    old_info = [{'farm_id': r[0], 'info': r[1]} for r in results]

    # Insert old_info into new farminfo table.
    op.bulk_insert(new_table, old_info)

    op.drop_column('farm', 'info')
    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column('farm', sa.Column('info', postgresql.JSONB(astext_type=sa.Text()), autoincrement=False, nullable=True))
    op.drop_index(op.f('ix_farminfo_farm_id'), table_name='farminfo')
    op.drop_table('farminfo')
    # ### end Alembic commands ###

In newer version of SQLAlchemy, line 33 needs to be changed to:

res = conn.execute(sa.text("select id, info from farm"))

ref: https://stackoverflow.com/a/69491015/1342618