如何通过pyodbc将处理后的pandas DataFrame写回Access数据库

你可以根据数据量和使用场景选择以下两种方案，均兼容你现有的pyodbc连接逻辑。

方案1：Pandas原生to_sql写入（代码简洁，适配中小数据量） #

Pandas的to_sql方法不支持直接传入pyodbc连接对象写入Access，需要搭配SQLAlchemy创建数据库连接，步骤如下：

• 安装依赖：

pip install sqlalchemy sqlalchemy-access

• 完整读写、处理、写回示例代码：

import pyodbc
import pandas as pd
from sqlalchemy import create_engine, text

# 原有读取数据逻辑
connStr = (
    r"DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};"
    r"DBQ=C:\Users\A\Documents\Database3.accdb;"
    )
cnxn = pyodbc.connect(connStr)
sql = "Select * From Table1"
data = pd.read_sql(sql, cnxn)
cnxn.close()  # 读取完成后及时关闭连接，避免锁库

# 此处插入你的数据转换处理逻辑
# 注意：处理后的DataFrame字段名、数据类型需和目标表匹配，提前移除Access不支持的特殊字段名
processed_data = data  # 替换为你实际处理完成的DataFrame

# 创建SQLAlchemy写入连接
engine = create_engine(
    "access+pyodbc:///?odbc_connect={}".format(pyodbc.quote_plus(connStr))
)

# 按需选择写入模式
# --- 模式1：覆盖原有同名表 ---
with engine.connect() as conn:
    conn.execute(text("DROP TABLE IF EXISTS Table1"))
    conn.commit()
processed_data.to_sql("Table1", engine, if_exists="append", index=False)

# --- 模式2：向原有表追加新数据（要求DataFrame与原表结构完全一致）---
# processed_data.to_sql("Table1", engine, if_exists="append", index=False)

关键注意点：必须传入index=False参数，避免Pandas将DataFrame索引作为额外字段写入，触发字段不匹配报错。

方案2：pyodbc批量插入（性能更高，适配大数据量场景） #

如果数据量超过10万行，to_sql写入效率较低，可以直接通过pyodbc执行批量插入，无需额外安装SQLAlchemy相关依赖：

import pyodbc
import pandas as pd

# 原有读取数据逻辑
connStr = (
    r"DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};"
    r"DBQ=C:\Users\A\Documents\Database3.accdb;"
    )
cnxn = pyodbc.connect(connStr)
sql = "Select * From Table1"
data = pd.read_sql(sql, cnxn)

# 此处插入你的数据转换处理逻辑
processed_data = data  # 替换为你实际处理完成的DataFrame

# 构造插入语句，字段名加[]避免和Access保留字冲突
cols = ",".join([f"[{col}]" for col in processed_data.columns])
placeholders = ",".join(["?" for _ in processed_data.columns])
insert_sql = f"INSERT INTO Table1 ({cols}) VALUES ({placeholders})"

# 执行批量写入
cursor = cnxn.cursor()
try:
    # 如果需要覆盖原表，取消下两行注释即可
    # cursor.execute("DROP TABLE IF EXISTS Table1")
    # 此处补充对应建表语句，字段与processed_data完全一致
    cursor.executemany(insert_sql, processed_data.fillna(None).values.tolist())
    cnxn.commit()
    print("数据写入完成")
except Exception as e:
    cnxn.rollback()
    raise e
finally:
    cnxn.close()

常见问题规避 #

• 写入前将DataFrame中的NaN/NaT空值替换为None，Access对pandas的空值类型兼容性差，容易触发类型报错
• 字段名不要包含.、!、[]等Access不支持的特殊字符，存在这类字符提前重命名字段
• 写入操作完成后必须关闭连接，否则Access文件会被进程锁定，无法正常打开编辑

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