用python导入20个G的json数据到Mysql数据库

整体思路参考资料：https://blog.csdn.net/layman2016/article/details/79252499

作业：有一个16个G的跟疫情相关的json新闻大数据（article.txt)，用python3将导入到数据库Mysql5.7，然后用可视化工具（我用的是R)对这些数据进行可视化

提供了数据字典，对字段有详细的说明

 

 还有一个article_demo.csv

 

 

说明：1.由于json数据太大，需要用json.loads()一条一条的解析，然后再插入到Mysql数据库中

2.数据量大，导入Mysql数据库速度太慢，一开始导了一遍需要4个小时+，经过调整mysql的my.ini参数设置时间缩为10分钟左右

3.当然也可以导出到csv，然后进行可视化也行，但是不利于后续对数据的操作，导出导入等。所以选择导入到数据库，方便练习简单的sql操作；当然导入到数据库会出现很多细节问题，对于新手也是一个练习的机会

结果：最终导入成功40万+条数据，舍弃了因清洗问题和其他报错问题的5万+条数据，基本满足可以完成作业的要求。

全部代码如下：（整体思路跟layman2016一致）

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
import jsonimport pymysql


#创建表格
def prem(db):
    cursor = db.cursor()   #调用操作游标
    cursor.execute("SELECT VERSION()")  #使用exectue执行SQL语句
    data = cursor.fetchone()  #使用fetchone获取一条数据
    print("Database version : %s " % data)  # 结果表明已经连接成功
    cursor.execute("DROP TABLE IF EXISTS article")  # 检查一下有没有同名，有则删除
    #19个字段
    sql = """CREATE TABLE article ( 
             content varchar(8000),title varchar(100),appName varchar(20),
             catLabel2 varchar(50),sourceRegion varchar(50),copyDate varchar(20),spamLabel varchar(40),
             appCode varchar(50),spamCode int,sourceType varchar(50),sentimentDistTitle_confidence float(8,6),
             sentimentDistTitle_positive float(8,6),sentimentDistTitle_negative float(8,6),
             id varchar(100), sentimentDist_confidence float(8,6),sentimentDist_positive float(8,6),sentimentDist_negative float(8,6),
             publishDate varchar(20),url varchar(1000)
             )ENGINE=MyISAM  default charset=utf8"""  #更换引擎，提高插入速度

    cursor.execute(sql)  # 根据需要创建一个表格



def article_insert(db):
    with open('E:/json_article/article.txt', encoding='utf-8') as f:
        i = 0
        error = 0
        line = f.readline()  # 使用逐行读取的方法
        while line:
            i += 1
            if i%10000==0:
                print("count is ",i)
            try:
                line = f.readline()
                article_text = json.loads(c,strict=False)  # 解析每一行数据,strict防止Invalid control character
                #print(c)

                #19个
                insert_re = "insert into article(content,title,appName,catLabel2,sourceRegion,copyDate,spamLabel,appCode,spamCode,sourceType,sentimentDistTitle_confidence,sentimentDistTitle_positive,sentimentDistTitle_negative,id,sentimentDist_confidence,sentimentDist_positive,sentimentDist_negative,publishDate,url) values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"

                result = []
                result.append((article_text["content"],article_text["title"],article_text["appName"],
                article_text["catLabel2"], article_text["sourceRegion"],article_text["copyDate"],article_text["spamLabel"],
                article_text["appCode"],article_text["spamCode"],article_text["sourceType"],
                article_text['sentimentDistTitle']['confidence'],article_text['sentimentDistTitle']['positive'],article_text['sentimentDistTitle']['negative'], 
                article_text['id'],article_text['sentimentDist']['confidence'],article_text['sentimentDist']['positive'],
                article_text['sentimentDist']['negative'], article_text['publishDate'],article_text["url"]))
                #print(result)
                #print(type(result))
                
                cursor = db.cursor()
                cursor.executemany(insert_re,result)
                
                db.commit()
                
            except Exception as e:
                error = error+1
                #print(str(e))
                continue    
            except UnicodeDecodeError as e: 
                error = error+1    #解决，直接忽略问题：utf-8 codec can't encode characters in :Invalid control character
                #print(str(e))
                continue 
            line = f.readline()  
        print("count is ",i,"error is ", error) 



if __name__ == "__main__":  # 起到一个初始化或者调用函数的作用
    db = pymysql.connect("localhost", "root", "******", "test", charset='utf8') #连接数据库test
    cursor = db.cursor()
    prem(db)
    article_insert(db)
    print('success')
    cursor.close()

第一部分 创建表格 def prem(db)

首先需要在Mysql数据库里面先创建数据库test，然后调用sql语句创建表格article。

db.cursor() 其实就是用来获得python执行Mysql命令的方法,也就是操作游标

cursor.execute（）执行sql语句

fetchone()则是接收返回结果行;fetchall()则是接受返回结果的多行记录

--参考fetchone和fetchall的介绍 https://blog.csdn.net/JackLiu16/article/details/78877460

 

如果对Mysql数据库语句和数据类型比较熟悉，可以直接在python里面直接创建表格；

如果不熟悉，建议在数据库创建好，因为Mysql里面的error code 比较详细，方便修改错误。

 

第二部分 数据插入  article_insert(db)

先用readline()逐行读取json数据

然后用json.loads()解析数据

--进一步理解区分load（）和loads（），dump（）和dumps（）参考 https://www.cnblogs.com/bigtreei/p/10466518.html

再用executemany()同时执行多条语句，执行同样多的语句比execute()快很多

--参考executemany()和execute()的区别和应用https://www.cnblogs.com/zeke-python-road/p/9442152.html