python的to_sql那点儿事

诡途

发布于 2020-10-16 10:01:30

1.8K0

发布于 2020-10-16 10:01:30

文章被收录于专栏：诡途的python路诡途的python路

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')

自定义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替换