kaxil commented on code in PR #74539:
URL: https://github.com/apache/airflow/pull/74539#discussion_r4622376471


##########
providers/microsoft/mssql/src/airflow/providers/microsoft/mssql/hooks/mssql.py:
##########
@@ -98,10 +157,20 @@ def get_sqlalchemy_connection(
         engine = self.get_sqlalchemy_engine(engine_kwargs=engine_kwargs)
         return engine.connect(**(connect_kwargs or {}))
 
-    def get_conn(self) -> PymssqlConnection:
+    def get_conn(self) -> Any:

Review Comment:
   Switching a connection to `mssql_python` also switches `MSSQLToGCSOperator` 
and `MsSqlToHiveOperator`, which call `get_conn()` and map 
`cursor.description[i][1]` through pymssql's integer type codes. mssql-python 
puts Python types there (`int`, `decimal.Decimal`, `datetime.datetime`), so the 
Hive operator creates a table with every column as STRING, and a parquet export 
from the GCS operator fails on the first numeric column. Can both operators 
handle the mssql-python types here, or should the docs say they need pymssql 
for now?



##########
providers/microsoft/mssql/src/airflow/providers/microsoft/mssql/hooks/mssql.py:
##########
@@ -81,12 +135,17 @@ def get_uri(self) -> str:
         from urllib.parse import parse_qs, urlencode, urlsplit, urlunsplit
 
         r = list(urlsplit(super().get_uri()))
-        # change pymssql driver:
+        # change the sqlalchemy driver:
         r[0] = self.sqlalchemy_scheme
-        # remove query string 'sqlalchemy_scheme' like parameters:
+        # remove query string parameters that are meant for the hook, not for 
the sqlalchemy dialect:
+        hook_only_extras = (

Review Comment:
   With `{"TrustServerCertificate": true}` in the extras, 
`Connection.get_uri()` can't round-trip the bool, so it packs everything into 
one `__extra__=<json>` param and this filter never sees `dbapi_driver` or 
anything else. The `mssqlpython` dialect copies that param into the connection 
string and mssql-python rejects `__extra__` as an unknown keyword, while 
`get_conn()` takes the same extras without complaint. Leftover pymssql keys 
like `timeout` hit the same rejection here, where `connect()` only warns about 
them. Could the mssql-python branch build `sqlalchemy_url` with 
`URL.create(...)` from stringified extras, dropping what `connect()` would 
ignore?



##########
providers/microsoft/mssql/docs/connections/mssql.rst:
##########
@@ -74,3 +74,46 @@ If serializing with JSON:
         "port": 1433,
         "schema": "database_name"
     }'
+
+Choosing the DBAPI driver
+-------------------------
+
+By default the hook connects with `pymssql 
<https://pypi.org/project/pymssql/>`__. To use Microsoft's

Review Comment:
   This section explains how to switch but not why someone would. Could it open 
with what mssql-python gives you over pymssql (Entra ID auth, Microsoft's 
bundled driver instead of FreeTDS) and what changes when you switch, like `%s` 
SQL needing a rewrite?



##########
providers/microsoft/mssql/src/airflow/providers/microsoft/mssql/hooks/mssql.py:
##########
@@ -98,10 +157,20 @@ def get_sqlalchemy_connection(
         engine = self.get_sqlalchemy_engine(engine_kwargs=engine_kwargs)
         return engine.connect(**(connect_kwargs or {}))
 
-    def get_conn(self) -> PymssqlConnection:
+    def get_conn(self) -> Any:
+        """Return a ``pymssql`` or ``mssql_python`` connection object, 
depending on ``dbapi_driver``."""
+        if self.dbapi_driver == self.DBAPI_DRIVER_MSSQL_PYTHON:
+            return self._get_mssql_python_conn()
+        return self._get_pymssql_conn()
+
+    def _get_pymssql_conn(self) -> PymssqlConnection:
         """Return ``pymssql`` connection object."""
+        import pymssql

Review Comment:
   pymssql is still a required dependency, so I don't think this needs to move 
into the method. Can it stay at module level?



##########
providers/microsoft/mssql/docs/connections/mssql.rst:
##########
@@ -74,3 +74,46 @@ If serializing with JSON:
         "port": 1433,
         "schema": "database_name"
     }'
+
+Choosing the DBAPI driver
+-------------------------
+
+By default the hook connects with `pymssql 
<https://pypi.org/project/pymssql/>`__. To use Microsoft's
+`mssql-python <https://pypi.org/project/mssql-python/>`__ driver instead, 
install the extra:
+
+.. code-block:: bash
+
+    pip install 'apache-airflow-providers-microsoft-mssql[mssql-python]'
+
+and set ``dbapi_driver`` to ``mssql_python`` in the connection extra. As a 
URI, the extras go in as query parameters:
+
+.. code-block:: bash
+
+    export 
AIRFLOW_CONN_MSSQL_DEFAULT='mssql://username:[email protected]:1433/database_name?dbapi_driver=mssql_python&TrustServerCertificate=yes'
+
+If serializing with JSON:
+
+
+.. code-block:: bash
+
+    export AIRFLOW_CONN_MSSQL_DEFAULT='{
+        "conn_type": "mssql",
+        "login": "username",
+        "password": "password",
+        "host": "server.com",
+        "port": 1433,
+        "schema": "database_name",
+        "extra": {"dbapi_driver": "mssql_python", "TrustServerCertificate": 
"yes"}
+    }'
+
+Notes
+~~~~~
+
+* ``dbapi_driver`` accepts ``pymssql`` (the default) or ``mssql_python`` and 
is case-insensitive.
+* Additional driver options, such as ``Encrypt``, ``TrustServerCertificate`` 
and
+  ``Authentication``, can be supplied through connection extras.
+* The default SQLAlchemy scheme is ``mssql+mssqlpython``, which requires 
SQLAlchemy 2.1

Review Comment:
   constraints-main pins SQLAlchemy 2.0.57 (3.2.0 pins 2.0.48), and Airflow 
2.11, which this provider still supports, caps it below 2.0. So on a 
constrained install the default scheme makes `get_sqlalchemy_engine()` fail 
with `NoSuchModuleError`. Can this note say that `get_conn()` works there but 
the SQLAlchemy paths need an upgrade past the constraints?



##########
providers/microsoft/mssql/tests/unit/microsoft/mssql/hooks/test_mssql.py:
##########
@@ -403,3 +403,157 @@ def test_get_extra(self, get_connection, 
mssql_connections):
 
         hook = MsSqlHook()
         assert hook.get_connection().extra
+
+
+def _mssql_hook(extra: str = "{}") -> MsSqlHook:

Review Comment:
   `MsSqlHook(connection=conn)` works here (`DbApiHook` pops `connection` from 
kwargs), which drops the unspecced `Mock` and the `type: ignore`. Could the 
helper also take `port`, so one test covers a connection without one? Nothing 
pins the `if server and conn.port` guard today, and without it the driver gets 
`Server=localhost,None`.



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: [email protected]

For queries about this service, please contact Infrastructure at:
[email protected]

Reply via email to