Python alembic.op.batch_alter_table() Examples

def downgrade():
    op.add_column('watchers', sa.Column('watch', sa.BOOLEAN(), nullable=True))

    # This section is to update the `watch` column with the value of
    # `watch_issues`
    connection = op.get_bind()
    for watcher in connection.execute(watcher_helper.select()):
        connection.execute(
            watcher_helper.update().where(
                watcher_helper.c.id == watcher.id
            ).values(
                watch=watcher.watch_issues
            )
        )

    with op.batch_alter_table('watchers') as b:
        # Set nullable to False now that we've set values
        b.alter_column('watch', nullable=False)
        # Drop the added columns
        b.drop_column('watch_issues')
        b.drop_column('watch_commits') 

def modify_execution_date_with_constraint(conn, batch_operator, table_name, type_, nullable):
    """
    Helper function changes type of column execution_date by
    dropping and recreating any primary/unique constraint associated with
    the column

    :param conn: sql connection object
    :param batch_operator: batch_alter_table for the table
    :param table_name: table name
    :param type_: DB column type
    :param nullable: nullable (boolean)
    :return: a dictionary of ((constraint name, constraint type), column name) of table
    :rtype: defaultdict(list)
    """
    constraint_dict = get_table_constraints(conn, table_name)
    drop_constraint(batch_operator, constraint_dict)
    batch_operator.alter_column(
        column_name="execution_date",
        type_=type_,
        nullable=nullable,
    )
    create_constraint(batch_operator, constraint_dict) 

def create_constraint(operator, constraint_dict):
    """
    Create a primary key or unique constraint

    :param operator: batch_alter_table for the table
    :param constraint_dict: a dictionary of ((constraint name, constraint type), column name) of table
    """
    for constraint, columns in constraint_dict.items():
        if 'execution_date' in columns:
            if constraint[1].lower().startswith("primary"):
                operator.create_primary_key(
                    constraint_name=constraint[0],
                    columns=reorder_columns(columns)
                )
            elif constraint[1].lower().startswith("unique"):
                operator.create_unique_constraint(
                    constraint_name=constraint[0],
                    columns=reorder_columns(columns)
                ) 

def drop_constraint(operator, constraint_dict):
    """
    Drop a primary key or unique constraint

    :param operator: batch_alter_table for the table
    :param constraint_dict: a dictionary of ((constraint name, constraint type), column name) of table
    """
    for constraint, columns in constraint_dict.items():
        if 'execution_date' in columns:
            if constraint[1].lower().startswith("primary"):
                operator.drop_constraint(
                    constraint[0],
                    type_='primary'
                )
            elif constraint[1].lower().startswith("unique"):
                operator.drop_constraint(
                    constraint[0],
                    type_='unique'
                ) 

def upgrade():
    connection = op.get_bind()
    if connection.engine.name != 'sqlite':
        return
    
    with op.batch_alter_table('user') as batch_op:
        batch_op.add_column(sa.Column('_password',
               sqlalchemy_utils.types.password.PasswordType(max_length=128),
               server_default='',
               nullable=False
            ))
    
    connection.execute(
            UserHelper.update().values(_password=UserHelper.c.password)
        )
    
    with op.batch_alter_table('user') as batch_op:
        batch_op.drop_column('password')
        batch_op.alter_column('_password', server_default=None, new_column_name='password') 

def upgrade():
    try:
        with op.batch_alter_table("smsgatewayoption") as batch_op:
            batch_op.drop_constraint('sgix_1', type_='unique')
            batch_op.create_unique_constraint('sgix_1', ['gateway_id', 'Key', 'Type'])
    except Exception as exx:
        print("Cannot change constraint 'sgix_1' in table smsgatewayoption.")
        print(exx)

    try:
        bind = op.get_bind()
        session = orm.Session(bind=bind)
        # add default type 'option' for all rows
        for row in session.query(SMSGatewayOption):
            if not row.Type:
                row.Type = "option"

    except Exception as exx:
        session.rollback()
        print("Failed to add option type for all existing entries in table smsgatewayoption!")
        print(exx)

    session.commit() 

def upgrade():
    """
    Make TaskInstance.pool field not nullable.
    """
    with create_session() as session:
        session.query(TaskInstance) \
            .filter(TaskInstance.pool.is_(None)) \
            .update({TaskInstance.pool: 'default_pool'},
                    synchronize_session=False)  # Avoid select updated rows
        session.commit()

    conn = op.get_bind()
    if conn.dialect.name == "mssql":
        op.drop_index('ti_pool', table_name='task_instance')

    # use batch_alter_table to support SQLite workaround
    with op.batch_alter_table('task_instance') as batch_op:
        batch_op.alter_column(
            column_name='pool',
            type_=sa.String(50),
            nullable=False,
        )

    if conn.dialect.name == "mssql":
        op.create_index('ti_pool', 'task_instance', ['pool', 'state', 'priority_weight']) 

