alembic downgrade --sql traceback: AttributeError: 'MockConnection' object has no attribute 'close'

[bialix]

Python 3.6-x64
Alembic 1.7.7
SQLAlchemy 1.4.35

I have to use op.batch_alter_table otherwise op.drop_column does not work with sqlite. But batch_alter_table does not work with --sql option and produces traceback (see below).

def downgrade():
    with op.batch_alter_table("nodes") as bop:
        bop.drop_column("node_name")

But simple downgrade code with drop column operation works with --sql. Please advise what should I use to support both real sqlite migration and --sql generation:

def downgrade():
    op.drop_column("nodes", "node_name")

Last code works as expected with --sql flag:

>alembic downgrade --sql 89f796e03954:67157dcc7036
INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Generating static SQL
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.runtime.migration] Running downgrade 89f796e03954 -> 67157dcc7036, node_name
-- Running downgrade 89f796e03954 -> 67157dcc7036

ALTER TABLE nodes DROP COLUMN node_name;

UPDATE alembic_version SET version_num='67157dcc7036' WHERE alembic_version.version_num = '89f796e03954'; 

Traceback with batch_alter_table below:

  File "...\alembicdir\versions\89f796e03954_node_name.py", line 28, in downgrade
    bop.drop_column("node_name")
  File "C:\Python\3.6-64\lib\contextlib.py", line 88, in __exit__
    next(self.gen)
  File "...\venv\lib\site-packages\alembic\operations\base.py", line 374, in batch_alter_table
    impl.flush()
  File "...\venv\lib\site-packages\alembic\operations\batch.py", line 125, in flush
    **self.reflect_kwargs
  File "<string>", line 2, in __new__
  File "...\venv\lib\site-packages\sqlalchemy\util\deprecations.py", line 309, in warned
    return fn(*args, **kwargs)
  File "...\venv\lib\site-packages\sqlalchemy\sql\schema.py", line 616, in __new__
    metadata._remove_table(name, schema)
  File "...\venv\lib\site-packages\sqlalchemy\util\langhelpers.py", line 72, in __exit__
    with_traceback=exc_tb,
  File "...\venv\lib\site-packages\sqlalchemy\util\compat.py", line 207, in raise_
    raise exception
  File "...\venv\lib\site-packages\sqlalchemy\sql\schema.py", line 611, in __new__
    table._init(name, metadata, *args, **kw)
  File "...\venv\lib\site-packages\sqlalchemy\sql\schema.py", line 691, in _init
    resolve_fks=resolve_fks,
  File "...\venv\lib\site-packages\sqlalchemy\sql\schema.py", line 719, in _autoload
    insp = inspection.inspect(autoload_with)
  File "...\venv\lib\site-packages\sqlalchemy\inspection.py", line 64, in inspect
    ret = reg(subject)
  File "...\venv\lib\site-packages\sqlalchemy\engine\reflection.py", line 178, in _connectable_insp
    return Inspector._construct(Inspector._init_legacy, bind)
  File "...\venv\lib\site-packages\sqlalchemy\engine\reflection.py", line 117, in _construct
    init(self, bind)
  File "...\venv\lib\site-packages\sqlalchemy\engine\reflection.py", line 124, in _init_legacy
    self._init_engine(bind)
  File "...\venv\lib\site-packages\sqlalchemy\engine\reflection.py", line 128, in _init_engine
    engine.connect().close()
AttributeError: 'MockConnection' object has no attribute 'close'

[bialix]

The strangest thing here that upgrade --sql works just fine with batch_alter_table:

def upgrade():
    with op.batch_alter_table("nodes") as bop:
        bop.add_column(sa.Column("node_name", sa.String(length=255), nullable=True))

Result:

>alembic upgrade --sql 67157dcc7036:89f796e03954
INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Generating static SQL
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade 67157dcc7036 -> 89f796e03954, node_name
-- Running upgrade 67157dcc7036 -> 89f796e03954

ALTER TABLE nodes ADD COLUMN node_name VARCHAR(255);

UPDATE alembic_version SET version_num='89f796e03954' WHERE alembic_version.version_num = '67157dcc7036';

[nicodmf]

Same pb here

[zzzeek]

OK I am not understanding what you both are trying to do. This operation:

def downgrade():
    with op.batch_alter_table("nodes") as bop:
        bop.drop_column("node_name")

by definition cannot work without a live database connection on SQLite, so --sql mode makes no sense.

what is it you are expecting to see ?

[sqla-tester]

Mike Bayer has proposed a fix for this issue in the main branch:

fail gracefully for batch_alter_table() called in --sql mode https://gerrit.sqlalchemy.org/c/sqlalchemy/alembic/+/3965