Python alembic.op.rename_table() Examples

def upgrade():
    conn = op.get_bind()

    temp_log_table = op.create_table(
        'temp_log',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('result_id', sa.Integer(), nullable=True),
        sa.Column('data', sa.LargeBinary(length=2048), nullable=True),
        sa.ForeignKeyConstraint(['result_id'], ['result.id'], ),
        sa.PrimaryKeyConstraint('id'))
    res = conn.execute('SELECT id, result_id, data FROM log')
    results = res.fetchall()
    if len(results) > 0:
        modified_logs = [{
            'id': r[0],
            'result_id': r[1],
            'data': msgpack.packb(json.loads(r[2]), use_bin_type=True)}
            for r in results]
        op.bulk_insert(temp_log_table, modified_logs)
    op.drop_table('log')
    op.rename_table('temp_log', 'log') 

def upgrade():
    op.rename_table('use_of_force_incidents', 'use_of_force_incidents_impd')
    op.execute('ALTER SEQUENCE use_of_force_incidents_id_seq RENAME TO use_of_force_incidents_impd_id_seq')
    op.execute('ALTER INDEX use_of_force_incidents_pkey RENAME TO use_of_force_incidents_impd_pkey')
    op.execute('ALTER TABLE use_of_force_incidents_impd RENAME CONSTRAINT "use_of_force_incidents_department_id_fkey" TO "use_of_force_incidents_impd_department_id_fkey"')

    op.rename_table('citizen_complaints', 'citizen_complaints_impd')
    op.execute('ALTER SEQUENCE citizen_complaints_id_seq RENAME TO citizen_complaints_impd_id_seq')
    op.execute('ALTER INDEX citizen_complaints_pkey RENAME TO citizen_complaints_impd_pkey')
    op.execute('ALTER TABLE citizen_complaints_impd RENAME CONSTRAINT "citizen_complaints_department_id_fkey" TO "citizen_complaints_impd_department_id_fkey"')

    op.rename_table('assaults_on_officers', 'assaults_on_officers_impd')
    op.execute('ALTER SEQUENCE assaults_on_officers_id_seq RENAME TO assaults_on_officers_impd_id_seq')
    op.execute('ALTER INDEX assaults_on_officers_pkey RENAME TO assaults_on_officers_impd_pkey')
    op.execute('ALTER TABLE assaults_on_officers_impd RENAME CONSTRAINT "assaults_on_officers_department_id_fkey" TO "assaults_on_officers_impd_department_id_fkey"')

    op.rename_table('officer_involved_shootings', 'officer_involved_shootings_impd')
    op.execute('ALTER SEQUENCE officer_involved_shootings_id_seq RENAME TO officer_involved_shootings_impd_id_seq')
    op.execute('ALTER INDEX officer_involved_shootings_pkey RENAME TO officer_involved_shootings_impd_pkey')
    op.execute('ALTER TABLE officer_involved_shootings_impd RENAME CONSTRAINT "officer_involved_shootings_department_id_fkey" TO "officer_involved_shootings_impd_department_id_fkey"') 

def upgrade():
    # Step 1: Schema migration
    # rename table
    op.rename_table("fetch_new_events_task", "sync_event_database_task")

    # Step 2: Data migration
    # Change task type to new sync_task_name
    engine = op.get_bind()
    meta = sa.MetaData(bind=engine)
    task = sa.Table("task", meta, autoload=True)
    stmt = (
        task.update()
        .where(task.c.type == "fetch_new_events")
        .values(type="sync_event_database")
    )
    engine.execute(stmt) 

def upgrade(active_plugins=None, options=None):
    # commands auto generated by Alembic - please adjust! #
    op.rename_table('devicetemplates', 'vnfd')
    op.rename_table('devicetemplateattributes', 'vnfd_attribute')
    op.rename_table('devices', 'vnf')
    op.rename_table('deviceattributes', 'vnf_attribute')
    migration.modify_foreign_keys_constraint_with_col_change(
        'vnfd_attribute', 'template_id', 'vnfd_id',
        sa.String(length=36))
    migration.modify_foreign_keys_constraint_with_col_change(
        'servicetypes', 'template_id', 'vnfd_id',
        sa.String(length=36))
    migration.modify_foreign_keys_constraint_with_col_change(
        'vnf', 'template_id', 'vnfd_id',
        sa.String(length=36))
    migration.modify_foreign_keys_constraint_with_col_change(
        'vnf_attribute', 'device_id', 'vnf_id',
        sa.String(length=36))
    # end Alembic commands # 

