summaryrefslogtreecommitdiff
path: root/lib/sqlalchemy/dialects/mssql/base.py
diff options
context:
space:
mode:
authorMike Bayer <mike_mp@zzzcomputing.com>2023-01-25 08:58:03 -0500
committerMike Bayer <mike_mp@zzzcomputing.com>2023-01-25 09:18:10 -0500
commitb710a5286310710d5d0a6d5170696e8fe4d4a0d8 (patch)
tree2e0980513d4728f1308267f36d1cdccc5793e7a5 /lib/sqlalchemy/dialects/mssql/base.py
parent55b243a3f22f267c829f9088fd4e801f0b621a31 (diff)
downloadsqlalchemy-b710a5286310710d5d0a6d5170696e8fe4d4a0d8.tar.gz
Make comment support conditional on fn_listextendedproperty availability
The newly added comment reflection and rendering capability of the MSSQL dialect, added in :ticket:`7844`, will now be disabled by default if it cannot be determined that an unsupported backend such as Azure Synapse may be in use; this backend does not support table and column comments and does not support the SQL Server routines in use to generate them as well as to reflect them. A new parameter ``supports_comments`` is added to the dialect which defaults to ``None``, indicating that comment support should be auto-detected. When set to ``True`` or ``False``, the comment support is either enabled or disabled unconditionally. Fixes: #9142 Change-Id: Ib5cac31806185e7353e15b3d83b580652d304b3b
Diffstat (limited to 'lib/sqlalchemy/dialects/mssql/base.py')
-rw-r--r--lib/sqlalchemy/dialects/mssql/base.py50
1 files changed, 50 insertions, 0 deletions
diff --git a/lib/sqlalchemy/dialects/mssql/base.py b/lib/sqlalchemy/dialects/mssql/base.py
index eabea88db..a30c57c7f 100644
--- a/lib/sqlalchemy/dialects/mssql/base.py
+++ b/lib/sqlalchemy/dialects/mssql/base.py
@@ -435,6 +435,29 @@ Note that when using LIMIT and/or OFFSET, whether using the older
or newer SQL Server syntaxes, the statement must have an ORDER BY as well,
else a :class:`.CompileError` is raised.
+.. _mssql_comment_support:
+
+DDL Comment Support
+--------------------
+
+Comment support, which includes DDL rendering for attributes such as
+:paramref:`_schema.Table.comment` and :paramref:`_schema.Column.comment`, as
+well as the ability to reflect these comments, is supported assuming a
+supported version of SQL Server is in use. If a non-supported version such as
+Azure Synapse is detected at first-connect time (based on the presence
+of the ``fn_listextendedproperty`` SQL function), comment support including
+rendering and table-comment reflection is disabled, as both features rely upon
+SQL Server stored procedures and functions that are not available on all
+backend types.
+
+To force comment support to be on or off, bypassing autodetection, set the
+parameter ``supports_comments`` within :func:`_sa.create_engine`::
+
+ e = create_engine("mssql+pyodbc://u:p@dsn", supports_comments=False)
+
+.. versionadded:: 2.0 Added support for table and column comments for
+ the SQL Server dialect, including DDL generation and reflection.
+
.. _mssql_isolation_level:
Transaction Isolation Level
@@ -3039,6 +3062,7 @@ class MSDialect(default.DefaultDialect):
use_scope_identity=True,
schema_name="dbo",
deprecate_large_types=None,
+ supports_comments=None,
json_serializer=None,
json_deserializer=None,
legacy_schema_aliasing=None,
@@ -3053,6 +3077,9 @@ class MSDialect(default.DefaultDialect):
self.ignore_no_transaction_on_rollback = (
ignore_no_transaction_on_rollback
)
+ self._user_defined_supports_comments = uds = supports_comments
+ if uds is not None:
+ self.supports_comments = uds
if legacy_schema_aliasing is not None:
util.warn_deprecated(
@@ -3160,6 +3187,7 @@ class MSDialect(default.DefaultDialect):
super().initialize(connection)
self._setup_version_attributes()
self._setup_supports_nvarchar_max(connection)
+ self._setup_supports_comments(connection)
def _setup_version_attributes(self):
if self.server_version_info[0] not in list(range(8, 17)):
@@ -3193,6 +3221,23 @@ class MSDialect(default.DefaultDialect):
else:
self._supports_nvarchar_max = True
+ def _setup_supports_comments(self, connection):
+ if self._user_defined_supports_comments is not None:
+ return
+
+ try:
+ connection.scalar(
+ sql.text(
+ "SELECT 1 FROM fn_listextendedproperty"
+ "(default, default, default, default, "
+ "default, default, default)"
+ )
+ )
+ except exc.DBAPIError:
+ self.supports_comments = False
+ else:
+ self.supports_comments = True
+
def _get_default_schema_name(self, connection):
query = sql.text("SELECT schema_name()")
default_schema_name = connection.scalar(query)
@@ -3432,6 +3477,11 @@ class MSDialect(default.DefaultDialect):
@reflection.cache
def get_table_comment(self, connection, table_name, schema=None, **kw):
+ if not self.supports_comments:
+ raise NotImplementedError(
+ "Can't get table comments on current SQL Server version in use"
+ )
+
schema_name = schema if schema else self.default_schema_name
COMMENT_SQL = """
SELECT cast(com.value as nvarchar(max))