support new MSFT driver, when it's usable

[zzzeek]

Describe the use case

https://github.com/microsoft/mssql-python

Databases / Backends / Drivers targeted

https://github.com/microsoft/mssql-python

Example Use

https://github.com/microsoft/mssql-python

Additional context

No response

[zzzeek]

let's see how hard preliminary support is.

[sqla-tester]

Mike Bayer has proposed a fix for this issue in the main branch:

WIP: mssql-python https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/6149

[zzzeek]

pending issues for basic functionality for the new driver have gone unanswered/untriaged for two days: microsoft/mssql-python#241 microsoft/mssql-python#242 so it looks like this driver probably wont be viable for 2.1

[gordthompson]

Just a note for future reference:

The docs for this new DBAPI say that connection pooling is enabled by default, so we should be mindful of

#11444

specifically

#11444 (comment)

[gordthompson]

Further to my previous comment:

from time import sleep

import mssql_python

# (uncomment the following line for non-default behaviour)
# mssql_python.pooling(enabled=False)

def get_connection():
    return mssql_python.connect(
        "SERVER=192.168.0.199;"
        "DATABASE=test;"
        "UID=scott;PWD=tiger^5HHH;"
        "TrustServerCertificate=yes;"
    )

cnxn = get_connection()
print(f"mssql_python.pooling enabled: {cnxn._pooling}")

crsr = cnxn.cursor()
if crsr.execute("SELECT name from sys.tables where name = '_TestTable'").fetchval():
    crsr.execute("""
            ALTER TABLE [dbo].[_TestTable] SET ( SYSTEM_VERSIONING = OFF)
        """)

    crsr.execute("""DROP TABLE [dbo].[_TestTable]""")
    crsr.execute("""DROP TABLE [dbo].[_TestTableHistory]""")
    cnxn.commit()
crsr.execute("""
CREATE TABLE [dbo].[_TestTable](
    [Id] [int] NOT NULL,
    [UserName] [nvarchar](50) NOT NULL,
    [StartTime] [datetime2](7) GENERATED ALWAYS AS ROW START NOT NULL,
    [EndTime] [datetime2](7) GENERATED ALWAYS AS ROW END NOT NULL,
PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY],
    PERIOD FOR SYSTEM_TIME ([StartTime], [EndTime])
) ON [PRIMARY]
WITH
(
SYSTEM_VERSIONING = ON ( HISTORY_TABLE = [dbo].[_TestTableHistory] )
)
""")
crsr.execute("INSERT INTO [dbo].[_TestTable] ([Id], [UserName]) VALUES (1, N'foo')")
crsr.close()
cnxn.commit()

for mode in ["emulate QueuePool", "emulate NullPool"]:
    print(f"\n[{mode}]")
    latest_tnx_end = cnxn.execute("SELECT CURRENT_TIMESTAMP").fetchval()
    cnxn.rollback()
    sleep(11)
    if mode == "emulate QueuePool":
        cnxn.rollback()
    else:
        cnxn.close()
        cnxn = get_connection()
    crsr = cnxn.cursor()
    crsr.execute("UPDATE [dbo].[_TestTable] SET [UserName] = [UserName] + N'x' WHERE [Id] = 1")
    start_time = crsr.execute("SELECT StartTime FROM [dbo].[_TestTable] WHERE [Id] = 1").fetchval()
    print(f"{latest_tnx_end=}")
    print(f"    {start_time=}")
    print(f"    difference={start_time - latest_tnx_end}")

mssql_python.pooling enabled (default)

mssql_python.pooling enabled: True

[emulate QueuePool]
latest_tnx_end=datetime.datetime(2025, 10, 19, 23, 8, 0, 300000)
    start_time=datetime.datetime(2025, 10, 19, 23, 8, 0, 341642)
    difference=0:00:00.041642

[emulate NullPool]
latest_tnx_end=datetime.datetime(2025, 10, 19, 23, 8, 11, 393000)
    start_time=datetime.datetime(2025, 10, 19, 23, 8, 22, 417865)
    difference=0:00:11.024865

mssql_python.pooling disabled

[emulate QueuePool]
latest_tnx_end=datetime.datetime(2025, 10, 19, 23, 9, 5, 670000)
    start_time=datetime.datetime(2025, 10, 19, 23, 9, 5, 823361)
    difference=0:00:00.153361

[emulate NullPool]
latest_tnx_end=datetime.datetime(2025, 10, 19, 23, 9, 16, 883000)
    start_time=datetime.datetime(2025, 10, 19, 23, 9, 27, 970803)
    difference=0:00:11.087803