def downgrade():

	op.drop_constraint('password_reset_code_email_fkey', 'password_reset_code', type_='foreignkey')

	# Drop New Index
	op.drop_index('ix_password_reset_code_email_code', 'password_reset_code')
	op.drop_index('ix_password_reset_code_email', 'password_reset_code')

	# Rename email column
	op.alter_column('password_reset_code', 'email', new_column_name='user_id', )

	# Drop date columns
	op.alter_column('password_reset_code', 'created_at', new_column_name='created', server_default=None)
	op.drop_column('password_reset_code', 'updated_at')

	op.rename_table('password_reset_code', 'password_reset') 

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_table('skin')
    op.drop_table('modules')
    op.drop_column('node', 'mods')
    op.drop_column('node', 'parentid')
    op.drop_column('node', 'folderid')
    op.drop_column('node', 'image')
    op.drop_column('node', 'folder')
    op.drop_column('node', 'custom')
    op.drop_column('node', 'target')
    op.drop_column('node', 'type')
    op.drop_column('node', 'skin_id')
    op.drop_column('node', 'modules_id')
    op.rename_table('node', 'page')
    op.execute('ALTER SEQUENCE node_id_seq RENAME TO page_id_seq')
    # ### end Alembic commands ### 

def downgrade():
    conn = op.get_bind()

    temp_log_table = op.create_table(
        'temp_log',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('result_id', sa.Integer(), nullable=True),
        sa.Column('data', sa.String(length=1024), nullable=True),
        sa.ForeignKeyConstraint(['result_id'], ['result.id'], ),
        sa.PrimaryKeyConstraint('id'))
    res = conn.execute('SELECT id, result_id, data FROM log')
    results = res.fetchall()
    if len(results) > 0:
        modified_logs = [{
            'id': r[0],
            'result_id': r[1],
            'data': json.dumps(msgpack.unpackb(r[2], raw=False))}
            for r in results]
        op.bulk_insert(temp_log_table, modified_logs)
    op.drop_table('log')
    op.rename_table('temp_log', 'log') 

def downgrade():
    op.rename_table('use_of_force_incidents_impd', 'use_of_force_incidents')
    op.execute('ALTER SEQUENCE use_of_force_incidents_impd_id_seq RENAME TO use_of_force_incidents_id_seq')
    op.execute('ALTER INDEX use_of_force_incidents_impd_pkey RENAME TO use_of_force_incidents_pkey')
    op.execute('ALTER TABLE use_of_force_incidents RENAME CONSTRAINT "use_of_force_incidents_impd_department_id_fkey" TO "use_of_force_incidents_department_id_fkey"')

    op.rename_table('citizen_complaints_impd', 'citizen_complaints')
    op.execute('ALTER SEQUENCE citizen_complaints_impd_id_seq RENAME TO citizen_complaints_id_seq')
    op.execute('ALTER INDEX citizen_complaints_impd_pkey RENAME TO citizen_complaints_pkey')
    op.execute('ALTER TABLE citizen_complaints RENAME CONSTRAINT "citizen_complaints_impd_department_id_fkey" TO "citizen_complaints_department_id_fkey"')

    op.rename_table('assaults_on_officers_impd', 'assaults_on_officers')
    op.execute('ALTER SEQUENCE assaults_on_officers_impd_id_seq RENAME TO assaults_on_officers_id_seq')
    op.execute('ALTER INDEX assaults_on_officers_impd_pkey RENAME TO assaults_on_officers_pkey')
    op.execute('ALTER TABLE assaults_on_officers RENAME CONSTRAINT "assaults_on_officers_impd_department_id_fkey" TO "assaults_on_officers_department_id_fkey"')

    op.rename_table('officer_involved_shootings_impd', 'officer_involved_shootings')
    op.execute('ALTER SEQUENCE officer_involved_shootings_impd_id_seq RENAME TO officer_involved_shootings_id_seq')
    op.execute('ALTER INDEX officer_involved_shootings_impd_pkey RENAME TO officer_involved_shootings_pkey')
    op.execute('ALTER TABLE officer_involved_shootings RENAME CONSTRAINT "officer_involved_shootings_impd_department_id_fkey" TO "officer_involved_shootings_department_id_fkey"') 

