使用Python+pyodbc循环向Access数据库插入数据时遇语法错误求助

问题背景 #

使用Python将Excel数据写入MS Access时触发语法错误，原代码及报错如下：

原代码 #

df = pd.read_excel(path, sheet_name='Sheet1')
acc_cnxn = pyodbc.connect(conn_str)
acc_crsr = acc_cnxn.cursor()

for index, row in df.iterrows():
    acc_crsr.executemany("""INSERT INTO testing 
                        (
                            [Col. Version],
                            Version,Entity,
                            Segment,
                            LOB,
                            [Week Starting],
                            Week,
                            Day,
                            Date,
                            Interval,
                            [col beta],
                            [col alpha],
                        ) 
                        values
                        (
                            ?,
                            ?,
                            ?,
                            ?,
                            ?,
                            ?,
                            ?,
                            ?,
                            ?,
                            ?,
                            ?,
                            ?
                            );""", row.Col. Version, row.Version, row.Entity, row.Segment, row.LOB, row.Week Starting, row.Week, row.Day, row.Date, row.Interval, row.col beta, row.col alpha)

acc_crsr.commit()

报错信息 #

语法错误：无效语法

错误原因及修复步骤 #

• SQL语句存在多余逗号：INSERT列列表的最后一项[col alpha],末尾多了逗号，VALUES列表最后一个?后的逗号也不符合Access语法规范，需全部删除。
• 特殊列名访问方式错误：列名包含空格、点号时，不能用.直接访问（如row.Col. Version），必须通过方括号索引取值，格式为row['列名']。
• executemany方法误用：该方法用于批量插入多条数据，参数需为包含多组参数的序列；单条数据插入应使用execute方法，参数传入单条数据的元组。

修复后的代码 #

import pandas as pd
import pyodbc

df = pd.read_excel(path, sheet_name='Sheet1')
acc_cnxn = pyodbc.connect(conn_str)
acc_crsr = acc_cnxn.cursor()

# 修正SQL语句，移除多余逗号
insert_sql = """INSERT INTO testing 
                (
                    [Col. Version],
                    Version,
                    Entity,
                    Segment,
                    LOB,
                    [Week Starting],
                    Week,
                    Day,
                    Date,
                    Interval,
                    [col beta],
                    [col alpha]
                ) 
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);"""

for index, row in df.iterrows():
    # 用方括号访问特殊列名，组装参数元组
    params = (
        row['Col. Version'],
        row['Version'],
        row['Entity'],
        row['Segment'],
        row['LOB'],
        row['Week Starting'],
        row['Week'],
        row['Day'],
        row['Date'],
        row['Interval'],
        row['col beta'],
        row['col alpha']
    )
    # 单条插入使用execute方法
    acc_crsr.execute(insert_sql, params)

acc_crsr.commit()
# 关闭游标和连接，避免资源泄漏
acc_crsr.close()
acc_cnxn.close()

批量插入优化（针对大数据量） #

循环插入效率较低，可将整份DataFrame转换为参数列表后批量插入：

# 提取需要插入的列，转换为参数列表
params_list = df[
    ['Col. Version', 'Version', 'Entity', 'Segment', 'LOB', 'Week Starting',
     'Week', 'Day', 'Date', 'Interval', 'col beta', 'col alpha']
].values.tolist()

# 批量插入
acc_crsr.executemany(insert_sql, params_list)
acc_crsr.commit()

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