ConnectorX与Polars是否支持Azure托管身份认证？

答案是支持，以下是两种适配你当前Polars+ConnectorX代码场景的具体实现方案：

方案一：直接使用ConnectorX风格连接串（无需额外依赖） #

ConnectorX从v0.3.0版本开始支持SQL Server的Azure AD托管身份认证，你可以直接修改连接字符串，移除用户名密码，添加对应认证参数即可：

修改后的代码示例 #

import os
from datetime import datetime, timedelta
from dotenv import load_dotenv
import polars as pl
from timeit import default_timer as timer

load_dotenv()

def extract_from_db():
    env_upper = os.environ["envUpp"]
    env_lower = env_upper.lower()
    host = f"my_host"
    port = "1433"
    db = f"nyd_db"
    view = "my.view"
    
    # 系统托管身份连接串（无需用户名密码）
    conn = f"mssql://{host}:{port}/{db}?encrypt=true&authentication=ActiveDirectoryMSI"
    
    # 如果是用户托管身份，需添加msi_client_id参数
    # msi_client_id = os.environ["MSI_CLIENT_ID"]
    # conn = f"mssql://{host}:{port}/{db}?encrypt=true&authentication=ActiveDirectoryMSI&msi_client_id={msi_client_id}"
    
    datetime_limit = str(datetime.today() - timedelta(days=365))[:-3]
    query = f"SELECT * from {view} WHERE RecordTime > '{datetime_limit}'"

    print(query)
    start = timer()
    data = pl.read_sql(query, conn)
    end = timer()
    print("time taken to load data into df=", timedelta(seconds=end - start))
    num_of_rows = data.shape[0]
    num_of_cols = data.shape[1]
    print(f"number of columns in df: {num_of_cols}")
    print(f"number of rows in df: {num_of_rows}")
    return data

注意事项 #

• 确保ConnectorX版本≥v0.3.0（可通过pip show connectorx查看Polars依赖的版本）
• 运行代码的环境（如Azure VM、App Service、AKS等）已配置对应托管身份，且该身份拥有目标SQL Server的访问权限

方案二：使用pyodbc配合托管身份（更灵活） #

如果需要更复杂的认证逻辑，可通过pyodbc创建带Azure托管身份的连接，再传给Polars的read_sql方法：

步骤1：安装依赖 #

pip install pyodbc azure-identity

修改后的代码示例 #

import os
import pyodbc
from datetime import datetime, timedelta
from dotenv import load_dotenv
import polars as pl
from timeit import default_timer as timer

load_dotenv()

def extract_from_db():
    env_upper = os.environ["envUpp"]
    env_lower = env_upper.lower()
    host = f"my_host"
    port = "1433"
    db = f"nyd_db"
    view = "my.view"
    
    # 构建pyodbc连接串（系统托管身份）
    pyodbc_conn_str = (
        f"DRIVER={{ODBC Driver 18 for SQL Server}};"
        f"SERVER={host},{port};"
        f"DATABASE={db};"
        f"ENCRYPT=yes;"
        f"AUTHENTICATION=ActiveDirectoryMSI"
    )
    
    # 用户托管身份需添加CLIENT ID
    # client_id = os.environ["MSI_CLIENT_ID"]
    # pyodbc_conn_str = (
    #     f"DRIVER={{ODBC Driver 18 for SQL Server}};"
    #     f"SERVER={host},{port};"
    #     f"DATABASE={db};"
    #     f"ENCRYPT=yes;"
    #     f"AUTHENTICATION=ActiveDirectoryMSI;"
    #     f"CLIENT ID={client_id}"
    # )
    
    # 创建pyodbc连接
    pyodbc_conn = pyodbc.connect(pyodbc_conn_str)
    
    datetime_limit = str(datetime.today() - timedelta(days=365))[:-3]
    query = f"SELECT * from {view} WHERE RecordTime > '{datetime_limit}'"

    print(query)
    start = timer()
    # 传入pyodbc连接对象
    data = pl.read_sql(query, pyodbc_conn)
    end = timer()
    print("time taken to load data into df=", timedelta(seconds=end - start))
    num_of_rows = data.shape[0]
    num_of_cols = data.shape[1]
    print(f"number of columns in df: {num_of_cols}")
    print(f"number of rows in df: {num_of_rows}")
    
    # 关闭连接
    pyodbc_conn.close()
    return data

注意事项 #

• 需安装ODBC Driver 18 for SQL Server（Azure环境通常已预装，本地环境需手动安装）
• 托管身份需拥有目标SQL数据库的db_datareader或对应数据读取权限

内容的提问来源于stack exchange，提问作者MPathan