无法将Excel数据插入MS SQL数据库，求动态插入实现方案

需求说明 #

• SQL Server中已创建两张表dbo.headers和dbo.data，结构均为：filename varchar(10) + column1到column200（各为varchar(10)）
• 读取Excel文件后，动态根据Excel的列数，将Excel表头+文件名插入headers表，Excel行数据+文件名插入data表（比如Excel有10列，就对应SQL表的column1到column10）

原代码问题 #

原代码处理data表时，错误复用了表头的构造逻辑，导致插入的不是Excel里的实际行数据，同时列映射未正确对应SQL表的column序列。

修正后的完整代码 #

import pandas as pd
from sqlalchemy import create_engine

server = 'DESKTOP-VFQ45HB\\SQLEXPRESS'
database = 'test'
driver = 'ODBC Driver 17 for SQL Server'
connection_string = f'mssql+pyodbc://{server}/{database}?trusted_connection=yes&driver={driver}'

def insert_excel_data_to_sql(filename, sheet_name, connection_string):
    # 读取Excel到DataFrame
    df = pd.read_excel(filename, sheet_name=sheet_name)
    column_count = len(df.columns)
    
    # 创建SQL引擎
    engine = create_engine(connection_string)

    # 1. 插入表头到headers表
    # 构造headers表的一行数据：filename + column1~columnN对应Excel表头
    header_data = {'filename': [filename.split('/')[-1]]}  # 可选：只存文件名而非完整路径
    for idx, col_name in enumerate(df.columns):
        header_data[f'column{idx+1}'] = [col_name]
    df_headers = pd.DataFrame(header_data)
    df_headers.to_sql('headers', con=engine, if_exists='append', index=False)

    # 2. 插入数据到data表
    # 给原数据添加filename列
    df_data = df.copy()
    df_data['filename'] = filename.split('/')[-1]  # 同样可选只存文件名
    
    # 构造SQL表的列映射：Excel的列对应SQL的column1~columnN，加上filename
    sql_columns = ['filename'] + [f'column{idx+1}' for idx in range(column_count)]
    # 重新排列DataFrame的列顺序，匹配SQL表的列顺序
    df_data = df_data[df.columns.tolist() + ['filename']]
    df_data.columns = sql_columns
    
    # 插入数据
    df_data.to_sql('data', con=engine, if_exists='append', index=False)

# 示例调用
filename = 'C:/Users/PC/Documents/test.xlsx'
sheet_name = 'Sheet1'
insert_excel_data_to_sql(filename, sheet_name, connection_string)

关键修复点 #

• 表头插入逻辑：正确构造headers表的一行数据，确保column1到columnN对应Excel的表头名称，同时携带文件名
• 数据插入逻辑：基于原Excel数据创建副本，添加filename列后，将Excel的列名映射为SQL表的column1到columnN，保证列顺序和SQL表一致
• 列匹配处理：通过重命名DataFrame的列，让其完全匹配SQL表的列名，避免to_sql时出现列不匹配的错误

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