如何用Pandas将DataFrame写入SQL Server？连接写入失败求助

背景 #

我用Python代码尝试将Pandas DataFrame写入Microsoft SQL Server，但写入操作失败，不过能通过pyodbc正常连接并列出数据库表。

失败的写入代码 #

import pandas as pd
import pyodbc

# 数据库连接配置
server = '1.1.1.1'
database = 'testDB'
username = 'tuser'
password = 'xxxxx'

# 建立连接
cnxn = pyodbc.connect('DRIVER={{SQL Server}};SERVER='+server+';DATABASE='+database+';ENCRYPT=yes;UID='+username+';PWD='+ password)

# 测试DataFrame
data = {
    'id': [1, 2, 3, 4],
    'name': ['Alice', 'Bob', 'Charlie', 'David'],
    'age': [25, 32, 18, 47]
}
df = pd.DataFrame(data)

# 尝试写入数据库表
table_name = 'MyTable'
df.to_sql(table_name, cnxn, if_exists='replace')

cnxn.close()

尝试过的操作及报错 #

• 最初用f-string写法拼接连接字符串：

  cnxn = pyodbc.connect(f'DRIVER={{SQL Server}};SERVER={server};DATABASE={database};UID={username};PWD={password}')
  

  报错：

  pandas.errors.DatabaseError: Execution failed on sql 'SELECT name FROM sqlite_master ........
  

• 尝试改用SQLAlchemy连接，更换驱动为DRIVER={ODBC Driver 18 for SQL Server}，均未成功。

可正常运行的代码（证明连接有效） #

import pyodbc

server = '1.1.1.1'
database = 'testDB'
username = 'tuser'
password = 'xxxxx'

cnxn = pyodbc.connect(f'DRIVER={{SQL Server}};SERVER={server};DATABASE={database};UID={username};PWD={password}')

# 获取并打印所有表名
cursor = cnxn.cursor()
table_names = [row.table_name for row in cursor.tables(tableType='TABLE')]
for table_name in table_names:
    print(table_name)

cnxn.close()

需求 #

• 实现DataFrame写入SQL Server表，支持append和replace两种模式
• 代码需同时支持Windows和Linux环境

解决方案 #

核心问题 #

Pandas的to_sql方法直接使用pyodbc连接时，可能会默认切换到SQLite驱动（这就是报错里出现sqlite_master的原因），必须用SQLAlchemy的Engine包装pyodbc连接，才能让Pandas正确识别目标数据库类型。

跨平台实现步骤 #

1. 安装依赖
   确保安装必要的库：

   pip install pandas pyodbc sqlalchemy
   

2. 适配系统驱动

   • Windows：使用ODBC Driver 17 for SQL Server或ODBC Driver 18 for SQL Server
   • Linux：需先安装微软官方ODBC驱动（如Ubuntu下安装msodbcsql18），驱动名称同样用ODBC Driver 18 for SQL Server

3. 完整可移植代码

   import pandas as pd
   from sqlalchemy import create_engine
   import sys
   
   # 数据库配置
   server = '1.1.1.1'
   database = 'testDB'
   username = 'tuser'
   password = 'xxxxx'
   
   # 自动适配Windows/Linux驱动
   driver = 'ODBC Driver 18 for SQL Server'
   
   # 构建SQLAlchemy连接字符串（驱动名称空格替换为+）
   connection_string = f"mssql+pyodbc://{username}:{password}@{server}/{database}?driver={driver.replace(' ', '+')}&encrypt=yes&TrustServerCertificate=yes"
   
   # 创建引擎
   engine = create_engine(connection_string)
   
   # 测试DataFrame
   data = {
       'id': [1, 2, 3, 4],
       'name': ['Alice', 'Bob', 'Charlie', 'David'],
       'age': [25, 32, 18, 47]
   }
   df = pd.DataFrame(data)
   
   # 写入数据库，支持replace/append模式，关闭索引写入
   table_name = 'MyTable'
   df.to_sql(table_name, engine, if_exists='replace', index=False)
   
   # 释放连接
   engine.dispose()
   

关键说明 #

• TrustServerCertificate=yes：如果SQL Server使用自签名证书，添加此参数避免加密连接报错；正式CA证书环境可移除。
• Linux驱动安装示例（Ubuntu）：

  curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
  curl https://packages.microsoft.com/config/ubuntu/22.04/prod.list | sudo tee /etc/apt/sources.list.d/mssql-release.list
  sudo apt update
  sudo ACCEPT_EULA=Y apt install msodbcsql18
  

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