def upgrade():
    # Rename option table
    op.drop_index("ix_poll_option_poll_id", table_name="poll_option")
    op.rename_table("poll_option", "option")
    op.create_index(op.f("ix_option_poll_id"), "option", ["poll_id"], unique=False)

    # Rename vote.poll_option
    op.drop_constraint("one_vote_per_option_and_user", "vote", type_="unique")
    op.drop_index("ix_vote_poll_option_id", table_name="vote")
    op.drop_constraint("vote_poll_option_id_fkey", "vote", type_="foreignkey")

    op.alter_column("vote", "poll_option_id", new_column_name="option_id")

    op.create_index(op.f("ix_vote_option_id"), "vote", ["option_id"], unique=False)
    op.create_unique_constraint(
        "one_vote_per_option_and_user", "vote", ["user_id", "poll_id", "option_id"]
    )
    op.create_foreign_key(
        "vote_option_id_fkey",
        "vote",
        "option",
        ["option_id"],
        ["id"],
        ondelete="cascade",
    ) 

def genericize_imapaccount():
    from inbox.models.session import session_scope
    from inbox.ignition import main_engine
    engine = main_engine(pool_size=1, max_overflow=0)
    Base = declarative_base()
    Base.metadata.reflect(engine)

    class ImapAccount_(Base):
        __table__ = Base.metadata.tables['imapaccount']

    # Get data from columns-to-be-dropped
    with session_scope() as db_session:
        results = db_session.query(ImapAccount_.id,
                                   ImapAccount_.imap_host).all()

    to_insert = [dict(id=r[0], imap_host=r[1]) for r in results]

    # Rename table, add new columns.
    op.rename_table('imapaccount', 'account')
    op.add_column('account', sa.Column('type', sa.String(16)))

    # Create new table, insert data
    # The table
    op.create_table('imapaccount',
                    sa.Column('imap_host', sa.String(512)),
                    sa.Column('id', sa.Integer()),
                    sa.ForeignKeyConstraint(['id'], ['account.id'],
                                            ondelete='CASCADE'),
                    sa.PrimaryKeyConstraint('id'))

    # The ad-hoc table for insert
    table_ = table('imapaccount',
                   column('imap_host', sa.String()),
                   column('id', sa.Integer))
    if to_insert:
        op.bulk_insert(table_, to_insert)

    # Drop columns now
    op.drop_column('account', 'imap_host') 

def upgrade():
    op.rename_table('knowledge_post_author', 'assoc_post_author')
    op.rename_table('knowledge_post_tags', 'assoc_post_tag')

    with op.batch_alter_table('assoc_post_author') as batch_op:
        batch_op.add_column(sa.Column('order', sa.Integer(), nullable=True))

    with op.batch_alter_table('posts') as batch_op:
        batch_op.add_column(sa.Column('uuid', sa.String(length=100), nullable=True))
        batch_op.create_unique_constraint('uq_uuid', ['uuid'])

    with op.batch_alter_table('pageviews') as batch_op:
        batch_op.add_column(sa.Column('object_action', sa.String(length=100), nullable=True))
        batch_op.add_column(sa.Column('version', sa.String(length=100), nullable=True)) 

def upgrade():
	# # password_reset_code
	op.rename_table('password_reset', 'password_reset_code')

	# Add date columns
	op.alter_column('password_reset_code', 'created', new_column_name='created_at', server_default=sa.func.current_timestamp())
	op.add_column('password_reset_code', sa.Column('updated_at', sa.TIMESTAMP, server_default=sa.func.current_timestamp()))

	# Rename email column
	op.alter_column('password_reset_code', 'user_id', new_column_name='email')

	# Create New Index
	op.create_index('ix_password_reset_code_email_code', 'password_reset_code', ['email', 'code'], unique=True)
	op.create_index('ix_password_reset_code_email', 'password_reset_code', ['email'])

	# Add new foreign key reference
	op.execute('ALTER TABLE "password_reset_code" ADD CONSTRAINT password_reset_code_email_fkey FOREIGN KEY (email) REFERENCES "user" (email) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE') 

def downgrade():
    op.rename_table("atomdetails", "taskdetails") 

def upgrade():
    op.rename_table("taskdetails", "atomdetails") 

