使用Alembic异步迁移ClickHouse时遇NotSupportedError错误求助

错误根源 #

从报错栈可以明确，问题出在Alembic尝试提交事务时，ClickHouse异步驱动asynch抛出NotSupportedError——核心原因是ClickHouse默认不支持事务，即便Alembic日志显示"Will assume non-transactional DDL"，实际执行流程中仍触发了事务提交逻辑。

解决方案 #

1. 强制Alembic禁用事务 #

修改migrations/env.py中的迁移执行逻辑，明确关闭事务支持：
在do_run_migrations函数内，调用context.run_migrations()前添加配置：

def do_run_migrations(connection):
    context.configure(
        connection=connection,
        target_metadata=target_metadata,
        transaction_per_migration=False,  # 关闭每个迁移的事务
        render_as_batch=True  # 针对ClickHouse表结构修改的兼容配置，可选
    )

    with context.begin_transaction():
        context.run_migrations()

如果你的env.py是异步模式，确保在run_async_migrations中传递正确配置。

2. 配置异步引擎为自动提交模式 #

在创建异步引擎时，设置隔离级别为自动提交，避免SQLAlchemy尝试触发事务：

from sqlalchemy.ext.asyncio import create_async_engine

dsn = f"clickhouse+asynch://{CLICKHOUSE_USER}:{CLICKHOUSE_PASSWORD}@{CLICKHOUSE_HOST}:{CLICKHOUSE_PORT}/{CLICKHOUSE_DB}"
engine = create_async_engine(
    dsn,
    isolation_level="AUTOCOMMIT"
)

也可以直接在DSN后追加参数：

dsn = f"clickhouse+asynch://{CLICKHOUSE_USER}:{CLICKHOUSE_PASSWORD}@{CLICKHOUSE_HOST}:{CLICKHOUSE_PORT}/{CLICKHOUSE_DB}?autocommit=True"

3. 检查迁移脚本 #

确保迁移脚本中没有依赖事务的操作（比如在一个迁移里执行多个DDL语句并期望原子性），因为ClickHouse不支持这类事务操作，每个DDL都需要独立执行。

错误日志参考 #

