You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
174 lines
7.8 KiB
174 lines
7.8 KiB
4 years ago
|
{
|
||
|
|
"cells": [
|
||
|
|
{
|
||
|
|
"cell_type": "code",
|
||
|
|
"execution_count": 3,
|
||
|
|
"metadata": {},
|
||
|
|
"outputs": [
|
||
|
|
{
|
||
|
|
"name": "stdout",
|
||
|
|
"output_type": "stream",
|
||
|
|
"text": [
|
||
|
|
"2020-08-14\n",
|
||
|
|
"The update was successful\n",
|
||
|
|
"+++ save_province_datas is over.\n",
|
||
|
|
"((1614, -125, 309, 28, 5, 1, 39, -2, 89673, 99, 2249, 22, 83352, 269, 4707, 3, '2020-08-14 16:53:26'),)\n"
|
||
|
|
]
|
||
|
|
}
|
||
|
|
],
|
||
|
|
"source": [
|
||
|
|
"import re\n",
|
||
|
|
"import json\n",
|
||
|
|
"import requests\n",
|
||
|
|
"import pymysql\n",
|
||
|
|
"import datetime\n",
|
||
|
|
"##################################################################################################\n",
|
||
|
|
" #爬取数据\n",
|
||
|
|
"# 国内疫情数据概况\n",
|
||
|
|
"def InsideSummary(ds):\n",
|
||
|
|
" class InsideSummary:\n",
|
||
|
|
" def __init__(self):\n",
|
||
|
|
" self.curConfirm = 0 #现有确诊\n",
|
||
|
|
" self.curConfirmRelative = 0#较昨日新增确诊\n",
|
||
|
|
" self.asymptomatic = 0 #无症状感染\n",
|
||
|
|
" self.asymptomaticRelative = 0 #较昨日新增无症状感染\n",
|
||
|
|
" self.unconfirmed = 0 #现有疑似\n",
|
||
|
|
" self.unconfirmedRelative = 0#较昨日疑似新增\n",
|
||
|
|
" self.icu = 0 #现有重症\n",
|
||
|
|
" self.icuRelative = 0 #较昨日重症病例新增\n",
|
||
|
|
" self.confirmed = 0 #累计确诊\n",
|
||
|
|
" self.confirmedRelative = 0 #较昨日累计确诊新增\n",
|
||
|
|
" self.overseasInput = 0 #累计境外输入\n",
|
||
|
|
" self.overseasInputRelative = 0#较昨日累计境外输入新 增\n",
|
||
|
|
" self.cured = 0 #累计治愈\n",
|
||
|
|
" self.curedRelative = 0 #较昨日累计治愈新增\n",
|
||
|
|
" self.died = 0 #累计死亡\n",
|
||
|
|
" self.diedRelative = 0#较昨日累计死亡新增\n",
|
||
|
|
" self.updatedTime = 0 #发布时间\n",
|
||
|
|
" # 数据库接口\n",
|
||
|
|
" def get_inside_summary_tuple(self):\n",
|
||
|
|
" return ((self.curConfirm, self.curConfirmRelative, self.asymptomatic, self.asymptomaticRelative, \\\n",
|
||
|
|
" self.unconfirmed, self.unconfirmedRelative, self.icu, self.icuRelative, self.confirmed, \\\n",
|
||
|
|
" self.confirmedRelative, self.overseasInput, self.overseasInputRelative, self.cured, self.curedRelative, \\\n",
|
||
|
|
" self.died, self.diedRelative, self.updatedTime))\n",
|
||
|
|
" # 输出接口\n",
|
||
|
|
" def __str__(self):\n",
|
||
|
|
" return '%s %s %s %s %s %s %s %s %s %s %s %s %s %s %s %s %s' % (\n",
|
||
|
|
" self.curConfirm, self.curConfirmRelative, self.asymptomatic, self.asymptomaticRelative, self.unconfirmed,\n",
|
||
|
|
" self.unconfirmedRelative, self.icu, self.icuRelative, self.confirmed, self.confirmedRelative,\n",
|
||
|
|
" self.overseasInput, self.overseasInputRelative, self.cured, self.curedRelative, self.died, self.diedRelative,\n",
|
||
|
|
" self.updatedTime)\n",
|
||
|
|
" #######################################################################################################\n",
|
||
|
|
" #业务逻辑类\n",
|
||
|
|
"\n",
|
||
|
|
" # 爬取国内疫情数据\n",
|
||
|
|
" res=requests.get('https://view.inews.qq.com/g2/getOnsInfo?name=disease_h5')\n",
|
||
|
|
" #解码\n",
|
||
|
|
" res=res.content.decode('utf-8')\n",
|
||
|
|
"\n",
|
||
|
|
" dict=json.loads(res) #str->dict\n",
|
||
|
|
"\n",
|
||
|
|
" #规范数据\n",
|
||
|
|
" for key in dict:\n",
|
||
|
|
" try:\n",
|
||
|
|
" dict[key] = dict[key].replace('\\\\', '')\n",
|
||
|
|
" key=key.replace('\\\\','')\n",
|
||
|
|
" except:\n",
|
||
|
|
" pass\n",
|
||
|
|
" #新生成data字典\n",
|
||
|
|
" data=json.loads(dict['data'])\n",
|
||
|
|
"\n",
|
||
|
|
" # 国内疫情数据实例化\n",
|
||
|
|
" # # #赋值\n",
|
||
|
|
" def fetch_inside_summary(data):\n",
|
||
|
|
" insideSummary = InsideSummary()\n",
|
||
|
|
" insideSummary.curConfirm = int(data['chinaTotal']['nowConfirm'])\n",
|
||
|
|
" insideSummary.curConfirmRelative = int(data['chinaAdd']['nowConfirm'])\n",
|
||
|
|
" insideSummary.asymptomatic = int(data['chinaTotal']['noInfect'])\n",
|
||
|
|
" insideSummary.asymptomaticRelative = int(data['chinaAdd']['noInfect'])\n",
|
||
|
|
" insideSummary.unconfirmed = int(data['chinaTotal']['suspect'])\n",
|
||
|
|
" insideSummary.unconfirmedRelative = int(data['chinaAdd']['suspect'])\n",
|
||
|
|
" insideSummary.icu = int(data['chinaTotal']['nowSevere'])\n",
|
||
|
|
" insideSummary.icuRelative = int(data['chinaAdd']['nowSevere'])\n",
|
||
|
|
" insideSummary.confirmed = int(data['chinaTotal']['confirm'])\n",
|
||
|
|
" insideSummary.updatedTime = data['lastUpdateTime']\n",
|
||
|
|
" insideSummary.confirmedRelative = int(data['chinaAdd']['confirm'])\n",
|
||
|
|
" insideSummary.overseasInput = int(data['chinaTotal']['importedCase'])\n",
|
||
|
|
" insideSummary.overseasInputRelative = int(data['chinaAdd']['importedCase'])\n",
|
||
|
|
" insideSummary.cured = int(data['chinaTotal']['heal'])\n",
|
||
|
|
" insideSummary.curedRelative = int(data['chinaAdd']['heal'])\n",
|
||
|
|
" insideSummary.died = int(data['chinaTotal']['dead'])\n",
|
||
|
|
" insideSummary.diedRelative = int(data['chinaAdd']['dead'])\n",
|
||
|
|
" return insideSummary\n",
|
||
|
|
" ##########################################################################################################\n",
|
||
|
|
" #存储数据于数据库\n",
|
||
|
|
"\n",
|
||
|
|
" #保存国内疫情概况数据\n",
|
||
|
|
"\n",
|
||
|
|
" def insert(res,ds):\n",
|
||
|
|
"\n",
|
||
|
|
" # 创建连接，并且返回连接对象)\n",
|
||
|
|
" conn =pymysql.connect(host = ds[0],user = ds[1],passwd = ds[2],db = ds[3])\n",
|
||
|
|
" # 创建游标对象\n",
|
||
|
|
" cursor = conn.cursor()\n",
|
||
|
|
"\n",
|
||
|
|
" date = data['lastUpdateTime'].split(' ',1)[0]\n",
|
||
|
|
" print(date)\n",
|
||
|
|
" sql = 'delete from home_realtime_datas where updatedTime like \"%s\"'%(date+'%')\n",
|
||
|
|
" print('The update was successful')\n",
|
||
|
|
" try:\n",
|
||
|
|
" cursor.execute(sql)\n",
|
||
|
|
" conn.commit()\n",
|
||
|
|
" except Exception as e:\n",
|
||
|
|
" print(e)\n",
|
||
|
|
"\n",
|
||
|
|
" sql='insert into home_realtime_datas(curConfirm,curConfirmRelative,asymptomatic,asymptomaticRelative,unconfirmed,unconfirmedRelative,icu,icuRelative,confirmed,confirmedRelative,overseasInput,overseasInputRelative,cured,curedRelative,died,diedRelative,updatedTime) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'\n",
|
||
|
|
" try:\n",
|
||
|
|
" cursor.execute(sql, res)\n",
|
||
|
|
" conn.commit()\n",
|
||
|
|
" except Exception as e:\n",
|
||
|
|
" print(e)\n",
|
||
|
|
" print('+++ save_province_datas is over.')\n",
|
||
|
|
" cursor.close()\n",
|
||
|
|
" conn.close()\n",
|
||
|
|
"\n",
|
||
|
|
" insert(fetch_inside_summary(data).get_inside_summary_tuple(),ds)\n",
|
||
|
|
" conn = pymysql.connect(host = ds[0],user = ds[1],passwd = ds[2],db = ds[3])\n",
|
||
|
|
" cursor = conn.cursor()\n",
|
||
|
|
" sql = 'select * from home_realtime_datas'\n",
|
||
|
|
" cursor.execute(sql)\n",
|
||
|
|
" results = cursor.fetchall()\n",
|
||
|
|
" print(results)\n"
|
||
|
|
]
|
||
|
|
},
|
||
|
|
{
|
||
|
|
"cell_type": "code",
|
||
|
|
"execution_count": null,
|
||
|
|
"metadata": {},
|
||
|
|
"outputs": [],
|
||
|
|
"source": []
|
||
|
|
}
|
||
|
|
],
|
||
|
|
"metadata": {
|
||
|
|
"kernelspec": {
|
||
|
|
"display_name": "Python 3",
|
||
|
|
"language": "python",
|
||
|
|
"name": "python3"
|
||
|
|
},
|
||
|
|
"language_info": {
|
||
|
|
"codemirror_mode": {
|
||
|
|
"name": "ipython",
|
||
|
|
"version": 3
|
||
|
|
},
|
||
|
|
"file_extension": ".py",
|
||
|
|
"mimetype": "text/x-python",
|
||
|
|
"name": "python",
|
||
|
|
"nbconvert_exporter": "python",
|
||
|
|
"pygments_lexer": "ipython3",
|
||
|
|
"version": "3.8.3"
|
||
|
|
}
|
||
|
|
},
|
||
|
|
"nbformat": 4,
|
||
|
|
"nbformat_minor": 4
|
||
|
|
}
|