def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column('node', sa.Column('modules_id', sa.INTEGER(), autoincrement=False, nullable=True))
    op.add_column('node', sa.Column('skin_id', sa.INTEGER(), autoincrement=False, nullable=True))
    op.add_column('node', sa.Column('type', sa.VARCHAR(length=255), autoincrement=False, nullable=True))
    op.add_column('node', sa.Column('target', sa.VARCHAR(length=80), autoincrement=False, nullable=True))
    op.add_column('node', sa.Column('custom', postgresql.BYTEA(), autoincrement=False, nullable=True))
    op.add_column('node', sa.Column('folder', sa.INTEGER(), server_default=sa.text('0'), autoincrement=False, nullable=True))
    op.add_column('node', sa.Column('image', sa.VARCHAR(length=255), autoincrement=False, nullable=True))
    op.add_column('node', sa.Column('folderid', sa.INTEGER(), autoincrement=False, nullable=True))
    op.add_column('node', sa.Column('parentid', sa.INTEGER(), autoincrement=False, nullable=True))
    op.add_column('node', sa.Column('mods', sa.INTEGER(), autoincrement=False, nullable=True))
    op.rename_table('page', 'node')
    op.execute('ALTER SEQUENCE page_id_seq RENAME TO node_id_seq')

    op.create_table('modules',
    sa.Column('id', sa.INTEGER(), nullable=False),
    sa.Column('name', sa.VARCHAR(length=80), autoincrement=False, nullable=True),
    sa.Column('title', sa.VARCHAR(length=80), autoincrement=False, nullable=True),
    sa.Column('orders', sa.INTEGER(), autoincrement=False, nullable=True),
    sa.PrimaryKeyConstraint('id', name='modules_pkey')
    )
    op.create_table('skin',
    sa.Column('id', sa.INTEGER(), nullable=False),
    sa.Column('title', sa.VARCHAR(length=80), autoincrement=False, nullable=True),
    sa.Column('fname', sa.VARCHAR(length=80), autoincrement=False, nullable=True),
    sa.Column('orders', sa.INTEGER(), autoincrement=False, nullable=True),
    sa.PrimaryKeyConstraint('id', name='skin_pkey')
    )
    # ### end Alembic commands ### 

def downgrade():
    # Rename option table
    op.drop_index(op.f("ix_option_poll_id"), table_name="option")
    op.rename_table("option", "poll_option")
    op.create_index("ix_poll_option_poll_id", "poll_option", ["poll_id"], unique=False)

    # Rename vote.poll_option
    op.drop_index("ix_vote_option_id", table_name="vote")
    op.drop_constraint("vote_option_id_fkey", "vote", type_="foreignkey")
    op.drop_constraint("one_vote_per_option_and_user", "vote", type_="unique")

    op.alter_column("vote", "option_id", new_column_name="poll_option_id")

    op.create_index(
        op.f("ix_vote_poll_option_id"), "vote", ["poll_option_id"], unique=False
    )
    op.create_foreign_key(
        "vote_poll_option_id_fkey",
        "vote",
        "poll_option",
        ["poll_option_id"],
        ["id"],
        ondelete="cascade",
    )
    op.create_unique_constraint(
        "one_vote_per_option_and_user", "vote", ["user_id", "poll_id", "poll_option_id"]
    ) 

def rename_table(conn, old_table_name, new_table_name, schema=None):
    """
    Renames given table in activity table. You should remember to call this
    function whenever you rename a versioned table.

    ::

        from alembic import op
        from postgresql_audit import rename_table


        def upgrade():
            op.rename_table('article', 'article_v2')
            rename_table(op, 'article', 'article_v2')


    :param conn:
        An object that is able to execute SQL (either SQLAlchemy Connection,
        Engine or Alembic Operations object)
    :param old_table_name:
        The name of table to rename
    :param new_table_name:
        New name of the renamed table
    :param schema:
        Optional name of schema to use.
    """
    activity_table = get_activity_table(schema=schema)
    query = (
        activity_table
        .update()
        .values(table_name=new_table_name)
        .where(activity_table.c.table_name == old_table_name)
    )
    return conn.execute(query) 

def upgrade():
    op.rename_table('fda', 'fdadl') 

def downgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.rename_table('stream_submission', 'stream_sub')
    ### end Alembic commands ### 

def downgrade():
    op.rename_table("github_summary_user", "user") 

def upgrade():
    op.rename_table("user", "github_summary_user") 

def downgrade_export_locations_table(connection):
    op.rename_table('share_instance_export_locations',
                    'share_export_locations')
    op.add_column(
        'share_export_locations',
        Column('share_id', String(36),
               ForeignKey('shares.id', name="sel_id_fk"))
    )

    # Convert share_instance_id to share_id
    share_el_table = utils.load_table('share_export_locations', connection)
    share_instances_table = utils.load_table('share_instances', connection)
    for export in connection.execute(share_el_table.select()):
        share_instance = connection.execute(
            share_instances_table.select().where(
                share_instances_table.c.id == export.share_instance_id)
        ).first()

        # pylint: disable=no-value-for-parameter
        op.execute(
            share_el_table.update().where(
                share_el_table.c.id == export.id
            ).values({'share_id': six.text_type(share_instance.share_id)})
        )

    with op.batch_alter_table("share_export_locations") as batch_op:
        batch_op.drop_constraint('sel_instance_id_fk', type_='foreignkey')
        batch_op.drop_column('share_instance_id') 

