Skip to content

Cannot add/update/remove comment in MSSQL to/from existing column #1755

Description

@Chormon

Describe the bug
When using the operations.alter_column to modify a comment in a column of a MSSQL table it gets an error. There is no problem with adding new column with comment.

Expected behavior
It's expected to update the column description (add/update/remove extended property).
Additionally, in MSSQL this operation should check for existing comment first, because adding new extended property with same name fails.

To Reproduce

op.alter_column(
    "user",
    "theme",
    existing_type=sa.String(length=20),
    comment="Column comment",
    existing_nullable=True,
    schema="dbo",
)

Error

Traceback (most recent call last):
  File "...\sqlalchemy\sql\visitors.py", line 134, in _compiler_dispatch
    meth = getter(visitor)
           ^^^^^^^^^^^^^^^
AttributeError: 'MSDDLCompiler' object has no attribute 'visit_clause'

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "...\sqlalchemy\ext\compiler.py", line 523, in _wrap_existing_dispatch
    return existing_dispatch(element, compiler, **kw)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "...\sqlalchemy\sql\visitors.py", line 136, in _compiler_dispatch
    return visitor.visit_unsupported_compilation(self, err, **kw)  # type: ignore  # noqa: E501
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "...\sqlalchemy\sql\compiler.py", line 919, in visit_unsupported_compilation
    raise exc.UnsupportedCompilationError(self, type(element)) from err
sqlalchemy.exc.UnsupportedCompilationError: Compiler <sqlalchemy.dialects.mssql.base.MSDDLCompiler object at 0x0000028C04822790> can't render element of type <class 'alembic.ddl.base.ColumnComment'> (Background on this error at: https://sqlalche.me/e/20/l7de)

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "<string>", line 8, in alter_column
  File "<string>", line 3, in alter_column
  File "...\alembic\operations\ops.py", line 1973, in alter_column
    return operations.invoke(alt)
           ^^^^^^^^^^^^^^^^^^^^^^
  File "...\alembic\operations\base.py", line 454, in invoke
    return fn(self, operation)
           ^^^^^^^^^^^^^^^^^^^
  File "...\alembic\operations\toimpl.py", line 53, in alter_column
    operations.impl.alter_column(
  File "...\alembic\ddl\mssql.py", line 164, in alter_column
    super().alter_column(
  File "...\alembic\ddl\impl.py", line 343, in alter_column
    self._exec(
  File "...\alembic\ddl\mssql.py", line 73, in _exec
    result = super()._exec(construct, *args, **kw)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "...\alembic\ddl\impl.py", line 246, in _exec
    return conn.execute(construct, params)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "...\sqlalchemy\engine\base.py", line 1419, in execute
    return meth(
           ^^^^^
  File "...\sqlalchemy\sql\ddl.py", line 187, in _execute_on_connection
    return connection._execute_ddl(
           ^^^^^^^^^^^^^^^^^^^^^^^^
  File "...\sqlalchemy\engine\base.py", line 1527, in _execute_ddl
    compiled = ddl.compile(
               ^^^^^^^^^^^^
  File "...\sqlalchemy\sql\elements.py", line 311, in compile
    return self._compiler(dialect, **kw)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "...\sqlalchemy\sql\ddl.py", line 76, in _compiler
    return dialect.ddl_compiler(dialect, self, **kw)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "...\sqlalchemy\sql\compiler.py", line 886, in __init__
    self.string = self.process(self.statement, **compile_kwargs)
                  ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "...\sqlalchemy\sql\compiler.py", line 932, in process
    return obj._compiler_dispatch(self, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "...\sqlalchemy\ext\compiler.py", line 538, in <lambda>
    lambda *arg, **kw: existing(*arg, **kw),
                       ^^^^^^^^^^^^^^^^^^^^
  File "...\sqlalchemy\ext\compiler.py", line 591, in __call__
    expr = fn(element, compiler, **kw)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "...\sqlalchemy\ext\compiler.py", line 525, in _wrap_existing_dispatch
    raise exc.UnsupportedCompilationError(
sqlalchemy.exc.UnsupportedCompilationError: Compiler <sqlalchemy.dialects.mssql.base.MSDDLCompiler object at 0x0000028C04822790> can't render element of type <class 'alembic.ddl.base.ColumnComment'>: <class 'alembic.ddl.base.ColumnComment'> construct has no default compilation handler. (Background on this error at: https://sqlalche.me/e/20/l7de)

Versions.

  • OS: Win 11
  • Python: 3.11
  • Alembic: 1.17.2
  • SQLAlchemy: 2.0.44
  • Database: Microsoft SQL Server 2019
  • DBAPI: mssql (pyodbc 5.2.0), ODBC Driver 17 for SQL Server

Have a nice day!

Metadata

Metadata

Assignees

No one assigned

    Labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions