this was kind of a big issue so that is fixed and in 0.8.3. today.
On 10/16/15 10:36 AM, Mike Bayer wrote: > this is a bug, > https://bitbucket.org/zzzeek/alembic/issues/333/batch-fails-on-tables-that-have-indexes > is added. thanks for reporting! > > > > On 10/16/15 4:20 AM, Dheeraj Gupta wrote: >> Hi, >> >> I have a single table in my sqlite database for which I am using alembic >> for migration. After the initial revision (creating table and columns), >> I needed to modify the models so that some of the columns allowed NULL. >> >> My original models.py looks like: >> >> # models.py ###########################################33 >> from sqlalchemy import (BigInteger, >> Column, >> Integer, >> SmallInteger, >> String) >> from sqlalchemy.ext.declarative import declarative_base >> from sqlalchemy.schema import MetaData >> >> >> convention = { >> "ix": 'ix_%(column_0_label)s', >> "uq": "uq_%(table_name)s_%(column_0_name)s", >> "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s", >> "pk": "pk_%(table_name)s" >> } >> >> mymetadata = MetaData(naming_convention=convention) >> Base = declarative_base(metadata=mymetadata) >> >> >> class MyBase(Base): >> __abstract__ = True >> __table_args__ = {'mysql_charset': 'utf8', 'mysql_engine': 'InnoDB', >> 'sqlite_autoincrement': 'True'} >> >> class TRequest(MyBase): >> __tablename__ = "trequests" >> id_ = Column("id", BigInteger().with_variant(Integer, "sqlite"), >> primary_key=True) >> req_uuid = Column(String(32), unique=True, index=True, nullable=False) >> ip1 = Column(String(15), nullable=True) >> ip2 = Column(String(15), nullable=True) >> port1 = Column(Integer, nullable=True) >> port2 = Column(Integer, nullable=True) >> proto = Column(String(3), nullable=True) >> tstamp = Column(Integer, nullable=True) >> win = Column(SmallInteger, nullable=False) >> status = Column(SmallInteger) >> status_changed = Column(Integer) >> accessed = Column(Integer) >> data_file = Column(String(255)) >> pcap_file = Column(String(255)) >> >> # End File############################################## >> >> req_uuid has a uniuq index which is named 'ix_trequests_req_uuid' per >> the naming convention >> >> In my base revision script, the upgrade() function is: >> def upgrade(): >> ### commands auto generated by Alembic - please adjust! ### >> op.create_table('trequests', >> sa.Column('id', sa.BigInteger().with_variant(sa.Integer, "sqlite"), >> nullable=False), >> sa.Column('req_uuid', sa.String(length=32), nullable=False), >> sa.Column('ip1', sa.String(length=15), nullable=False), >> sa.Column('ip2', sa.String(length=15), nullable=False), >> sa.Column('port1', sa.Integer(), nullable=False), >> sa.Column('port2', sa.Integer(), nullable=False), >> sa.Column('tstamp', sa.Integer(), nullable=False), >> sa.Column('proto', sa.String(length=3), nullable=False), >> sa.Column('win', sa.SmallInteger(), nullable=False), >> sa.Column('status', sa.SmallInteger(), nullable=True), >> sa.Column('accessed', sa.Integer(), nullable=True), >> sa.Column('status_changed', sa.Integer(), nullable=True), >> sa.Column('data_file', sa.String(length=15), nullable=True), >> sa.Column('pcap_file', sa.String(length=15), nullable=True), >> sa.PrimaryKeyConstraint('id', name=op.f('pk_trequests')), >> mysql_charset='utf8', >> mysql_engine='InnoDB', >> sqlite_autoincrement=True >> ) >> op.create_index(op.f('ix_trequests_req_uuid'), 'trequests', >> ['req_uuid'], unique=True) >> # End ########################### >> >> Now, I am adding a new revision to remove nullable=True from some >> columns. The relevant upgrade function is >> >> >> from net_transcript.db.models import convention as naming_convention >> >> def upgrade(): >> ### commands auto generated by Alembic - please adjust! ### >> with op.batch_alter_table('trequests', schema=None, >> naming_convention=naming_convention) as >> batch_op: >> batch_op.alter_column('port1', >> existing_type=sa.INTEGER(), >> nullable=True) >> batch_op.alter_column('port2', >> existing_type=sa.INTEGER(), >> nullable=True) >> batch_op.alter_column('proto', >> existing_type=sa.VARCHAR(length=3), >> nullable=True) >> ### end Alembic commands ### >> >> # End ####################################### >> >> However, on running the command I get an OperationalError >> (sqlite3.OperationalError) index ix_trequests_req_uuid already exists >> [SQL: u'CREATE UNIQUE INDEX ix_trequests_req_uuid ON _alembic_batch_temp >> (req_uuid)'] >> >> I looked at the source and in >> https://bitbucket.org/zzzeek/alembic/src/459549c9ab7fef91b2dc8986bc0643bb2f6ec0c8/alembic/operations/batch.py?at=master&fileviewer=file-view-default#batch.py-170 >> the Index is being defined by same name. I commented this code block and >> the migration succeeded without an error. >> Is this a bug or am I doing something wrong? >> >> >> Regards, >> Dheeraj >> >> -- >> You received this message because you are subscribed to the Google >> Groups "sqlalchemy-alembic" group. >> To unsubscribe from this group and stop receiving emails from it, send >> an email to sqlalchemy-alembic+unsubscr...@googlegroups.com >> <mailto:sqlalchemy-alembic+unsubscr...@googlegroups.com>. >> For more options, visit https://groups.google.com/d/optout. > -- You received this message because you are subscribed to the Google Groups "sqlalchemy-alembic" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy-alembic+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
