python to sql_python的to_sql那点儿事

to_sql结论可以对齐字段（dataframe的columns和数据库字段一一对齐）

可以缺少字段（dataframe的columns可以比数据库字段少）

不可以多出字段，会报错

if_exists='append’进行新增（bug：如果设置了PK,ignore 和 replace会报错）

一定要先创建好数据库，设置好格式，

否则使用if_exists='append’自动创建的字段格式乱七八糟

项目需求excel表格按字段存入数据库，近几年的excel表格，业务系统几经迭代

所以每张表格的字段顺序不一致，甚至有的字段已经不需要剔除了

我本以为python的to_sql是按顺序一一映射进mysql里，

于是便有了下面这篇特憨的一篇的文章：

当时找遍了百度、CSDN都没找到解决办法，

偶然间灵感乍现的思路，还以为自己开创了先河

现在想想当时多么自豪，现在就有多羞愧！

不过几经迭代，倒也帮我解决了to_sql不能 ignore和replace的问题

代码比对

to_sql代码#构建数据库连接

engine=create_engine(f'mysql+pymysql://{user}:{passwd}@{host}:3306/{db}')

#可以对齐字段，以及缺少字段；不可以增加字段

data.to_sql(sql_name,engine,index=False,if_exists='append')

自定义w_sql (迭代后版本)# 定义写入数据库函数

def w_sql(sql_name,data,db_name,host=host,user=user,passwd=passwd):

zd=""

for j in data.columns:

zd=zd+j+","

connent = pymysql.connect(host=host, user=user, passwd=passwd, db=db_name, charset='utf8mb4') #连接数据库

cursor = connent.cursor()#创建游标

for i in data.values:

va=""

for j in i:

if pd.isnull(j):

va=va+","+'null' #缺失值判断和转换

else:

va=va+","+'"'+str(j)+'"'

# sql=u"""insert ignore into %s (%s) values(%s)"""%(sql_name,zd[:-1],va[1:])

sql=u"""replace into %s (%s) values(%s)"""%(sql_name,zd[:-1],va[1:])

cursor.execute(sql)

connent.commit() #提交事务

cursor.close()#关闭游标

connent.close()#断开连接

优劣对比

对比项to_sql自定义w_sql运行速度整表写入，运行速度快一行行写入，运行速度特慢

新表创建提前创建（格式问题，函数本身可以创建）提前创建

字段对齐可以对齐可以对齐

字段多余报错警示报错警示

主键处理不可以ignore可以ignore

数据修改不可以修改replace替换

运行速度慢可以说是这个自定义函数唯一也是最大，甚至于说致命的劣势！

这也是我为什么会发现我这么憨的原因

毕竟自从有了这个自定义函数，以往都是无往而不利的，

基本上没在使用过to_sql

如果不是碰到了那个她

一个上百万行的excel文件

我恐怕这辈子都不会发现 to_sql的秘密

庆幸的是相比去年多少进步了点儿，知道测试了，不只是凭感觉了

总的来说，自定义的sql写入语句更灵活，但是相对于to_sql官方函数来说效率比较低下，如果参考下源码，或许可以优化下效率问题，如果有更好的想法，欢迎评论交流！