def downgrade():
    connection = op.get_bind()
    if connection.engine.name != 'sqlite':
        return
    
    with op.batch_alter_table('user') as batch_op:
        batch_op.add_column(sa.Column('_password',
               type_=sa.NUMERIC(precision=128),
               server_default='',
               nullable=False
            ))
    
    connection.execute(
            UserHelper.update().values(_password=UserHelper.c.password)
        )
    
    with op.batch_alter_table('user') as batch_op:
        batch_op.drop_column('password')
        batch_op.alter_column('_password', server_default=None, new_column_name='password') 

def upgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.add_column('user', sa.Column('created', sa.DateTime(), nullable=True))
    op.add_column('user', sa.Column('updated', sa.DateTime(), nullable=True))
    with op.batch_alter_table('user') as batch_op:
        batch_op.alter_column('password',
               existing_type=sa.VARCHAR(length=128),
               type_=sqlalchemy_utils.types.password.PasswordType(max_length=128),
               existing_nullable=False,
               postgresql_using='password::bytea')
    ### end Alembic commands ###

    user = sa.Table('user',
        sa.MetaData(),
        sa.Column('created', sa.DateTime()),
        sa.Column('updated', sa.DateTime()),
    )

    op.execute(
        user.update().values({'created': datetime.now(), 'updated': datetime.now()})
    ) 

def downgrade():
    op.drop_table("download")
    op.drop_table("build_architecture")
    op.drop_table("displayname")
    op.drop_table("description")
    op.drop_table("icon")
    sa.Enum(name="icon_size").drop(op.get_bind(), checkfirst=False)
    op.drop_table("build")
    op.drop_table("version_service_dependency")
    op.drop_table("package_user_maintainer")
    with op.batch_alter_table("version", schema=None) as batch_op:
        batch_op.drop_index(batch_op.f("ix_version_version"))
    op.drop_table("version")
    op.drop_table("screenshot")
    op.drop_table("user_role")
    op.drop_table("package")
    op.drop_table("service")
    op.drop_table("firmware")
    op.drop_table("language")
    op.drop_table("architecture")
    op.drop_table("role")
    op.drop_table("user") 

def upgrade():
    op.add_column('watchers', sa.Column('watch_commits', sa.Boolean(),
                                        nullable=True))
    op.add_column('watchers', sa.Column('watch_issues', sa.Boolean(),
                                        nullable=True))
    # This section is to update the `watch_issues` and `watch_commits` columns
    # with the value of `watch`
    connection = op.get_bind()
    for watcher in connection.execute(watcher_helper.select()):
        connection.execute(
            watcher_helper.update().where(
                watcher_helper.c.id == watcher.id
            ).values(
                watch_issues=watcher.watch,
                watch_commits=False
            )
        )

    with op.batch_alter_table('watchers') as b:
        # Set nullable to False now that we've set values
        b.alter_column('watch_issues', nullable=False)
        b.alter_column('watch_commits', nullable=False)
        # Remove the watch column
        b.drop_column('watch') 

def upgrade():
    """Apply Set conn_type as non-nullable"""

    Base = declarative_base()

    class Connection(Base):
        __tablename__ = "connection"

        id = sa.Column(sa.Integer(), primary_key=True)
        conn_id = sa.Column(sa.String(250))
        conn_type = sa.Column(sa.String(500))

    # Generate run type for existing records
    connection = op.get_bind()
    sessionmaker = sa.orm.sessionmaker()
    session = sessionmaker(bind=connection)

    # imap_default was missing it's type, let's fix that up
    session.query(Connection).filter_by(conn_id="imap_default", conn_type=None).update(
        {Connection.conn_type: "imap"}, synchronize_session=False
    )
    session.commit()

    with op.batch_alter_table("connection", schema=None) as batch_op:
        batch_op.alter_column("conn_type", existing_type=sa.VARCHAR(length=500), nullable=False) 

