全国疫情统计可视化地图-第一阶段

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()

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;

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;
    }
}

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;
    }
}

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;
    }
}

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);
    }

}

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();
            }
        }
    }

}

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();
            }
        }
    }

}