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]