python 小工具

最新推荐文章于 2024-10-08 12:37:10 发布

transitory_truth

最新推荐文章于 2024-10-08 12:37:10 发布

阅读量128

点赞数 4

本文链接：https://blog.csdn.net/transitory_truth/article/details/140050606

版权

python 同时被 3 个专栏收录

3 篇文章 0 订阅

订阅专栏

pandas

2 篇文章 0 订阅

订阅专栏

excel

1 篇文章 0 订阅

订阅专栏

import pandas as pd

将excel文件内数据处理成批量插入SQL语句

def splicing2batch_insert_sql(file_path: str,sheet_name: str|int,table_name: str):
    df = pd.read_excel(file_path,sheet_name=sheet_name,skiprows=0, index_col=False,keep_default_na=True)
    cn = df.columns
    res = "INSERT INTO `{0}`(".format(table_name)
    for e in cn:
        res += ("`{0}`,").format(e)
    res = res[:-1]
    res+=") VALUES"
    for index, row in df.iterrows():
        ts = "("
        for e in cn:
            if pd.isna(row[e]):
                ts += "NULL,"
            elif not isinstance(row[e],(int,float)):
                ts += "'{0}',".format(str(row[e]))
            else:
                ts += str(row[e]) + ","
        res += ts[:-1]+"),"
    return res[:-1]+";"