Alembic detecting tables that already exits,so it trying to create them again

Question

My model of animation table is below: In the database there are not the test column and I want to update it with alembic.

class Animation(Base):
    __tablename__="Animation"
    id=Column(Integer,primary_key=True,index=True,autoincrement=True)
    name=Column(VARCHAR(100))
    description=Column(VARCHAR(100))
    animation=Column(VARCHAR(100))
    created_at=Column(DateTime)
    updated_at=Column(DateTime)
    test=Column(DateTime)

But alembic detecting all existing tables and trying to recreate them. So it returns an error when ı run the command that "alembic upgrade head".It says that the table already exists.

Version script like this when ı run the autogenerate command :

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('Animation',
    sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
    sa.Column('name', sa.VARCHAR(length=100), nullable=True),
    sa.Column('description', sa.VARCHAR(length=100), nullable=True),
    sa.Column('animation', sa.VARCHAR(length=100), nullable=True),
    sa.Column('created_at', sa.DateTime(), nullable=True),
    sa.Column('updated_at', sa.DateTime(), nullable=True),
    sa.Column('test', sa.DateTime(), nullable=True),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_Animation_id'), 'Animation', ['id'], unique=False)
    op.create_table('Faq',
    sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
    sa.Column('title', sa.VARCHAR(length=100), nullable=True),
    sa.Column('description', sa.VARCHAR(length=100), nullable=True),
    sa.Column('animation_id', sa.Integer(), nullable=True),
    sa.Column('created_at', sa.DateTime(), nullable=True),
    sa.Column('updated_at', sa.DateTime(), nullable=True),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_Faq_id'), 'Faq', ['id'], unique=False)
    op.create_table('Service',
    sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
    sa.Column('plate', sa.VARCHAR(length=100), nullable=True),
    sa.Column('arrive_city', sa.VARCHAR(length=100), nullable=True),
    sa.Column('arrive_district', sa.VARCHAR(length=100), nullable=True),
    sa.Column('departure_time', sa.Time(), nullable=True),
    sa.Column('created_at', sa.DateTime(), nullable=True),
    sa.Column('updated_at', sa.DateTime(), nullable=True),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_Service_id'), 'Service', ['id'], unique=False)
    op.drop_index('ix_Service_id', table_name='service')
    op.drop_table('service')
    op.drop_index('ix_Animation_id', table_name='animation')
    op.drop_table('animation')
    op.drop_index('ix_Faq_id', table_name='faq')
    op.drop_table('faq')
    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('faq',
    sa.Column('id', mysql.INTEGER(display_width=11), autoincrement=True, nullable=False),
    sa.Column('title', mysql.VARCHAR(collation='utf8_turkish_ci', length=100), nullable=True),
    sa.Column('description', mysql.VARCHAR(collation='utf8_turkish_ci', length=100), nullable=True),
    sa.Column('animation_id', mysql.INTEGER(display_width=11), autoincrement=False, nullable=True),
    sa.Column('created_at', mysql.DATETIME(), nullable=True),
    sa.Column('updated_at', mysql.DATETIME(), nullable=True),
    sa.PrimaryKeyConstraint('id'),
    mysql_collate='utf8_turkish_ci',
    mysql_default_charset='utf8',
    mysql_engine='MyISAM'
    )
    op.create_index('ix_Faq_id', 'faq', ['id'], unique=False)
    op.create_table('animation',
    sa.Column('id', mysql.INTEGER(display_width=11), autoincrement=True, nullable=False),
    sa.Column('name', mysql.VARCHAR(collation='utf8_turkish_ci', length=100), nullable=True),
    sa.Column('description', mysql.VARCHAR(collation='utf8_turkish_ci', length=100), nullable=True),
    sa.Column('animation', mysql.VARCHAR(collation='utf8_turkish_ci', length=100), nullable=True),
    sa.Column('created_at', mysql.DATETIME(), nullable=True),
    sa.Column('updated_at', mysql.DATETIME(), nullable=True),
    sa.PrimaryKeyConstraint('id'),
    mysql_collate='utf8_turkish_ci',
    mysql_default_charset='utf8',
    mysql_engine='MyISAM'
    )
    op.create_index('ix_Animation_id', 'animation', ['id'], unique=False)
    op.create_table('service',
    sa.Column('id', mysql.INTEGER(display_width=11), autoincrement=True, nullable=False),
    sa.Column('plate', mysql.VARCHAR(collation='utf8_turkish_ci', length=100), nullable=True),
    sa.Column('arrive_city', mysql.VARCHAR(collation='utf8_turkish_ci', length=100), nullable=True),
    sa.Column('arrive_district', mysql.VARCHAR(collation='utf8_turkish_ci', length=100), nullable=True),
    sa.Column('departure_time', mysql.TIME(), nullable=True),
    sa.Column('created_at', mysql.DATETIME(), nullable=True),
    sa.Column('updated_at', mysql.DATETIME(), nullable=True),
    sa.PrimaryKeyConstraint('id'),
    mysql_collate='utf8_turkish_ci',
    mysql_default_charset='utf8',
    mysql_engine='MyISAM'
    )
    op.create_index('ix_Service_id', 'service', ['id'], unique=False)
    op.drop_index(op.f('ix_Service_id'), table_name='Service')
    op.drop_table('Service')
    op.drop_index(op.f('ix_Faq_id'), table_name='Faq')
    op.drop_table('Faq')
    op.drop_index(op.f('ix_Animation_id'), table_name='Animation')
    op.drop_table('Animation')
    # ### end Alembic commands ###

I am new at alembic and I could not find any solution.

Answer 1

It sounds like you have already created the tables in your database, and have only started using alembic now that you need to add a new column. So alembic assumes that it needs to create all the tables.

To workaround this you could:

• remove old migration scripts
• back up your database (just a precaution)
• do DELETE FROM alembic_version in your database, if the table exists
• revert your code to before you added the new column
• generate a migration, note the reference (abc123ef or similar)
• do alembic stamp <reference>
• add the code for the new column
• generate and run an alembic migration as usual

alembic stamp sets the reference of the previous migration in the database, so alembic knows where to start from. By generating an initial migration and stamping it we let alembic know that it only needs to run the migration for the new column.

You may be able to simply run alembic stamp head rather than generating and stamping the initial migration, but I haven't tested this.

Answer 2

The actual problem is here:

class Animation(Base):
    __tablename__="Animation"
    id=Column(Integer,primary_key=True,index=True,autoincrement=True)
    name=Column(VARCHAR(100))
    description=Column(VARCHAR(100))
    animation=Column(VARCHAR(100))
    created_at=Column(DateTime)
    updated_at=Column(DateTime)
    test=Column(DateTime)

The table name must start with a lowercase letter. Alembic tries to find a table that starts with Uppercase A. But in MySQL table names starts with a lowercase letter. So they don't match. So Alembic tries to recreate them. This fixed my problem.