You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@airflow.apache.org by "RaphCodec (via GitHub)" <gi...@apache.org> on 2026/10/08 00:27:08 UTC

[I] Add mssql_python to the mssql provider [airflow]

RaphCodec opened a new issue, #74432:
URL: https://github.com/apache/airflow/issues/74432

   ### Description
   
   With the release of sqlalchemy 2.1.0 Microsoft's [mssql_python](https://github.com/microsoft/mssql-python) library is now a [supported dialect](https://docs.sqlalchemy.org/en/21/dialects/mssql.html#module-sqlalchemy.dialects.mssql.mssqlpython) of sqlalchemy.  Prior to the release of version 2.1.0, there wasn't a great alternative to pymssql for the Airflow MSSQL provider (aside from the odbc provider), as sqlalchemy did have native support for Microsoft's mssql-python driver.
   
   With this addtion Airflow users would ideally have the option to select what dialect (pymssql or mssql_python) they would like to use with the mssql provider.
   
   ### Use case/motivation
   
   The mssql_python library has some great features/benefits that can be beneficial to Airflow users if it is integrated into the mssql provider.
   
   These include but are not limited to:
   - DDBC driver for connecting to the database
   - better integration with Entra ID/Azure Auth
   - native Bulk copy methods for quick data ingestion
   
   ### Related issues
   
   I'm not sure how sqlalchemy integration issues shown in https://github.com/apache/airflow/pull/73865 may impact this feature request.  Based on that PR it seems ike adding sqlachemy 2.1.0 to the dependencies may not be possible right now without removing pymssql.
   
   I'm not sugessting remove pymssql support from the mssql provider as that would cause breaking changes.  Rather adding an option to choose between the pymssql and mssql_python dialects would be ideal.
   
   Maybe this request will need wait for a later release of sqlalchemy that doesn't break the existing pymssql functionality.
   
   ### Are you willing to submit a PR?
   
   - [ ] Yes I am willing to submit a PR!
   
   ### Code of Conduct
   
   - [x] I agree to follow this project's [Code of Conduct](https://github.com/apache/airflow/blob/main/CODE_OF_CONDUCT.md)
   


-- 
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: commits-unsubscribe@airflow.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [I] Add mssql_python to the mssql provider [airflow]

Posted by "RehanAhmad25 (via GitHub)" <gi...@apache.org>.
RehanAhmad25 commented on issue #74432:
URL: https://github.com/apache/airflow/issues/74432#issuecomment-6066476850

   Hey @RaphCodec , I'd like to take this one if that's okay.
   
   Two things I checked first:
   
   - This doesn't look blocked on SQLAlchemy anymore. #73865 only excluded 2.1.0 (the sdist had a duplicate mssql-pymssql extra that uv couldn't parse). On main, airflow-core allows sqlalchemy>=2.0.50 with no exclusion, and today's constraints-main resolves SQLAlchemy 2.1.3 together with pymssql 2.4.2.
   - MsSqlHook already reads sqlalchemy_scheme from the constructor or the connection extra, so the SQLAlchemy engine side should work with the mssql+mssqlpython scheme on 2.1+. The hardwired part is the raw DBAPI path: get_conn() calls pymssql.connect(...), set_autocommit/get_autocommit use pymssql specific calls, pymssql is imported at module level, and it's a hard dependency of the provider.
   
   What I'd propose:
   
   1. Add a driver option on the connection extra (pymssql or mssql_python). pymssql stays the default so nothing changes for existing users.
   2. Make the pymssql import lazy and add mssql-python as an optional extra of the provider.
   3. Add an mssql_python branch in get_conn() and the autocommit methods. It would build mssql-python's connection string from the Airflow connection fields, including the Entra auth keywords.
   4. Unit tests with both drivers mocked, plus docs.
   
   Does this shape work for you? In particular, connection extra vs a new hook argument for picking the driver, and optional extra vs a second regular dependency. Happy to change either before I write any code.


