RehanAhmad25 opened a new pull request, #74539: URL: https://github.com/apache/airflow/pull/74539
closes: #74432 ## What `MsSqlHook` can now connect with Microsoft's `mssql-python` driver instead of `pymssql`. The driver is chosen with a new `dbapi_driver` connection extra (`pymssql` or `mssql_python`, case-insensitive). `pymssql` stays the default, so existing connections behave exactly as before. An unsupported value raises a `ValueError`. ## How it works With `dbapi_driver` set to `mssql_python`: - `get_conn()` builds the connection with `mssql_python.connect(...)`. The host and port are sent as `Server=host,port`, and the schema, login and password map to `Database`, `UID` and `PWD`. - Other connection extras (for example `Encrypt`, `TrustServerCertificate`, `Authentication`) are passed to the driver as keyword arguments. The hook's own extras (`dbapi_driver`, `sqlalchemy_scheme`, `placeholder` and the other `DbApiHook` format extras) are not passed on, and empty values are dropped. - The SQLAlchemy scheme defaults to `mssql+mssqlpython`. `sqlalchemy_scheme` still overrides it. `get_uri()` strips `dbapi_driver` from the query string. - The default placeholder is `?`, because mssql-python does not accept a plain `%s`. The `placeholder` extra still overrides it. - Result rows are converted to plain tuples, since mssql-python returns its own `Row` objects. - Autocommit goes through the base class behaviour, since the driver exposes a settable `autocommit` property. - If the package is missing, the error tells the user to install the `mssql-python` extra. `pymssql` is now imported inside the pymssql code path, not at module level. It is still a required dependency of the provider. Making it optional would be a separate change. ## Where - `providers/microsoft/mssql/src/airflow/providers/microsoft/mssql/hooks/mssql.py`: the hook changes - `providers/microsoft/mssql/tests/unit/microsoft/mssql/hooks/test_mssql.py`: new tests - `providers/microsoft/mssql/docs/connections/mssql.rst`: new "Choosing the DBAPI driver" section - `providers/microsoft/mssql/pyproject.toml`: new optional extra `mssql-python` (`mssql-python>=1.15.0`) - `providers/microsoft/mssql/docs/index.rst`: extras table, updated by the repo's hook - `uv.lock`: regenerated with `uv 0.13.0` for the new extra (adds `mssql-python` and `mssql-python-odbc`) ## Notes for reviewers - I picked `>=1.15.0` as the floor because it is the version I checked the driver's behaviour against. Happy to loosen it if you prefer. - The SQLAlchemy dialect for mssql-python needs SQLAlchemy 2.1 or newer, and the docs say so. ## Testing I added 14 new test cases for driver selection and validation, scheme and placeholder defaults, `get_uri`, the arguments passed to `connect`, the missing-package error, autocommit and row conversion. The existing pymssql tests pass unchanged (35 passed in Breeze). The prek hooks, including ruff, mypy for providers and the provider.yaml check, pass locally. Both drivers are mocked in the tests, so I have not run this against a real SQL Server yet. The Microsoft Entra ID `Authentication` options in particular are untested, so a check on a real instance would be welcome. Related: #72000 --- ##### Was generative AI tooling used to co-author this PR? - [X] Yes (Claude) Generated-by: Claude (Anthropic) following [the guidelines](https://github.com/apache/airflow/blob/main/contributing-docs/05_pull_requests.rst#gen-ai-assisted-contributions) -- 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]
