pandas读取上G CSV文件包含空值直连数据库导入(空值和大批量数据解决方案)

最新推荐文章于 2024-08-12 09:00:44 发布

laoli815

最新推荐文章于 2024-08-12 09:00:44 发布

阅读量616

点赞数

分类专栏： pandas 文章标签： pandas 数据库

本文链接：https://blog.csdn.net/laoli815/article/details/127654342

版权

pandas 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

在遇到pandas的df.to_sql方法在导入大数据到SQL Server时失败后，采用了一种替代方案。通过设置read_csv的chunksize参数，逐块处理大CSV文件，并处理其中的NaN值，转换为MySQL可识别的类型。然后，通过数据库连接执行executemany方法批量插入数据。此方法在MySQL和SQL Server中都得到了验证，提升了大批量数据导入的性能。

摘要由CSDN通过智能技术生成

事件背景：
最近碰到一个问题，python 使用了SQLAlchemy 创建engine，但是到了df.to_sql(sqlserver服务器表) 却失败了，折腾了大半天也没找到解决办法，最后寻找替代方法，pandas读取csv文件，然后直接通过数据库链接执行executemany方法导入到数据库，但是csv文件比较大都上亿条，服务器内存也吃消，而且一次导入导入太多数据库不提交，数据库undo也可能被撑满，业务无法运行
解决方案：
本来df.to_sql()是可以指定导入多少数据进行提交，但是本次to_sql无法使用，这就没招了，很简单几句代码的事情要自己另外想办法搞定，使用to_sql方法也不用考虑里面的NAN值问题。只能采用最原始暴力直连数据库来解决
考虑到里面有空值nan的的问题，mysql识别不了，做了改进直接上代码，本次此时方案基于MySQL 进行测试，此方法在SQLSERVER经验证同样可以使用

import pandas as pd
import  time
import  numpy as np
pd.set_option('max_colwidth',200)
#显示所有列，把行显示设置成最大
pd.set_option('display.max_columns', None)
#显示所有行，把列显示设置成最大
import pymysql
conn = pymysql.connect(
    host='**',  # 连接名称，默认127.0.0.1
    user='**',  # 用户名
    passwd='**',  # 密码
    port=3306,  # 端口，默认为3306
    db='**',  # 数据库名称
    charset='utf8',  # 字符编码
)
cur=conn.cursor()
start=time.perf_counter()
pd.set_option('display.max_rows', None)
columns=pd.read_csv('Query_10000.csv').columns
df=pd.read_csv('Query_10000.csv',iterator=True,chunksize=5000,encoding='utf-8')
for chunk in df:
    dfmid = pd.DataFrame(columns=columns)
    dfmid=pd.concat([dfmid,chunk])
    dfmid = dfmid.where(pd.notnull(dfmid), None)
    # print(dfmid,dfmid.values.tolist())
    sql="insert into sample1(message_no,outbreak_date,local_id,equip_no,signal_no,message_type,message_kind, alarm_level,outbrk_return_kind,ai_detect_value,limit_value,unit,pickup_dropdn_msg,local_name,equip_name,signal_name,error_code,mail_flag,mail_send_flag,message_flag,confirm_flag,regist_date) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
    cur.executemany(sql,dfmid.values.tolist())
    conn.commit()
cur.close()
end=time.perf_counter()
print(end-start)

注意事项：
1.read_csv里面加上了chunksize 这时候df就已经不在是Dataframe类型了，我们使用f.where(pd.notnull(dfmid), None)这个命令会报错，所以我创建一个空的dataframe 然后和df进行合并成一个新的dataframe，这样才能对里面nan值进行处理，变成mysql能识别类型
2.sql语句中，字段名字都不能加单引号不然会报错

结论：
批量插入性能会很强，通过end-start就可以验证