(reporting3.11) PS D:\Projects\work\reporting\reporting> alembic upgrade head                       
INFO  [alembic.runtime.migration] Context impl ClickHouseDialectImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade  -> fc4badfc681a, empty message
Traceback (most recent call last):
  File "<frozen runpy>", line 198, in _run_module_as_main
  File "<frozen runpy>", line 88, in _run_code
  File "D:\Projects\env\reporting3.11\Scripts\alembic.exe\__main__.py", line 7, in <module>
  File "D:\Projects\env\reporting3.11\Lib\site-packages\alembic\config.py", line 641, in main
    CommandLine(prog=prog).main(argv=argv)
  File "D:\Projects\env\reporting3.11\Lib\site-packages\alembic\config.py", line 631, in main
    self.run_cmd(cfg, options)
  File "D:\Projects\env\reporting3.11\Lib\site-packages\alembic\config.py", line 608, in run_cmd
    fn(
  File "D:\Projects\env\reporting3.11\Lib\site-packages\alembic\command.py", line 403, in upgrade
    script.run_env()
  File "D:\Projects\env\reporting3.11\Lib\site-packages\alembic\script\base.py", line 583, in run_env
    util.load_python_file(self.dir, "env.py")
  File "D:\Projects\env\reporting3.11\Lib\site-packages\alembic\util\pyfiles.py", line 95, in load_python_file
    module = load_module_py(module_id, path)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "D:\Projects\env\reporting3.11\Lib\site-packages\alembic\util\pyfiles.py", line 113, in load_module_py
    spec.loader.exec_module(module)  # type: ignore
    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "<frozen importlib._bootstrap_external>", line 940, in exec_module
  File "<frozen importlib._bootstrap>", line 241, in _call_with_frames_removed
  File "D:\Projects\work\reporting\reporting\migrations\env.py", line 100, in <module>
    run_migrations_online()
  File "D:\Projects\work\reporting\reporting\migrations\env.py", line 94, in run_migrations_online
    asyncio.run(run_async_migrations())
  File "C:\Users\Ira\AppData\Local\Programs\Python\Python311\Lib\asyncio\runners.py", line 190, in run
    return runner.run(main)
           ^^^^^^^^^^^^^^^^
  File "C:\Users\Ira\AppData\Local\Programs\Python\Python311\Lib\asyncio\runners.py", line 118, in run
    return self._loop.run_until_complete(task)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\Ira\AppData\Local\Programs\Python\Python311\Lib\asyncio\base_events.py", line 653, in run_until_complete
    return future.result()
           ^^^^^^^^^^^^^^^
  File "D:\Projects\work\reporting\reporting\migrations\env.py", line 86, in run_async_migrations
    await connection.run_sync(do_run_migrations)
  File "D:\Projects\env\reporting3.11\Lib\site-packages\sqlalchemy\ext\asyncio\engine.py", line 886, in run_sync
    return await greenlet_spawn(
           ^^^^^^^^^^^^^^^^^^^^^
  File "D:\Projects\env\reporting3.11\Lib\site-packages\sqlalchemy\util\_concurrency_py3k.py", line 201, in greenlet_spawn
    result = context.throw(*sys.exc_info())
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "D:\Projects\work\reporting\reporting\migrations\env.py", line 70, in do_run_migrations
    context.run_migrations()
  File "<string>", line 8, in run_migrations
  File "D:\Projects\env\reporting3.11\Lib\site-packages\alembic\runtime\environment.py", line 948, in run_migrations
    self.get_context().run_migrations(**kw)
  File "D:\Projects\env\reporting3.11\Lib\site-packages\alembic\runtime\migration.py", line 616, in run_migrations
    with self.begin_transaction(_per_migration=True):
  File "D:\Projects\env\reporting3.11\Lib\site-packages\alembic\runtime\migration.py", line 84, in __exit__
    self._proxied_transaction.__exit__(type_, value, traceback)
  File "D:\Projects\env\reporting3.11\Lib\site-packages\sqlalchemy\engine\util.py", line 147, in __exit__
    with util.safe_reraise():
  File "D:\Projects\env\reporting3.11\Lib\site-packages\sqlalchemy\util\langhelpers.py", line 146, in __exit__
    raise exc_value.with_traceback(exc_tb)
  File "D:\Projects\env\reporting3.11\Lib\site-packages\sqlalchemy\engine\util.py", line 145, in __exit__
    self.commit()
  File "D:\Projects\env\reporting3.11\Lib\site-packages\sqlalchemy\engine\base.py", line 2633, in commit
    self._do_commit()
  File "D:\Projects\env\reporting3.11\Lib\site-packages\sqlalchemy\engine\base.py", line 2738, in _do_commit
    self._connection_commit_impl()
  File "D:\Projects\env\reporting3.11\Lib\site-packages\sqlalchemy\engine\base.py", line 2709, in _connection_commit_impl
    self.connection._commit_impl()
  File "D:\Projects\env\reporting3.11\Lib\site-packages\sqlalchemy\engine\base.py", line 1150, in _commit_impl
    self._handle_dbapi_exception(e, None, None, None, None)
  File "D:\Projects\env\reporting3.11\Lib\site-packages\sqlalchemy\engine\base.py", line 2360, in _handle_dbapi_exception
    raise exc_info[1].with_traceback(exc_info[2])
  File "D:\Projects\env\reporting3.11\Lib\site-packages\sqlalchemy\engine\base.py", line 1148, in _commit_impl
    self.engine.dialect.do_commit(self.connection)
  File "D:\Projects\env\reporting3.11\Lib\site-packages\sqlalchemy\engine\default.py", line 696, in do_commit
    dbapi_connection.commit()
  File "D:\Projects\env\reporting3.11\Lib\site-packages\clickhouse_sqlalchemy\drivers\asynch\connector.py", line 182, in commit
    self.await_(self._connection.commit())
  File "D:\Projects\env\reporting3.11\Lib\site-packages\sqlalchemy\util\_concurrency_py3k.py", line 131, in await_only
    return current.driver.switch(awaitable)  # type: ignore[no-any-return]
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "D:\Projects\env\reporting3.11\Lib\site-packages\sqlalchemy\util\_concurrency_py3k.py", line 196, in greenlet_spawn
    value = await result
            ^^^^^^^^^^^^
  File "D:\Projects\env\reporting3.11\Lib\site-packages\asynch\connection.py", line 90, in commit
    raise errors.NotSupportedError
asynch.errors.NotSupportedError: Code: None.

原DSN配置 #

dsn: str = f"clickhouse+asynch://{CLICKHOUSE_USER}:{CLICKHOUSE_PASSWORD}@{CLICKHOUSE_HOST}:{CLICKHOUSE_PORT}/{CLICKHOUSE_DB}"

内容的提问来源于stack exchange，提问作者Ира Волокидо