整体思路参考资料:
作业:有一个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的介绍
如果对Mysql数据库语句和数据类型比较熟悉,可以直接在python里面直接创建表格;
如果不熟悉,建议在数据库创建好,因为Mysql里面的error code 比较详细,方便修改错误。
第二部分 数据插入 article_insert(db)
先用readline()逐行读取json数据
然后用json.loads()解析数据
--进一步理解区分load()和loads(),dump()和dumps()参考
再用executemany()同时执行多条语句,执行同样多的语句比execute()快很多
--参考executemany()和execute()的区别和应用