better exception when attempting reflection in batch with --sql

[sqlalchemy-bot]

Migrated issue, originally created by Tim Mitchell

Given a migration from

# original table
class Customer(Base):
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(Unicode, nullable=False)

to this

# new table
class Customer(Base):
    id = Column(Integer, primary_key=True, autoincrement=True)
    first_name = Column(Unicode, nullable=False)
    last_name = Column(Unicode, nullable=False)

with upgrade step

# upgrade step in migration script
def upgrade():
    op.add_column('Customer', sa.Column('first_name', sa.Unicode(), nullable=True))
    op.add_column('Customer', sa.Column('last_name', sa.Unicode(), nullable=True))

    conn = op.get_bind()
    conn.execute('''UPDATE Customer SET
                     first_name = SUBSTR(name, 0, instr(name, ' ')),
                     last_name = SUBSTR(name, instr(name, ' ')+1)
                     ''')

    with op.batch_alter_table("Customer") as batch_op:  # sqlite requires this
        batch_op.alter_column('first_name', nullable=False)
        batch_op.alter_column('last_name', nullable=False)
        batch_op.drop_column('name')

I get this traceback

  File "migrations\env.py", line 95, in <module>
    run_migrations_offline()
  File "migrations\env.py", line 72, in run_migrations_offline
    context.run_migrations()
  File "<string>", line 8, in run_migrations
  File "C:\envs\pycon\lib\site-packages\alembic\runtime\environment.py", line 788, in run_migrations
    self.get_context().run_migrations(**kw)
  File "C:\envs\pycon\lib\site-packages\alembic\runtime\migration.py", line 312, in run_migrations
    step.migration_fn(**kw)
  File "C:\Users\tim.mitchell\Source\pycon-talk\migrations\versions\39bb7539f0ef_split_name_column.py", line 32, in upgrade
    batch_op.drop_column('name')
  File "C:\Python-279-amd64\Lib\contextlib.py", line 24, in __exit__
    self.gen.next()
  File "C:\envs\pycon\lib\site-packages\alembic\operations\base.py", line 299, in batch_alter_table
    impl.flush()
  File "C:\envs\pycon\lib\site-packages\alembic\operations\batch.py", line 69, in flush
    *self.reflect_args, **self.reflect_kwargs)
  File "C:\envs\pycon\lib\site-packages\sqlalchemy\sql\schema.py", line 416, in __new__
    metadata._remove_table(name, schema)
  File "C:\envs\pycon\lib\site-packages\sqlalchemy\util\langhelpers.py", line 60, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "C:\envs\pycon\lib\site-packages\sqlalchemy\sql\schema.py", line 411, in __new__
    table._init(name, metadata, *args, **kw)
  File "C:\envs\pycon\lib\site-packages\sqlalchemy\sql\schema.py", line 484, in _init
    self._autoload(metadata, autoload_with, include_columns)
  File "C:\envs\pycon\lib\site-packages\sqlalchemy\sql\schema.py", line 494, in _autoload
    autoload_with.run_callable(
AttributeError: 'MockConnection' object has no attribute 'run_callable'

Source files attached.

---

Attachments: pycon-talk.zip

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

I see run_migrations_offline(), which implies you are running in --sql mode. As per the docs is not really how batch is meant to work and requires special steps to work around, as table reflection is obviously not available. Is this what you intend ? The "copy_from" flag at the very least is required.

[sqlalchemy-bot]

Tim Mitchell wrote:

Yes, I am running in --sql, hadn't noticed that extra parameter requirement in the docs - my bad sorry. Still, a message to that effect would be nice.

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

sure thing, batch is a new / quasi-experimental feature

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• changed title from "Traceback generating SQL from op.batch_alter_table" to "better exception when attempting reflection in bat"