使用SQLAlchemy的df.to_sql连接本地SQL Server遭拒绝，求助排查

操作步骤 #

尝试将pandas数据帧插入SQL Server的test1表，步骤如下：

1. 创建SQLAlchemy连接URL：

connection_url = URL.create(
    "mssql+pyodbc",
    username="",
    password="",
    host="localhost",
    port=1433,
    database="priority",
    query={
        "driver": "ODBC Driver 17 for SQL Server",
        "authentication": "ActiveDirectoryIntegrated",
    },
)

2. 创建引擎：

engine = sqlalchemy.create_engine(connection_url)

3. 执行插入操作：

df.to_sql('test1', engine, if_exists='replace')

报错信息 #

OperationalError: (pyodbc.OperationalError) ('08001', '[08001] [Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: No connection could be made because the target machine actively refused it.\r\n (10061) (SQLDriverConnect); [08001] [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0); [08001] [Microsoft][ODBC Driver 17 for SQL Server]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online. (10061)')

已尝试的排查措施 #

• 开放SQL端口1433，通过管理员命令行添加防火墙规则并确认端口已开放；
• 将host参数从localhost替换为127.0.0.1；
• 将host参数替换为SQL Server主机名（通过SELECT HOST_NAME()查询获取为LAPTOP-NumDig）；
• 将ODBC驱动更新至17版本；
• 将认证方式从"ActiveDirectoryIntegrated"替换为"Trusted_Connection": "yes"

此前成功的pyodbc连接代码 #

此前使用pyodbc可成功连接数据库并创建test1表：

conn = pyodbc.connect('Driver={SQL Server};' 
                      'Server=LAPTOP-NumDig;'
                      "Database=priority;"
                      'UID=;' # username
                      'PWD=;' # password
                      )
cursor = conn.cursor()

cursor.execute("""
CREATE TABLE test1 (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255)
);""")

环境版本 #

• VSCode 1.71.2
• Python 3.9.13
• Microsoft SQL Server 2019
• sqlalchemy 1.4.41
• pyodbc 4.0.34

解决建议 #

1. 基于已成功的pyodbc连接字符串构建SQLAlchemy引擎
   直接使用之前验证有效的连接参数创建SQLAlchemy引擎，避免URL构造时的参数冲突：

   import sqlalchemy
   
   # 复用成功的连接参数，指定ODBC Driver 17
   connection_str = (
       "Driver={ODBC Driver 17 for SQL Server};"
       "Server=LAPTOP-NumDig;"
       "Database=priority;"
       "Trusted_Connection=yes;"
   )
   engine = sqlalchemy.create_engine(f"mssql+pyodbc:///?odbc_connect={connection_str}")
   df.to_sql('test1', engine, if_exists='replace')
   

2. 检查SQL Server实例配置

   • 打开SQL Server配置管理器，确认SQL Server服务处于运行状态；
   • 检查SQL Server网络配置中对应实例的TCP/IP协议是否启用；
   • 若使用命名实例，确认实例名称正确，无需强制指定1433端口（命名实例默认使用动态端口）。

3. 调整SQLAlchemy URL构造参数
   由于使用Windows认证（Trusted Connection），无需传入username和password，直接使用已验证的主机名：

   from sqlalchemy.engine import URL
   
   connection_url = URL.create(
       "mssql+pyodbc",
       host="LAPTOP-NumDig",
       database="priority",
       query={
           "driver": "ODBC Driver 17 for SQL Server",
           "Trusted_Connection": "yes",
       },
   )
   engine = sqlalchemy.create_engine(connection_url)
   

4. 验证端口连通性
   使用PowerShell命令Test-NetConnection LAPTOP-NumDig -Port 1433测试端口是否可访问，若不通，需确认SQL Server是否监听1433端口，或是否有防火墙/安全软件拦截。

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