batch_op removes sqlite_autoincrement

[sqlalchemy-bot]

Migrated issue, originally created by berend (berend)

Given a sqlite3 table with __tableargs__ = {'sqlite_autoincrement': True} and a batch_op migration touching that table:

Model. initial migration

#!python

def upgrade():
    op.create_table('person',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('firstname', sa.String(), nullable=False),
    sa.Column('lastname', sa.String(), nullable=False),
    sa.PrimaryKeyConstraint('id'),
    sqlite_autoincrement=True
    )

simple migration, making firstname nullable:

#!python

def upgrade():
    with op.batch_alter_table('person') as batch_op:
        batch_op.alter_column(
            'firstname',
            existing_type=sa.TEXT(),
            server_default=None,
            nullable=True)

With echo-sql on I can see, that the first version of the person table has autoincrement on the id column. During the second migration, alembic creates a temp table, because sqlite does not support alter column operations. That temp table does not have the autoincrement on the id column. After renaming it to person, the autoincrement is gone

Manually adding table kwargs to the batch_op is a workaround.

#!python

def upgrade():
    with op.batch_alter_table('person', table_kwargs={'sqlite_autoincrement': True}) as batch_op:
        batch_op.alter_column(
            'firstname',
            existing_type=sa.TEXT(),
            server_default=None,
            nullable=True)

Do I have to always use table_kwargs to let alembic know? Or is there a bug when reading the table to create the temp table?

[sqlalchemy-bot]

Michael Bayer (zzzeek) wrote:

well SQLAlchemy would have to reflect this which I don't believe it does right now. SQLA can accept a pull request to include reflection of this option. sqlite_autoincrement is a rarely used option so the workaround here is not that unreasonable.

[sqlalchemy-bot]

Changes by berend (berend):

• edited description

[sqlalchemy-bot]

Changes by berend (berend):

• edited description