Skip to content

Add mssql_python to the mssql provider #74432

Open
#74539
Open
Add mssql_python to the mssql provider#74432
#74539
Labels
kind:featureFeature Requestsneeds-triagelabel for new issues that we didn't triage yet

Description

@RaphCodec
RaphCodec
opened on Oct 8, 2026

Description

With the release of sqlalchemy 2.1.0 Microsoft's mssql_python library is now a supported dialect 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 #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

Activity

  1. RaphCodec
    added
    kind:featureFeature Requests
    needs-triagelabel for new issues that we didn't triage yet
    on Oct 8, 2026

  2. RehanAhmad25 commented on Oct 8, 2026

    @RehanAhmad25
    RehanAhmad25
    on Oct 8, 2026

    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. Exclude SQLAlchemy 2.1.0 #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.

  3. Dev-iL commented on Oct 9, 2026

    @Dev-iL
    Dev-iL
    on Oct 9, 2026
    Collaborator

    Conceptually related:

  4. RaphCodec commented on Oct 9, 2026

    @RaphCodec
    RaphCodec
    on Oct 9, 2026
    ContributorAuthor

    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. Exclude SQLAlchemy 2.1.0 #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!

  5. RehanAhmad25 commented on Oct 10, 2026

    @RehanAhmad25
    RehanAhmad25
    on Oct 10, 2026

    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.

  6. RehanAhmad25 commented on Oct 10, 2026

    @RehanAhmad25
    RehanAhmad25
    on Oct 10, 2026

    @Dev-iL & @RaphCodec Opened a PR for this: #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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    kind:featureFeature Requestsneeds-triagelabel for new issues that we didn't triage yet

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions