专栏首页诡途的python路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里,

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

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替换

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

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

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

基本上没在使用过to_sql

如果不是碰到了那个她

一个上百万行的excel文件

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

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

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

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 02 复杂度分析_pythoner学习数据结构与算法系列

    设计算法时,时间复杂度要比空间复杂度更容易出问题,所以一般情况一下我们只对时间复杂度进行研究。一般面试或者工作的时候没有特别说明的话,复杂度就是指时间复杂度...

    诡途
  • python 文件管理神器os.walk-文件指定日期整理程序

    诡途
  • 01数据结构与算法总览_pythoner学习数据结构与算法系列

    可以简单理解为: 当一个一维的链表的分叉有两个的时候, 它就变成了一个二维的数据结构,相当于树结构

    诡途
  • Python sql注入 过滤字符串的非法字符实例

    以上这篇Python sql注入 过滤字符串的非法字符实例就是小编分享给大家的全部内容了,希望能给大家一个参考。

    砸漏
  • Oracle AWR 阙值影响历史执行计划

          最近有网友提到为什么在dba_hist_sql_plan中无法查看到sql语句的历史执行计划,对于这个问题是由于缺省情况下,Oracle 设定的阙值...

    Leshami
  • Mysql学习笔记(三) - Sql中的安全问题

    很多时候开发人员只关系系统的功能的实现,很多系统开发人员甚至sql的安全全然不知,那么在开发中的sql具体有哪些注意点?这里我们就跟随笔者一起看看sql注入和相...

    程序员_备忘录
  • python实现一次性封装多条sql语句(begin end)

    因为业务需求,优化模型运行时间。考虑到sql语句每一次执行都要建立连接,查询,获取数据耗时过多。就想到将sql一起提交上去运行,能够节省很多时间。原本1.6-2...

    砸漏
  • 从python读取sql的实例方法

    以上就是从python读取sql的实例方法的详细内容,更多关于如何从python读取sql的资料请关注ZaLou.Cn其它相关文章!

    砸漏
  • pl sql 查看历史执行过的sql记录

    现在越来越多人用plsql 查询和执行sql,因为该工具很方便,不仅可以执行sql、以及命令窗口,但是呢,有时候我们执行完sql,可能忘记保存或者当时觉得可能不...

    小小鱼儿小小林
  • sql格式化工具

    该工具支持oracle、mysql、sql server等关系型数据库,能让你看到sql美化后的清晰的结构,可运用于对复杂SQL语句的分析或者是程序代码优化上,...

    ixiaoyang8

扫码关注云+社区

领取腾讯云代金券