Weird Alembic Behavior

Question

I have 2 migration scripts.

Script 1: Base

from models import EntityProperty
from contextlib import contextmanager

# revision identifiers, used by Alembic.
revision = 'ecbde8fa83e3'
down_revision = None
branch_labels = None
depends_on = None

from alembic import op       # noqa
import sqlalchemy as sa      # noqa


@contextmanager
def session_scope():
   session = sa.orm.session.Session(bind=op.get_bind())
   try:
       yield session
       session.commit()
   except:
        session.rollback()
        raise
   finally:
        session.close()


def _upgrade(session):
   properties = session.query(EntityProperty).filter(
      ~EntityProperty._ptype.in_(["AI", "AO", "AV"])
   ).all()

   for _property in properties:
      _property._cov_increment = None


def upgrade():
   with session_scope() as session:
    _upgrade(session)


def downgrade():
    pass

Script 2:

 revision = 'ab47480a7be7'
 down_revision = u'ecbde8fa83e3'
 branch_labels = None
 depends_on = None

 from alembic import op       # noqa
 import sqlalchemy as sa      # noqa


def upgrade():
   # add bacnet_enable to EP
   with op.batch_alter_table(u'entityproperties', schema=None) as batch_op:
      batch_op.execute("PRAGMA foreign_keys=OFF;")
      batch_op.add_column(
          sa.Column(
              'bacnet_object', sa.Boolean(), nullable=True,
              server_default=expression.true()))


 def downgrade():
    with op.batch_alter_table(u'entityproperties', schema=None) as batch_op:
      batch_op.drop_column('bacnet_object')

Now when I try to downgrade to the base, I get an error stating that

no such column: entityproperties.bacnet_object

This error is generated while executing the base script(Script 1). I checked the generated SQL and it has

entityproperties.bacnet_object AS entityproperties_bacnet_object

Why is bacnet_object column is being asked for while executing script1? The downgrade of Script2 should remove the column from the EntityProperty table. What am I doing wrong here?

Update: I did check the db after the 2nd script is executed. There is no column called bacnet_object in EntityProperty table, but still the SQL generated is looking for a bacnet_object column?

score 0 · Answer 1 · answered Oct 31 '17 at 08:18

Answering my question.

When we import a model from application in the migration script we might run into issues like these. Since each migration file represents a point in time of your schema design, one that might be very different from what the current model is. This difference in the current model during migration and the imported model might cause issues.

The solution is to create table metadata in the migration script itself.We dont need all the columns of the table, just the primary key and the columns that will be used in the migration script. Once we create a model in the script, in update we can create a session. Here is the modifier script 1.

"""
"""
from __future__ import unicode_literals
from __future__ import absolute_import

from contextlib import contextmanager
from sqlalchemy.ext.declarative import declarative_base

# revision identifiers, used by Alembic.
revision = 'ecbde8fa83e3'
down_revision = None
branch_labels = None
depends_on = None

from alembic import op       # noqa
import sqlalchemy as sa      # noqa


Base = declarative_base()


class EntityProperty(Base):

    __tablename__ = "entityproperties"
    _uuid = sa.Column(sa.String, primary_key=True, nullable=False)
    _ptype = sa.Column(sa.String, nullable=False)
    _protourl = sa.Column(sa.String)


@contextmanager
def session_scope():
    session = sa.orm.session.Session(bind=op.get_bind())
    try:
        yield session
        session.commit()
    except:
        session.rollback()
        raise
    finally:
        session.close()


def _upgrade(session):
    properties = session.query(EntityProperty).filter(
        ~EntityProperty._ptype.in_(["AI", "AO", "AV"])
    ).all()

    for _property in properties:
        _property._cov_increment = None


def upgrade():
    with session_scope() as session:
        _upgrade(session)


def downgrade():
    pass

Weird Alembic Behavior

1 Answers1