问题描述
根据上次题目的基础上新增要求如下：
问题分析
python爬取数据存入数据库，修改之前的代码显示最新数据即可。
源码
python部分（sql的最好做法应该是先删除再插入）
1 # coding=utf-8 2 import pymysql 3 import requests 4 import json 5 6 url = "https://c.m.163.com/ug/api/wuhan/app/data/list-total?t=316765403234" 7 header = {"user-agent": "Mozilla/5.0 (Linux; Android 8.0.0; Pixel 2 XL Build/OPD1.170816.004) AppleWebKit/537.36 (" 8 "KHTML, like Gecko) Chrome/80.0.3987.116 Mobile Safari/537.36"} 9 response = requests.get(url, headers=header) 10 mydir = json.loads(response.content.decode()) 11 # 数据库的连接 12 db = pymysql.connect(host='localhost', port=3306, user='root', passwd='123456', db='yiqing', charset='utf8') 13 14 # 使用cursor方法创建一个游标 15 cursor = db.cursor() 16 # mydir['data']['areaTree'][0]['children']再往后是省份 17 # print(mydir['data']['areaTree'][0]['children'][33]) 18 for item in mydir['data']['areaTree'][0]['children']: 19 name = item['name'] 20 confirm = item['total']['confirm'] 21 severe = item['total']['severe'] 22 heal = item['total']['heal'] 23 dead = item['total']['dead'] 24 suspect = item['total']['suspect'] 25 idcode = item['id'] 26 lastUpdateTime = item['lastUpdateTime'] 27 # 插入数据 28 # sql = "insert into pythontest(id,name,confirm,suspect,heal,dead,severe,idcode,lastupdatetime) " 29 # "values ({},'{}','{}','{}','{}','{}','{}','{}','{}');".format( 30 # 0, name, confirm, suspect, heal, dead, severe, idcode, lastUpdateTime) 31 32 # 更新数据 33 sql = "update realtime set confirm='{}',suspect='{}',heal='{}',dead='{}',severe='{}',lastupdatetime='{}'" 34 " where idcode='{}'".format(confirm, suspect, heal, dead, severe, lastUpdateTime, idcode) 35 print(sql) 36 cursor.execute(sql) 37 db.commit()
web方面，在上次的基础上修改。
数据库工具类DBUtil添加如下方法用于读取数据：
1 public static String queryLatestData() 2 { 3 Connection connection = null; 4 PreparedStatement preparedStatement = null; 5 ResultSet resultSet = null; 6 JSONArray jsonArray = new JSONArray(); 7 String sql = "select * from realtime"; 8 9 try { 10 connection = getConnection(); 11 preparedStatement = connection.prepareStatement(sql); 12 resultSet = preparedStatement.executeQuery(); 13 while (resultSet.next()) { 14 JSONObject jsonObject = new JSONObject(); 15 jsonObject.put("name", resultSet.getString(2)); 16 jsonObject.put("confirm", resultSet.getString(3)); 17 jsonObject.put("suspect", resultSet.getString(4)); 18 jsonObject.put("heal", resultSet.getString(5)); 19 jsonObject.put("dead", resultSet.getString(6)); 20 jsonObject.put("severe", resultSet.getString(7)); 21 jsonObject.put("idcode", resultSet.getString(8)); 22 jsonObject.put("lastupdatetime", resultSet.getString(9)); 23 jsonArray.add(jsonObject); 24 } 25 return jsonArray.toString(); 26 } catch (Exception e) { 27 return null; 28 } 29 }
servlet，变化的只是改成调用上述方法
主页面，适当修改ajax即可
1 <!DOCTYPE html> 2 <html> 3 <head> 4 <meta charset="UTF-8"> 5 <meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1"> 6 <link rel="stylesheet" href="layui/css/layui.css"> 7 <script src="js/echarts.min.js"></script> 8 <script src="js/jquery.min.js"></script> 9 <script type="text/javascript"> 10 function getLatestDate() { 11 var date = new Date(); 12 var seperator1 = "-"; 13 var seperator2 = ":"; 14 var month = date.getMonth() + 1; 15 var strDate = date.getDate(); 16 if(month >= 1 && month <= 9) { 17 month = "0" + month; 18 } 19 if(strDate >= 0 && strDate <= 9) { 20 strDate = "0" + strDate; 21 } 22 var currentdate = date.getFullYear() + seperator1 + month + seperator1 + strDate; 23 24 document.getElementById("span1").innerHTML="<font color='red'>"+currentdate+"</font>"; 25 } 26 </script> 27 <style> 28 #top { 29 auto; 30 height: 30px; 31 text-align: center; 32 margin: auto; 33 padding-top: 5px; 34 background-color: gray; 35 } 36 </style> 37 <title>YiQing</title> 38 </head> 39 <body onload="getLatestDate()"> 40 41 <div id="top"> 42 <span id="span1"></span> 最新疫情信息：<button>查询</button> 43 </div> 44 45 <div id="main" style=" 100%;height:550px;overflow: auto;"></div> 46 <script type="text/javascript"> 47 var myChart = echarts.init(document.getElementById('main')); 48 myChart.showLoading(); 49 var names=[]; 50 var nums=[]; 51 $("button").click(function(){ 52 var date = new Date(); 53 var seperator1 = "-"; 54 var seperator2 = ":"; 55 var month = date.getMonth() + 1; 56 var strDate = date.getDate(); 57 if(month >= 1 && month <= 9) { 58 month = "0" + month; 59 } 60 if(strDate >= 0 && strDate <= 9) { 61 strDate = "0" + strDate; 62 } 63 var currentdate = date.getFullYear() + seperator1 + month + seperator1 + strDate; 64 65 names=[]; 66 nums=[]; 67 $(".head").empty(); 68 $(".main").empty(); 69 $.post( 70 'http://localhost:8086/yiqing/queryDataServlet', 71 function(msg){ 72 var json=JSON.parse(msg); 73 var size=json.length; 74 for(i=0;i<size;i++){ 75 names.push(json[i].name); 76 nums.push(parseInt(json[i].confirm)); 77 } 78 79 myChart.hideLoading(); 80 var option = { 81 title: { 82 text: currentdate+'确诊人数' 83 }, 84 tooltip: {}, 85 legend: { 86 data:['确诊人数'] 87 }, 88 grid: { 89 y2: 150 90 }, 91 xAxis: { 92 type : 'category', 93 data: names, 94 axisLabel:{ 95 interval:0, 96 rotate:-17, 97 } 98 }, 99 yAxis: {}, 100 series: [{ 101 name: '确诊人数', 102 type: 'bar', 103 data: nums 104 }] 105 }; 106 myChart.setOption(option); 107 tr="<tr><th>省份</th><th>确诊人数</th><th>疑似人数</th><th>治愈人数</th><th>死亡人数</th><th>重症人数</th><th>编码</th><th>更新时间</th></tr>"; 108 $('.head').append(tr); 109 for(i=0;i<size;i++) 110 $('.main').append("<tr></tr>"); 111 $('.main tr').each(function(i){ 112 $(this).append("<td>"+json[i].name+"</td>"); 113 $(this).append("<td>"+json[i].confirm+"</td>"); 114 $(this).append("<td>"+json[i].suspect+"</td>"); 115 $(this).append("<td>"+json[i].heal+"</td>"); 116 $(this).append("<td>"+json[i].dead+"</td>"); 117 $(this).append("<td>"+json[i].severe+"</td>") 118 $(this).append("<td>"+json[i].idcode+"</td>"); 119 $(this).append("<td>"+json[i].lastupdatetime+"</td>"); 120 }) 121 } 122 123 ) 124 }) 125 </script> 126 <table class="layui-table"> 127 <thead class="head"> 128 </thead> 129 <tbody class="main"></tbody> 130 </table> 131 </body> 132 </html>
结果样例
PSP