-- 
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: commits-unsubscribe@airflow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [I] Add mssql_python to the mssql provider [airflow]

Posted by "RaphCodec (via GitHub)" <gi...@apache.org>.
RaphCodec commented on issue #74432:
URL: https://github.com/apache/airflow/issues/74432#issuecomment-6084272908

   > Hey @RaphCodec , I'd like to take this one if that's okay.
   > 
   > Two things I checked first:
   > 
   > - This doesn't look blocked on SQLAlchemy anymore. #73865 only excluded 2.1.0 (the sdist had a duplicate mssql-pymssql extra that uv couldn't parse). On main, airflow-core allows sqlalchemy>=2.0.50 with no exclusion, and today's constraints-main resolves SQLAlchemy 2.1.3 together with pymssql 2.4.2.
   > - MsSqlHook already reads sqlalchemy_scheme from the constructor or the connection extra, so the SQLAlchemy engine side should work with the mssql+mssqlpython scheme on 2.1+. The hardwired part is the raw DBAPI path: get_conn() calls pymssql.connect(...), set_autocommit/get_autocommit use pymssql specific calls, pymssql is imported at module level, and it's a hard dependency of the provider.
   > 
   > What I'd propose:
   > 
   > 1. Add a driver option on the connection extra (pymssql or mssql_python). pymssql stays the default so nothing changes for existing users.
   > 2. Make the pymssql import lazy and add mssql-python as an optional extra of the provider.
   > 3. Add an mssql_python branch in get_conn() and the autocommit methods. It would build mssql-python's connection string from the Airflow connection fields, including the Entra auth keywords.
   > 4. Unit tests with both drivers mocked, plus docs.
   > 
   > Does this shape work for you? In particular, connection extra vs a new hook argument for picking the driver, and optional extra vs a second regular dependency. Happy to change either before I write any code.
   
   Hi.  I missed that the constraints file had sqlalchemy 2.1.3. That's great.
   
   Yes, what you're proposing is exactly how I was thinking this may be implemented. Excited to see how this will turn out!


-- 
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: commits-unsubscribe@airflow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [I] Add mssql_python to the mssql provider [airflow]

Posted by "Dev-iL (via GitHub)" <gi...@apache.org>.
Dev-iL commented on issue #74432:
URL: https://github.com/apache/airflow/issues/74432#issuecomment-6075579907

   Conceptually related: https://github.com/apache/airflow/pull/72000


-- 
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: commits-unsubscribe@airflow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [I] Add mssql_python to the mssql provider [airflow]

Posted by "RehanAhmad25 (via GitHub)" <gi...@apache.org>.
RehanAhmad25 commented on issue #74432:
URL: https://github.com/apache/airflow/issues/74432#issuecomment-6093622744

   Thanks @RaphCodec , and thanks @Dev-iL for the pointer to #72000. I'm going to start on this now.
   
   I'll keep driver selection on the connection extra only (no new hook argument), same convention as sqlalchemy_scheme, with pymssql staying the default. I'm planning to call the extra dbapi_driver since "driver" already means the ODBC driver name in OdbcHook, but happy to rename it if there's a better convention.
   
   I'll link the PR here once it's up.


-- 
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: commits-unsubscribe@airflow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [I] Add mssql_python to the mssql provider [airflow]

Posted by "RehanAhmad25 (via GitHub)" <gi...@apache.org>.
RehanAhmad25 commented on issue #74432:
URL: https://github.com/apache/airflow/issues/74432#issuecomment-6099368257

   @Dev-iL & @RaphCodec Opened a PR for this: https://github.com/apache/airflow/pull/74539
   
   pymssql stays the default, and mssql_python is chosen with a `dbapi_driver` connection extra. Tests use mocks only, so a check against a real SQL Server (especially the Entra ID `Authentication` options) would be welcome if someone has an instance handy.


-- 
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: commits-unsubscribe@airflow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org