python的to_sql

原吧网址

https://blog.csdn.net/qq_35866846/article/details/108121786

文章目录
to_sql结论
项目需求
代码比对
优劣对比
to_sql结论
可以对齐字段（dataframe的columns和数据库字段一一对齐）
可以缺少字段（dataframe的columns可以比数据库字段少）
不可以多出字段，会报错
if_exists='append’进行新增（bug：如果设置了PK,ignore 和 replace会报错）
一定要先创建好数据库，设置好格式，
否则使用if_exists='append’自动创建的字段格式乱七八糟
项目需求
excel表格按字段存入数据库，近几年的excel表格，业务系统几经迭代
所以每张表格的字段顺序不一致，甚至有的字段已经不需要剔除了
我本以为python的to_sql是按顺序一一映射进mysql里，

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

Python：dataframe写入mysql时候，如何对齐DataFrame的columns和SQL的字段名？

当时找遍了百度、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')
1
2
3
4
5
自定义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()#断开连接
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
优劣对比
对比项 to_sql 自定义w_sql
运行速度 整表写入，运行速度快 一行行写入，运行速度特慢
新表创建 提前创建（格式问题，函数本身可以创建） 提前创建
字段对齐 可以对齐 可以对齐
字段多余 报错警示 报错警示
主键处理 不可以ignore 可以ignore
数据修改 不可以修改 replace替换
运行速度慢可以说是这个自定义函数唯一也是最大，甚至于说致命的劣势！

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

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

基本上没在使用过to_sql

如果不是碰到了那个她

一个上百万行的excel文件

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

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

总的来说，自定义的sql写入语句更灵活，但是相对于to_sql官方函数来说效率比较低下，如果参考下源码，或许可以优化下效率问题，如果有更好的想法，欢迎评论交流！
————————————————
版权声明：本文为CSDN博主「诡途」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/qq_35866846/article/details/108121786