我这里用的是pycharm64.exe软件和数据库MySQL5.5,数据库可视化SQLyogCommunity - 64 bit软件,语言是python3
爬取内容是腾讯实时监控内容,url:https://news.qq.com/zt2020/page/feiyan.htm?from=timeline&isappinstalled=0#/
爬取结果
history表
爬取程序:
#爬取历史数据 import requests import json import time def get_history(): url = 'https://view.inews.qq.com/g2/getOnsInfo?name=disease_other&callback=jQuery341026745307075030955_1584946267054&_=1584946267055' headers ={ 'user-agent': 'Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/70.0.3538.25 Safari/537.36 Core/1.70.3741.400 QQBrowser/10.5.3863.400' } res = requests.get(url,headers=headers) # print(res.text) response_data = json.loads(res.text.replace('jQuery341026745307075030955_1584946267054(','')[:-1]) # print(response_data) data = json.loads(response_data['data']) # print(data.keys()) chinaDayList = data['chinaDayList']#历史记录 chinaDayAddList = data['chinaDayAddList']#历史新增记录 # print(chinaDayList,chinaDayAddList) history = {} for i in chinaDayList: ds = '2021.' + i['date']#时间 tup = time.strptime(ds,'%Y.%m.%d') ds = time.strftime('%Y-%m-%d',tup)#改变时间格式,插入数据库 confirm = i['confirm'] suspect = i['suspect'] heal = i['heal'] dead = i['dead'] history[ds] = {'confirm':confirm,'suspect':suspect,'heal':heal,'dead':dead} for i in chinaDayAddList: ds = '2021.' + i['date']#时间 tup = time.strptime(ds,'%Y.%m.%d') ds = time.strftime('%Y-%m-%d',tup)#改变时间格式,插入数据库 confirm_add = i['confirm'] suspect_add = i['suspect'] heal_add = i['heal'] dead_add = i['dead'] history[ds].update({'confirm_add':confirm_add,'suspect_add':suspect_add,'heal_add':heal_add,'dead_add':dead_add}) return history
数据库连接
import time import pymysql import traceback def get_conn(): # 创建连接 conn = pymysql.connect(host='localhost', user='root', password='123456', database='jdbc', charset='utf8') # 创建游标 cursor = conn.cursor() # 执行完毕返回的结果集默认以元组显示 return conn, cursor def close_conn(conn, cursor): if cursor: cursor.close() if conn: conn.close()
创建表
import pymysql # 打开数据库连接 db = pymysql.connect(host='localhost', user='root', password='123456', database='jdbc') # 使用 cursor() 方法创建一个游标对象 cursor cursor = db.cursor() # 使用 execute() 方法执行 SQL,如果表存在则删除 cursor.execute("DROP TABLE IF EXISTS history") # 使用预处理语句创建表 sql = """CREATE TABLE history ( ds CHAR(20), confirm INT(20), confirm_add INT(20), suspect INT, suspect_add INT, heal INT, heal_add INT, dead INT, dead_add INT )""" # update_time cursor.execute(sql) # 关闭数据库连接 db.close()
表二
import pymysql # 打开数据库连接 db = pymysql.connect(host='localhost', user='root', password='123456', database='jdbc') # 使用 cursor() 方法创建一个游标对象 cursor cursor = db.cursor() # 使用 execute() 方法执行 SQL,如果表存在则删除 cursor.execute("DROP TABLE IF EXISTS details") # 使用预处理语句创建表 sql = """CREATE TABLE details ( id int(11) not null auto_increment, updateTime datetime default null comment '数据最后更新时间', province varchar(50) default null comment '省', city varchar(50) default null comment '市', confirm int(11) default null comment '累计确诊', confirm_add int(11) default null comment '新增确诊', heal int(11) default null comment '累计治愈', dead int(11) default null comment '累计死亡', primary key(id) )engine=InnoDB default charset=utf8;""" # update_time cursor.execute(sql) # 关闭数据库连接 db.close()
数据存储
#数据存储details def update_details(): cursor = None conn = None try: li = get_details() conn, cursor = get_conn() # sql = "insert into details(update_time,province,city,confirm,confirm_add,heal,dead) values(%s,%s,%s,%s,%s,%s,%s)" sql = "insert into details(updateTime,province,city,confirm,confirm_add,heal,dead) values(%s,%s,%s,%s,%s,%s,%s)" sql_query = 'select %s=(select updateTime from details order by id desc limit 1)' #对比当前最大时间戳 cursor.execute(sql_query,li[0][0]) if not cursor.fetchone()[0]: print(f"{time.asctime()}开始更新最新数据") for item in li: cursor.execute(sql, item) conn.commit() # 提交事务 update delete insert操作 print(f"{time.asctime()}更新最新数据完毕") else: print(f"{time.asctime()}已是最新数据!") except: traceback.print_exc() finally: close_conn(conn, cursor) # get_history() # update_details() def insert_history(): cursor = None conn = None try: dic = get_history() print(f"{time.asctime()}开始插入历史数据") conn, cursor = get_conn() sql = "insert into history values(%s,%s,%s,%s,%s,%s,%s,%s,%s)" for k, v in dic.items(): # item 格式 {‘2021-01-13’: {‘confirm’: 41, ‘suspect’: 0, ‘heal’: 0, ‘dead’: 1} cursor.execute(sql, [k, v.get("confirm"), v.get("confirm_add"),v.get("suspect"), v.get("suspect_add"), v.get("heal"), v.get("heal_add"), v.get("dead"), v.get("dead_add")]) conn.commit() # 提交事务 update delete insert操作 print(f"{time.asctime()}插入历史数据完毕") except: traceback.print_exc() finally: close_conn(conn, cursor) insert_history()