def upgrade_export_locations_table(connection):
    # Update 'share_export_locations' table
    op.add_column(
        'share_export_locations',
        Column('share_instance_id', String(36),
               ForeignKey('share_instances.id', name="sel_instance_id_fk"))
    )

    # Convert share_id to share_instance_id
    share_el_table = utils.load_table('share_export_locations', connection)
    share_instances_table = utils.load_table('share_instances', connection)
    for export in connection.execute(share_el_table.select()):
        share_instance = connection.execute(
            share_instances_table.select().where(
                share_instances_table.c.share_id == export.share_id)
        ).first()

        # pylint: disable=no-value-for-parameter
        op.execute(
            share_el_table.update().where(
                share_el_table.c.id == export.id
            ).values({'share_instance_id': six.text_type(share_instance.id)})
        )
    with op.batch_alter_table("share_export_locations") as batch_op:
        batch_op.drop_constraint('sel_id_fk', type_='foreignkey')
        batch_op.drop_column('share_id')
        batch_op.rename_table('share_export_locations',
                              'share_instance_export_locations') 

def downgrade():
    LOG.info("Creating volume_type_extra_specs table")
    vt_es = op.create_table(
        'volume_type_extra_specs',
        sa.Column('created_at', sa.DateTime),
        sa.Column('updated_at', sa.DateTime),
        sa.Column('deleted_at', sa.DateTime),
        sa.Column('deleted', sa.Boolean),
        sa.Column('id', sa.Integer, primary_key=True, nullable=False),
        sa.Column('volume_type_id', sa.String(length=36),
                  sa.ForeignKey('share_types.id'), nullable=False),
        sa.Column('key', sa.String(length=255)),
        sa.Column('value', sa.String(length=255)),
        mysql_engine='InnoDB')

    LOG.info("Migrating share_type_extra_specs to "
             "volume_type_extra_specs")
    _copy_records(destination_table=vt_es, up_migration=False)

    LOG.info("Dropping share_type_extra_specs table")
    op.drop_table("share_type_extra_specs")

    LOG.info("Renaming share_types table to volume_types")
    op.drop_constraint('st_name_uc', 'share_types', type_='unique')
    op.create_unique_constraint('vt_name_uc', 'share_types',
                                ['name', 'deleted'])
    op.rename_table("share_types", "volume_types")

    LOG.info("Renaming column name shares.share_type_id to "
             "shares.volume_type.id")
    op.alter_column("shares", "share_type_id",
                    new_column_name="volume_type_id",
                    type_=sa.String(length=36)) 

def upgrade():
    LOG.info("Renaming column name shares.volume_type_id to "
             "shares.share_type.id")
    op.alter_column("shares", "volume_type_id",
                    new_column_name="share_type_id",
                    type_=sa.String(length=36))

    LOG.info("Renaming volume_types table to share_types")
    op.rename_table("volume_types", "share_types")
    op.drop_constraint('vt_name_uc', 'share_types', type_='unique')
    op.create_unique_constraint('st_name_uc', 'share_types',
                                ['name', 'deleted'])

    LOG.info("Creating share_type_extra_specs table")
    st_es = op.create_table(
        'share_type_extra_specs',
        sa.Column('created_at', sa.DateTime),
        sa.Column('updated_at', sa.DateTime),
        sa.Column('deleted_at', sa.DateTime),
        sa.Column('deleted', sa.Integer),
        sa.Column('id', sa.Integer, primary_key=True, nullable=False),
        sa.Column('share_type_id', sa.String(length=36),
                  sa.ForeignKey('share_types.id', name="st_id_fk"),
                  nullable=False),
        sa.Column('spec_key', sa.String(length=255)),
        sa.Column('spec_value', sa.String(length=255)),
        mysql_engine='InnoDB')

    LOG.info("Migrating volume_type_extra_specs to "
             "share_type_extra_specs")
    _copy_records(destination_table=st_es, up_migration=True)

    LOG.info("Dropping volume_type_extra_specs table")
    op.drop_table("volume_type_extra_specs") 

def downgrade():
    op.rename_table('report', 'vote_ban') 

def upgrade():
    op.rename_table('vote_ban', 'report') 

def upgrade():
    for table in tables:
        op.rename_table(table, 'data_'+table) 

def downgrade():
    op.rename_table('fdadl', 'fda') 

def downgrade():
    op.rename_table("sync_event_database_task", "fetch_new_events_task")

    engine = op.get_bind()
    meta = sa.MetaData(bind=engine)
    task = sa.Table("task", meta, autoload=True)
    stmt = (
        task.update()
        .where(task.c.type == "sync_event_database")
        .values(type="fetch_new_events")
    )
    engine.execute(stmt)