全国疫情统计可视化地图
发表时间：2021.3.7
要求：
第一阶段目标：导入全国疫情数据库payiqing.sql（MySQL数据库）。可以按照时期查询各个省市的疫情统计表格。以折线图或柱状图展示某天的全国各省的数据。
爬取代码：
import time
import requests
import pymysql
def build_connection_from_sourcedb():
return pymysql.connect(host="localhost",database="test",user="root",password="123456",port=3306,charset='utf8')
def get_province_list():
conn = build_connection_from_sourcedb()
cur = conn.cursor()
sql = "select province_short_name from dict_zone_province" # 请注意数据表中应事先存储此数据.
cur.execute(sql)
result = cur.fetchall()
cur.close()
conn.close()
province_list = []
for i in result:
province_list.append(str(i[0]))
return province_list
###############################################################
# 目标数据库操作
###############################################################
def target_database_operation(sql):
conn = build_connection_from_sourcedb()
cur = conn.cursor()
cur.execute(sql)
conn.commit()
cur.close()
conn.close()
def record_time_slot():
return time.strftime("%m.%d")
def delta_load():
today = record_time_slot()
url_prefix = "https://api.inews.qq.com/newsqa/v1/query/pubished/daily/list?province="
province_list = get_province_list()
province_list.append("香港")
province_list.append("澳门")
province_list.append("台湾")
i = 0
len_province_list = len(province_list)
conn = build_connection_from_sourcedb()
cur = conn.cursor()
sql_prefix = "insert into info_copy1(Date,Province,Confirmed_num,Yisi_num,Cured_num,Dead_num) values('"
sql_prefix_all = "insert into ncovi2019_daily_all(date,province,confirm,dead,heal,confirm_add) values('"
while i < len_province_list:
url = url_prefix + province_list[i]
response = requests.get(url)
data_province_i = response.json()["data"]
len_data_province_i = len(data_province_i)
j = 0
while j < len_data_province_i:
Date = str(data_province_i[j]["date"])
Province = str(data_province_i[j]["province"])
Confirmed_num = str(data_province_i[j]["confirm"])
Yisi_num = str(data_province_i[j]["confirm_add"])
Dead_num = str(data_province_i[j]["dead"])
Cured_num = str(data_province_i[j]["heal"])
# 如果是今天,那么插入数据到ncovi2019_daily
if today == Date:
fields = Date + "','" + Province + "'," + Confirmed_num + "," + Yisi_num + "," + Dead_num + "," + Cured_num +")"
sql = sql_prefix + fields
print(sql)
cur.execute(sql)
Date = ""
Province = ""
Confirmed_num = ""
Yisi_num = ""
Dead_num = ""
Cured_num = ""
j = j + 1
conn.commit()
print("第",str(i),"个省份-",province_list[i],"的"+today+"的疫情数据已插入ncovi2019_daily")
i = i + 1
target_database_operation("delete from ncovi2019_daily_all where date = '"+today+"'")
print("今天"+today+"的数据已经从ncovi2019_daily_all表中删除")
# 在ncovi2019_daily 中插入全国汇总数
print("将今天的数据汇总插入到ncovi2019_daily")
sql = """
insert into info_copy1(Date,Province,Confirmed_num,Yisi_num,Cured_num,Dead_num)
SELECT
Date,
'全国' as Province,
sum(Confirmed_num) AS Confirmed_num,
sum(Yisi_num) AS Yisi_num,
sum(Cured_num) AS Cured_num,
sum(Dead_num) AS Dead_num,
FROM
info_copy1
GROUP BY
Date
ORDER BY
1
"""
cur.execute(sql)
conn.commit()
print("将今天的数据插入到ncovi2019_daily_all")
sql_today = """
insert into ncovi2019_daily_all(date,province,confirm,dead,heal,confirm_add)
SELECT
date,
province as province,
confirm AS confirm,
dead AS dead,
heal AS heal,
confirm_add AS confirm_add
FROM
ncovi2019_daily
where province != '全国'
"""
print("上步结束后进行全国汇总")
cur.execute(sql_today)
conn.commit()
sql_all_summary = """
insert into ncovi2019_daily_all(date,province,confirm,dead,heal,confirm_add)
SELECT
date,
'全国' as province,
sum(confirm) AS confirm,
sum(dead) AS dead,
sum(heal) AS heal,
sum(confirm_add) AS confirm_add
FROM
ncovi2019_daily_all
where province != '全国'
GROUP BY
date
ORDER BY
1
"""
cur.execute(sql_all_summary)
conn.commit()
print("全国疫情数据已汇总插入ncovi2019_daily_all")
target_database_operation("update info_copy1 set report_date = Date(concat('2020',left(Date,2),right(Date,2))) ")
target_database_operation("update ncovi2019_daily_all set report_date = date(concat('2020',left(date,2),right(date,2))) ")
cur.close()
conn.close()
if __name__ == "__main__":
print("今天日期:"+record_time_slot())
target_database_operation("truncate table info_copy1")
print("清空ncovi2019_daily")
delta_load()
infoBean.java
package com.bean;
import java.util.Date;
public class infoBean {
int Id;
private String date;
private String province;
private String City;
private String Confirmed_num;
private String Yisi_num;
private String Cured_num;
private String Dead_num;
public infoBean(){
}
public int getId() {
return Id;
}
public void setId(int id) {
Id = id;
}
public String getDate() {
return date;
}
public void setDate(String date2) {
this.date = date2;
}
public String getProvince() {
return province;
}
public void setProvince(String province) {
this.province = province;
}
public String getCity() {
return City;
}
public void setCity(String city) {
City = city;
}
public String getConfirmed_num() {
return Confirmed_num;
}
public void setConfirmed_num(String confirmed_num) {
Confirmed_num = confirmed_num;
}
public String getYisi_num() {
return Yisi_num;
}
public void setYisi_num(String yisi_num) {
Yisi_num = yisi_num;
}
public String getCured_num() {
return Cured_num;
}
public void setCured_num(String cured_num) {
Cured_num = cured_num;
}
public String getDead_num() {
return Dead_num;
}
public void setDead_num(String dead_num) {
Dead_num = dead_num;
Mydata.java
package com.bean;
import java.util.Date;
public class infoBean {
int Id;
private String date;
private String province;
private String City;
private String Confirmed_num;
private String Yisi_num;
private String Cured_num;
private String Dead_num;
public infoBean(){
}
public int getId() {
return Id;
}
public void setId(int id) {
Id = id;
}
public String getDate() {
return date;
}
public void setDate(String date2) {
this.date = date2;
}
public String getProvince() {
return province;
}
public void setProvince(String province) {
this.province = province;
}
public String getCity() {
return City;
}
public void setCity(String city) {
City = city;
}
public String getConfirmed_num() {
return Confirmed_num;
}
public void setConfirmed_num(String confirmed_num) {
Confirmed_num = confirmed_num;
}
public String getYisi_num() {
return Yisi_num;
}
public void setYisi_num(String yisi_num) {
Yisi_num = yisi_num;
}
public String getCured_num() {
return Cured_num;
}
public void setCured_num(String cured_num) {
Cured_num = cured_num;
}
public String getDead_num() {
return Dead_num;
}
public void setDead_num(String dead_num) {
Dead_num = dead_num;
}
}
SearchDao.java
package com.dao;
import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import javax.servlet.ServletException;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;
import com.util.Butil;
import com.bean.infoBean;
import com.bean.infoBean;
public class SearchDao {
public List<infoBean> search(String date1,String date2)throws IOException, SQLException{
List<infoBean> offs=new ArrayList<infoBean>();
Butil db=new Butil();
//System.out.println(username);
Connection conn=(Connection) db.getConn();
String sql="" +
" select * from info_copy1 where Date between ? and ?";
PreparedStatement ptmt=(PreparedStatement) conn.prepareStatement(sql);
ptmt.setString(1,date1);
ptmt.setString(2,date2);
ResultSet rs=ptmt.executeQuery();
while(rs.next()){
infoBean result=new infoBean();
String province = rs.getString("Province");
String Confirmed_num = rs.getString("Confirmed_num");
String Yisi_num = rs.getString("Yisi_num");
String Cured_num = rs.getString("Cured_num");
String Dead_num = rs.getString("Dead_num");
result.setProvince(province);
result.setConfirmed_num(Confirmed_num);
result.setYisi_num(Yisi_num);
result.setCured_num(Cured_num);
result.setDead_num(Dead_num);
offs.add(result);
//System.out.println(rs.getString("id"));
//System.out.println(rs.getString("password"));
}
conn.close();
return offs;
}
public List<infoBean> search2(String date1)throws IOException, SQLException{
List<infoBean> offs=new ArrayList<infoBean>();
Butil db=new Butil();
//System.out.println(username);
Connection conn=(Connection) db.getConn();
String sql="" +
" select * from info_copy1 where Date = ?";
PreparedStatement ptmt=(PreparedStatement) conn.prepareStatement(sql);
ptmt.setString(1,date1);
ResultSet rs=ptmt.executeQuery();
while(rs.next()){
infoBean result=new infoBean();
String province = rs.getString("Province");
String Confirmed_num = rs.getString("Confirmed_num");
String Yisi_num = rs.getString("Yisi_num");
String Cured_num = rs.getString("Cured_num");
String Dead_num = rs.getString("Dead_num");
result.setDate("03.13");
result.setProvince(province);
result.setConfirmed_num(Confirmed_num);
result.setYisi_num(Yisi_num);
result.setCured_num(Cured_num);
result.setDead_num(Dead_num);
offs.add(result);
//System.out.println(rs.getString("id"));
//System.out.println(rs.getString("password"));
}
conn.close();
return offs;
}
public static List<infoBean> getAllinfo1()throws IOException, SQLException {
// TODO Auto-generated method stub
List<infoBean> offs=new ArrayList<infoBean>();
Butil db=new Butil();
Connection conn=(Connection) db.getConn();
String sql = "select * from info_copy1_copy1 ";
PreparedStatement ptmt=(PreparedStatement) conn.prepareStatement(sql);
ResultSet rs=ptmt.executeQuery();
while(rs.next()){
infoBean result=new infoBean();
String province = rs.getString("Province");
String Confirmed_num = rs.getString("Confirmed_num");
String Yisi_num = rs.getString("Yisi_num");
String Cured_num = rs.getString("Cured_num");
String Dead_num = rs.getString("Dead_num");
result.setProvince(province);
result.setConfirmed_num(Confirmed_num);
result.setYisi_num(Yisi_num);
result.setCured_num(Cured_num);
result.setDead_num(Dead_num);
offs.add(result);
//System.out.println(rs.getString("id"));
//System.out.println(rs.getString("password"));
}
conn.close();
return offs;
}
}
SearchServlet.java
package com.dao;
import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import javax.servlet.ServletException;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;
import com.util.Butil;
import com.bean.infoBean;
import com.bean.infoBean;
public class SearchDao {
public List<infoBean> search(String date1,String date2)throws IOException, SQLException{
List<infoBean> offs=new ArrayList<infoBean>();
Butil db=new Butil();
//System.out.println(username);
Connection conn=(Connection) db.getConn();
String sql="" +
" select * from info_copy1 where Date between ? and ?";
PreparedStatement ptmt=(PreparedStatement) conn.prepareStatement(sql);
ptmt.setString(1,date1);
ptmt.setString(2,date2);
ResultSet rs=ptmt.executeQuery();
while(rs.next()){
infoBean result=new infoBean();
String province = rs.getString("Province");
String Confirmed_num = rs.getString("Confirmed_num");
String Yisi_num = rs.getString("Yisi_num");
String Cured_num = rs.getString("Cured_num");
String Dead_num = rs.getString("Dead_num");
result.setProvince(province);
result.setConfirmed_num(Confirmed_num);
result.setYisi_num(Yisi_num);
result.setCured_num(Cured_num);
result.setDead_num(Dead_num);
offs.add(result);
//System.out.println(rs.getString("id"));
//System.out.println(rs.getString("password"));
}
conn.close();
return offs;
}
public List<infoBean> search2(String date1)throws IOException, SQLException{
List<infoBean> offs=new ArrayList<infoBean>();
Butil db=new Butil();
//System.out.println(username);
Connection conn=(Connection) db.getConn();
String sql="" +
" select * from info_copy1 where Date = ?";
PreparedStatement ptmt=(PreparedStatement) conn.prepareStatement(sql);
ptmt.setString(1,date1);
ResultSet rs=ptmt.executeQuery();
while(rs.next()){
infoBean result=new infoBean();
String province = rs.getString("Province");
String Confirmed_num = rs.getString("Confirmed_num");
String Yisi_num = rs.getString("Yisi_num");
String Cured_num = rs.getString("Cured_num");
String Dead_num = rs.getString("Dead_num");
result.setDate("03.13");
result.setProvince(province);
result.setConfirmed_num(Confirmed_num);
result.setYisi_num(Yisi_num);
result.setCured_num(Cured_num);
result.setDead_num(Dead_num);
offs.add(result);
//System.out.println(rs.getString("id"));
//System.out.println(rs.getString("password"));
}
conn.close();
return offs;
}
public static List<infoBean> getAllinfo1()throws IOException, SQLException {
// TODO Auto-generated method stub
List<infoBean> offs=new ArrayList<infoBean>();
Butil db=new Butil();
Connection conn=(Connection) db.getConn();
String sql = "select * from info_copy1_copy1 ";
PreparedStatement ptmt=(PreparedStatement) conn.prepareStatement(sql);
ResultSet rs=ptmt.executeQuery();
while(rs.next()){
infoBean result=new infoBean();
String province = rs.getString("Province");
String Confirmed_num = rs.getString("Confirmed_num");
String Yisi_num = rs.getString("Yisi_num");
String Cured_num = rs.getString("Cured_num");
String Dead_num = rs.getString("Dead_num");
result.setProvince(province);
result.setConfirmed_num(Confirmed_num);
result.setYisi_num(Yisi_num);
result.setCured_num(Cured_num);
result.setDead_num(Dead_num);
offs.add(result);
//System.out.println(rs.getString("id"));
//System.out.println(rs.getString("password"));
}
conn.close();
return offs;
}
}
echarts.java
package com.servlet;
import java.io.IOException;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.bean.Mydata;
import com.bean.infoBean;
import com.dao.SearchDao;
import com.google.gson.Gson;
/**
* echarts implementation class echarts
*/
@WebServlet("/echarts")
public class echarts extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public echarts() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
request.setCharacterEncoding("UTF-8");
response.setContentType("text/html;charset=utf-8");
List<infoBean> Data = null;
SearchDao pro=new SearchDao();
try {
Data = pro.getAllinfo1();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
List<Mydata> mydata = new ArrayList<Mydata>();
for (infoBean data : Data) {
Mydata info = new Mydata();
info.setName(data.getProvince());
info.setValue(data.getConfirmed_num());
mydata.add(info);
}
Gson gson = new Gson();
String json = gson.toJson(mydata);
System.out.println(json);
response.getWriter().write(json);
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
doGet(request, response);
}
}
Butil.java
package com.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/**
* 閺佺増宓佹惔鎾圭箾閹恒儱浼愰崗锟?
* @author 閺夊孩杈扮?癸拷
*
*/
public class Butil {
//閼辨梻绮ㄧ?涙顑佹稉锟? //閺佺増宓佹惔鎾虫倳test
public static String db_url = "jdbc:mysql://localhost:3306/sys?&useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC";
//閺佺増宓佹惔鎾舵暏閹村嘲鎮?
public static String db_user = "root";
//閺佺増宓佹惔鎾崇槕閻礁鎮?
public static String db_pass = "123456";
public static Connection getConn () {
//婢圭増妲戞稉搴㈡殶閹诡喖绨遍惃鍕箾閹恒儱鑻熺?圭偘绶ラ崠鏍﹁礋null
Connection conn = null;
try {
//妞瑰崬濮╃粙瀣碍閸氾拷
Class.forName("com.mysql.jdbc.Driver");//鏉╃偞甯撮弫鐗堝祦鎼达拷
//閸忚渹缍嬮崷鎷岀箾閹恒儱鍩岄弫鐗堝祦鎼存挴锟芥柡锟芥棁浠堥幒銉ョ摟缁楋缚瑕嗛敍鍫熸殶閹诡喖绨遍崥宥忕礆閿涘矁浠堥幒銉ф暏閹村嘲鎮曢敍宀冧粓閹恒儱鐦戦惍浣告倳
conn = DriverManager.getConnection(db_url, db_user, db_pass);
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
/**
* 閸忔娊妫存潻鐐村复
* @param state
* @param conn
*/
public static void close (Statement state, Connection conn) {
if (state != null) {
try {
state.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void close (ResultSet rs, Statement state, Connection conn) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (state != null) {
try {
state.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
search.java
package com.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/**
* 閺佺増宓佹惔鎾圭箾閹恒儱浼愰崗锟?
* @author 閺夊孩杈扮?癸拷
*
*/
public class Butil {
//閼辨梻绮ㄧ?涙顑佹稉锟? //閺佺増宓佹惔鎾虫倳test
public static String db_url = "jdbc:mysql://localhost:3306/sys?&useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC";
//閺佺増宓佹惔鎾舵暏閹村嘲鎮?
public static String db_user = "root";
//閺佺増宓佹惔鎾崇槕閻礁鎮?
public static String db_pass = "123456";
public static Connection getConn () {
//婢圭増妲戞稉搴㈡殶閹诡喖绨遍惃鍕箾閹恒儱鑻熺?圭偘绶ラ崠鏍﹁礋null
Connection conn = null;
try {
//妞瑰崬濮╃粙瀣碍閸氾拷
Class.forName("com.mysql.jdbc.Driver");//鏉╃偞甯撮弫鐗堝祦鎼达拷
//閸忚渹缍嬮崷鎷岀箾閹恒儱鍩岄弫鐗堝祦鎼存挴锟芥柡锟芥棁浠堥幒銉ョ摟缁楋缚瑕嗛敍鍫熸殶閹诡喖绨遍崥宥忕礆閿涘矁浠堥幒銉ф暏閹村嘲鎮曢敍宀冧粓閹恒儱鐦戦惍浣告倳
conn = DriverManager.getConnection(db_url, db_user, db_pass);
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
/**
* 閸忔娊妫存潻鐐村复
* @param state
* @param conn
*/
public static void close (Statement state, Connection conn) {
if (state != null) {
try {
state.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void close (ResultSet rs, Statement state, Connection conn) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (state != null) {
try {
state.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
浙公网安备 33010602011771号