def upgrade():
    # first, find and merge duplicates
    # then, set unique index
    bind = op.get_bind()
    select = sa.text('select email from "user" where email is not null group by email having count(*) > 1')
    for email in bind.execute(select):
        same_users = bind.execute(users.select().where(users.c.email == email[0]).order_by('registration_date')).fetchall()
        kept_user = same_users.pop()
        for obsolete_user in same_users:
            merge_users(bind, obsolete_user, kept_user)
            bind.execute(users.delete().where(
                and_(
                    users.c.username == obsolete_user.username,
                    users.c.source == obsolete_user.source)))
    # phantom users, lost forever...
    bind.execute(users.update().where(users.c.email == None).values(email_to_confirm=None))
    with op.batch_alter_table('user', schema=None) as batch_op:
        batch_op.create_index(batch_op.f('ix_email'), ['email'], unique=True)
        batch_op.create_index(batch_op.f('ix_email_to_confirm'), ['email_to_confirm'], unique=True) 

def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    with op.batch_alter_table('puppet') as batch_op:
        batch_op.drop_column('custom_mxid')
        batch_op.drop_column('access_token')
    # ### end Alembic commands ### 

def upgrade():
    ### commands auto generated by Alembic - please adjust! ###
    with op.batch_alter_table('user') as batch_op:
        batch_op.alter_column('created',
               existing_type=sa.DATETIME(),
               nullable=False)
        batch_op.alter_column('updated',
               existing_type=sa.DATETIME(),
               nullable=False)
    ### end Alembic commands ### 

def downgrade():
    ### commands auto generated by Alembic - please adjust! ###
    with op.batch_alter_table('user') as batch_op:
        batch_op.alter_column('updated',
               existing_type=sa.DATETIME(),
               nullable=True)
        batch_op.alter_column('created',
               existing_type=sa.DATETIME(),
               nullable=True)
    ### end Alembic commands ### 

def downgrade():
    with op.batch_alter_table("puppet") as batch_op:
        batch_op.drop_column("next_batch") 

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    with op.batch_alter_table('user', schema=None) as batch_op:
        batch_op.add_column(sa.Column('user_agent', sa.String(length=255), nullable=True))

    # ### end Alembic commands ### 

def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    with op.batch_alter_table('portal') as batch_op:
        batch_op.drop_column('encrypted')
    op.drop_table('nio_outgoing_key_request')
    op.drop_table('nio_olm_session')
    op.drop_table('nio_megolm_inbound_session')
    op.drop_table('nio_device_key')
    op.drop_table('nio_account')
    # ### end Alembic commands ### 

def downgrade():
    with op.batch_alter_table('fda_dap') as batch_op:
        batch_op.drop_column('drug_name')
        batch_op.drop_column('active_ingredients')
        batch_op.drop_column('company') 

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

    with op.batch_alter_table('oauth2_token') as batch_op:
        batch_op.alter_column('token_type',
               existing_type=sa.Enum('Bearer', name='tokentypes'),
               type_=sa.VARCHAR(length=40),
               existing_nullable=False)

    tokentypes = sa.dialects.postgresql.ENUM('Bearer', name='tokentypes')
    tokentypes.drop(connection) 

def downgrade():
    with op.batch_alter_table('team_member') as batch_op:
        batch_op.alter_column('is_leader',
               existing_type=sa.BOOLEAN(),
               nullable=True) 

def upgrade():
    with op.batch_alter_table('team_member') as batch_op:
        batch_op.alter_column('is_leader',
               existing_type=sa.BOOLEAN(),
               nullable=False) 

def downgrade():
    with op.batch_alter_table('team_member') as batch_op:
        batch_op.drop_column('is_leader')
    op.rename_table('team_member', 'team_members') 

def downgrade():
    with op.batch_alter_table('team_member') as batch_op:
        batch_op.drop_constraint('_team_user_uc', type_='unique') 

def upgrade():
    with op.batch_alter_table('team_member') as batch_op:
        batch_op.create_unique_constraint('_team_user_uc', ['team_id', 'user_id']) 

def downgrade():
    with op.batch_alter_table('board') as batch_op:
        batch_op.alter_column('show_archive', new_column_name='archive') 

def upgrade():
    with op.batch_alter_table('board') as batch_op:
        batch_op.alter_column('archive', new_column_name='show_archive') 

def upgrade():
    with op.batch_alter_table('command') as batch_op:
        batch_op.add_column(sa.Column('request', sa.String(1024)))
        batch_op.add_column(sa.Column('response', sa.String(1024)))
        batch_op.drop_column('body') 

def downgrade():
    with op.batch_alter_table('project') as batch_op:
        batch_op.drop_column('crawlable')

    with op.batch_alter_table('result') as batch_op:
        batch_op.drop_column('crawlable')