SameerMesiah97 commented on code in PR #74539:
URL: https://github.com/apache/airflow/pull/74539#discussion_r4238449931
##########
providers/microsoft/mssql/tests/unit/microsoft/mssql/hooks/test_mssql.py:
##########
@@ -381,3 +381,126 @@ def test_get_extra(self, get_connection,
mssql_connections):
hook = MsSqlHook()
assert hook.get_connection().extra
+
+
+def _mssql_hook(extra: str = "{}") -> MsSqlHook:
+ conn = Connection(
+ conn_id="mssql_default",
+ conn_type="mssql",
+ host="localhost",
+ login="user",
+ password="secret",
+ schema="db",
+ port=1433,
+ extra=extra,
+ )
+ hook = MsSqlHook()
+ hook.get_connection = mock.Mock(return_value=conn)
+ return hook
+
+
+class TestMsSqlHookDbapiDriver:
+ def test_default_driver_is_pymssql(self):
+ hook = _mssql_hook()
+ assert hook.dbapi_driver == "pymssql"
+ assert hook.sqlalchemy_scheme == "mssql+pymssql"
+ assert hook.placeholder == "%s"
+
+ @pytest.mark.parametrize(
+ "extra",
+ [
+ '{"dbapi_driver": "mssql_python"}',
+ '{"DBAPI_Driver": "MSSQL_Python"}',
+ ],
+ )
+ def test_mssql_python_driver_selection(self, extra):
+ hook = _mssql_hook(extra)
+ assert hook.dbapi_driver == "mssql_python"
+ assert hook.sqlalchemy_scheme == "mssql+mssqlpython"
+
+ def test_unsupported_driver_raises(self):
Review Comment:
I would extend this with parametrized cases for "", False, 0, a list and a
dict in addition to "odbc".
##########
providers/microsoft/mssql/src/airflow/providers/microsoft/mssql/hooks/mssql.py:
##########
@@ -60,13 +83,39 @@ def __init__(
self.schema = kwargs.pop("schema", None)
self._sqlalchemy_scheme = sqlalchemy_scheme
+ @property
+ def dbapi_driver(self) -> str:
+ """DBAPI driver, either ``pymssql`` (default) or ``mssql_python``,
from the connection extra."""
+ driver = str(self.connection_extra_lower.get("dbapi_driver") or
self.DBAPI_DRIVER_PYMSSQL).lower()
+ if driver not in self.SUPPORTED_DBAPI_DRIVERS:
+ raise ValueError(
+ f"Unsupported dbapi_driver {driver!r} in the connection extra.
"
+ f"Supported values: {', '.join(self.SUPPORTED_DBAPI_DRIVERS)}"
+ )
+ return driver
Review Comment:
This could be cleaner. Please see below:
```
driver = self.connection_extra_lower.get("dbapi_driver")
if driver is None:
return self.DBAPI_DRIVER_PYMSSQL
if not isinstance(driver, str) or driver.lower() not in
self.SUPPORTED_DBAPI_DRIVERS:
raise ValueError(
f"Unsupported dbapi_driver {driver!r} in the connection extra. "
f"Supported values: {',
'.join(sorted(self.SUPPORTED_DBAPI_DRIVERS))}"
)
return driver.lower()
```
##########
providers/microsoft/mssql/docs/connections/mssql.rst:
##########
@@ -74,3 +74,40 @@ 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:
+
+.. 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"}
+ }'
+
+Things to know when using ``mssql_python``:
+
+* ``dbapi_driver`` accepts ``pymssql`` (the default) or ``mssql_python``. The
name is case-insensitive.
+* Every other extra is passed to ``mssql_python.connect`` as a connection
string keyword, for example
+ ``Encrypt``, ``TrustServerCertificate`` or ``Authentication`` (for Microsoft
Entra ID sign-in). The
+ host and port are sent as ``Server=host,port``.
+* The SQLAlchemy scheme defaults to ``mssql+mssqlpython``, which needs
SQLAlchemy 2.1 or newer. It can still
+ be changed with the ``sqlalchemy_scheme`` extra or the hook argument.
+* The driver does not understand the plain ``%s`` parameter marker. Write SQL
parameters as ``?`` with a
+ list or tuple, or as ``%(name)s`` with a dict. The hook's default
placeholder for generated statements
+ is ``?`` and can be overridden with the ``placeholder`` extra.
+* ``mssql-python`` pools connections at the driver level by default.
Review Comment:
I think this could be clearer and more concise. Please see the below:
```
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
or newer. Override it using ``sqlalchemy_scheme`` in the connection extra
or hook argument.
* Use ``?`` placeholders with positional parameters or ``%(name)s`` with
named parameters.
Plain ``%s`` placeholders are not supported. The hook defaults to ``?``
for generated statements.
* Driver-level connection pooling is enabled by default.
```
##########
providers/microsoft/mssql/tests/unit/microsoft/mssql/hooks/test_mssql.py:
##########
@@ -381,3 +381,126 @@ def test_get_extra(self, get_connection,
mssql_connections):
hook = MsSqlHook()
assert hook.get_connection().extra
+
+
+def _mssql_hook(extra: str = "{}") -> MsSqlHook:
+ conn = Connection(
+ conn_id="mssql_default",
+ conn_type="mssql",
+ host="localhost",
+ login="user",
+ password="secret",
+ schema="db",
+ port=1433,
+ extra=extra,
+ )
+ hook = MsSqlHook()
+ hook.get_connection = mock.Mock(return_value=conn)
+ return hook
+
+
+class TestMsSqlHookDbapiDriver:
+ def test_default_driver_is_pymssql(self):
+ hook = _mssql_hook()
+ assert hook.dbapi_driver == "pymssql"
+ assert hook.sqlalchemy_scheme == "mssql+pymssql"
+ assert hook.placeholder == "%s"
+
+ @pytest.mark.parametrize(
+ "extra",
+ [
+ '{"dbapi_driver": "mssql_python"}',
+ '{"DBAPI_Driver": "MSSQL_Python"}',
+ ],
+ )
+ def test_mssql_python_driver_selection(self, extra):
+ hook = _mssql_hook(extra)
+ assert hook.dbapi_driver == "mssql_python"
+ assert hook.sqlalchemy_scheme == "mssql+mssqlpython"
+
+ def test_unsupported_driver_raises(self):
+ hook = _mssql_hook('{"dbapi_driver": "odbc"}')
+ with pytest.raises(ValueError, match="Unsupported dbapi_driver"):
+ _ = hook.dbapi_driver
+
+ def test_sqlalchemy_scheme_extra_wins_over_driver_default(self):
+ hook = _mssql_hook('{"dbapi_driver": "mssql_python",
"sqlalchemy_scheme": "mssql+pyodbc"}')
+ assert hook.sqlalchemy_scheme == "mssql+pyodbc"
+
+ def test_get_uri_strips_dbapi_driver(self):
+ hook = _mssql_hook('{"dbapi_driver": "mssql_python",
"TrustServerCertificate": "yes"}')
+ uri = hook.get_uri()
+ assert
uri.startswith("mssql+mssqlpython://user:secret@localhost:1433/db")
+ assert "dbapi_driver" not in uri
+ assert "TrustServerCertificate=yes" in uri
+
+ def test_mssql_python_placeholder_defaults_to_qmark(self):
+ assert _mssql_hook('{"dbapi_driver": "mssql_python"}').placeholder ==
"?"
+
+ def test_mssql_python_placeholder_extra_override(self):
+ hook = _mssql_hook('{"dbapi_driver": "mssql_python", "placeholder":
"%s"}')
+ assert hook.placeholder == "%s"
+
+ @mock.patch("airflow.providers.microsoft.mssql.hooks.mssql.import_module")
+ def test_get_conn_mssql_python(self, mock_import_module):
+ extra = (
+ '{"dbapi_driver": "mssql_python", "sqlalchemy_scheme":
"mssql+mssqlpython", '
+ '"placeholder": "?", "TrustServerCertificate": "yes", "Encrypt":
""}'
+ )
+ hook = _mssql_hook(extra)
+ conn = hook.get_conn()
+ mock_import_module.assert_called_once_with("mssql_python")
+ mock_import_module.return_value.connect.assert_called_once_with(
+ Server="localhost,1433",
+ Database="db",
+ UID="user",
+ PWD="secret",
+ TrustServerCertificate="yes",
+ )
+ assert conn is mock_import_module.return_value.connect.return_value
+
+ @mock.patch(
+ "airflow.providers.microsoft.mssql.hooks.mssql.import_module",
+ side_effect=ImportError("no module"),
+ )
+ def test_get_conn_mssql_python_not_installed(self, mock_import_module):
+ hook = _mssql_hook('{"dbapi_driver": "mssql_python"}')
+ with pytest.raises(ImportError, match="mssql-python"):
+ hook.get_conn()
+
+ @mock.patch("pymssql.connect")
+ def test_get_conn_pymssql_ignores_hook_only_extras(self, mock_connect):
+ hook = _mssql_hook('{"dbapi_driver": "pymssql", "sqlalchemy_scheme":
"mssql+pymssql", "timeout": 5}')
+ hook.get_conn()
+ mock_connect.assert_called_once_with(
+ server="localhost",
+ user="user",
+ password="secret",
+ database="db",
+ port="1433",
+ timeout=5,
+ )
+
+ def test_mssql_python_autocommit_uses_property(self):
Review Comment:
Can you cover `autocommit=False` as well?
--
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]
