python根据excel生成insert sql语句

需要先安装pandas

import pandas as pd

path = r'D:\Desktop\\新建文件夹\test.xlsx'
data = pd.read_excel(path)
table_name = 'table_name '

columns = ', '.join(data.columns)

with open('insert_statements.txt', 'w', encoding='utf-8') as f:
    insert_statements = []
    for index, row in data.iterrows():
        valuesList = []
        for col_name, value in row.items():
            if pd.isna(value):
                value = 'NULL'
                valuesList.append(value)
            else:
                if isinstance(value, (int, float)):
                    # 如果是数值类型
                    value = str(value)
                    valuesList.append(str(value))
                else:
                    # 如果是字符串类型
                    value = str(value).strip()
                    value = value.replace("'", "\\'").replace("\r\n", " ").replace("\n", " ")
                    value = f"'{value}'"
                    valuesList.append(value)
        values = ', '.join(valuesList)
        insert_statement = f"INSERT INTO {table_name} ({columns}) VALUES ({values});"
        insert_statements.append(insert_statement)
        f.writelines(insert_statement)

# 打印出来看看效果
for statement in insert_statements:
    print(statement)