记录自己三天速成django+html制作国内疫情可视化平台的过程（二）_使用django框架及html,css,bootstrap等技术建立可视化展示平台-优快云博客

本文链接：https://blog.youkuaiyun.com/qq_53500716/article/details/125412087

3.可视化页面

3.1设计

经过（一）中的操作，我们已经将登录/注册的功能基本实现。下面就是设计可视化页面了，一共会画8张图，我初步的设计是这样的：

{% load static %}
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
    <link rel="stylesheet" href="{% static 'plugins/bootstrap-3.4.1/css/bootstrap.css' %}">
    <style>
        body{
     
     
            margin: 0;
            padding: 0;
            background: url("{% static 'img/4.png'%}") fixed center center;
            background-size: cover;
        }
        .box{
     
     
            padding-left: 20px;
            padding-right: 20px;
        }
        .header h1{
     
     
            color: white;
            text-align: center;
            font-family: 华文彩云,sans-serif;
            font-size: 40px;
        }
        .c1{
     
     
            height: 900px;
            {
     
     #margin-right: 10px;#}
        }
        .area1{
     
     
            padding: 20px;
        }
        .area1 div{
     
     
            position: relative;
            height: 32.5%;
            margin-bottom: 15px;
            border: 1px solid red;
        }
        .area2{
     
     
            padding: 20px;
        }
        .area2 .map2{
     
     
            margin-top: 26px;
            margin-bottom: 0;
        }
        .area2 div{
     
     
            position: relative;
            height: 49%;
            margin-bottom: 15px;
            border: 1px solid red;
        }
    </style>
</head>
<body>
    <div class="box">
        <div class="header"><h1>国内疫情可视化平台</h1>></div>
        <div class="area1 col-sm-3 c1">
            <div class="line" id="line"></div>
            <div class="mypanel" id="mypanel"></div>
            <div class="scatter" id="scatter"></div>
        </div>
        <div class="area2 col-sm-6 c1">
            <div class="map1" id="map1"></div>
            <div class="map2" id="map2"></div>
        </div>
        <div class="area1 col-sm-3 c1">
            <div class="TR" id="TP"></div>
            <div class="Pie" id="Pie"></div>
            <div class="wordc" id="wordc"></div>
        </div>
    </div>
    <script src="{% static 'js/jquery-3.6.0.js' %}"></script>
    <script src="{% static 'plugins/bootstrap-3.4.1/js/bootstrap.js' %}"></script>
    <script src="{% static 'js/echarts.js' %}"></script>
</body>
</html>

在这里插入图片描述
分成8个区域，每个区域放一张图

3.2后端逻辑

这里同样也会用到我们之前提到的后端给前端传数据功能，由于摆烂不想写sql语句，所以借鉴了
大佬（点击跳转原帖）的sql语句思路。
在这里插入图片描述
在utils下建立一个sqldata.py，学过一点pymysql的人应该都看的懂

from pymysql import Connect
from jieba.analyse import extract_tags
import re
import jieba


class SqlData:
    def __init__(self):
        self.conn = Connect(host="127.0.0.1",
                            port=3306,
                            user="root",
                            password="123456",
                            database="data",
                            charset="utf8")

    def get_data(self, sql):
        cur = self.conn.cursor()
        cur.execute(sql)
        self.conn.commit()
        data = cur.fetchall()
        cur.close()
        return data

    def line_data(self):
        """折线图数据"""
        sql = """SELECT cast(SUM(confirmed_add) as char),day(update_time) 
                 FROM covid_data WHERE day(update_time) <> 13 
                 GROUP BY update_time ORDER BY update_time ASC"""
        data = self.get_data(sql)
        # 得到的data是一个“二维元组”
        data_x = []
        data_y = []
        j = 0
        for i in data:
            # 将每一天的人数装入x，每一天装入y
            data_y.append(int(i[0]))
            data_x.append(i[1])
            j = j + 1
        return data_x, data_y

    def scatter_data(self):
        """散点图数据"""
        sql = """SELECT `人口占比`,`累计确诊` FROM (SELECT province_name,
                SUM( covid_data.confirmed_count ) AS '累计确诊' 
                FROM covid_data WHERE covid_data.update_time = "2020-02-29" 
                AND covid_data.province_name <> "湖北省" 
                GROUP BY covid_data.province_name ) AS a2 
                LEFT JOIN ( SELECT target_province_name, `value` AS `人口占比` 
                FROM migrate_data WHERE source_province_name = "湖北省" ) 
                AS a1 ON province_name = target_province_name ORDER BY a1.`人口占比`"""
        # 同样也是一个二维数组
        data = self.get_data(sql)
        scatter_data = []
        for i in data:
            scatter_data.append([i[0], int(i[1])])
        return scatter_data

    def panel_data(self):
        """仪表盘数据"""
        sql = """SELECT cast(SUM( dead_count )/( SELECT SUM( confirmed_count ) FROM covid_data 
        WHERE update_time = '2020-2-29' )as char) AS 死亡率 
        FROM covid_data WHERE update_time = '2020-2-29'"""
        data = self.get_data(sql)
        panel_data = round(float(data[0][0]) * 100, 2)
        return panel_data

    def geoLines_data(self):
        """迁徙图数据"""
        sql = """SELECT source_province_name,target_province_name,value,longitude,latitude FROM 
            `migrate_data`,area_china where source_province_name = '湖北省' 
            and migrate_data.target_province_code = area_china.`code`"""
        data = self.get_data(sql)
        target = [114.341861, 30.546498]
        series1 = []
        for i in data:
            dic = {
   
   "fromName": i[0], "toName": i[1], "value": i[2], "coords": [target, [i[3], i[4]]]}
            series1.append(dic)
        series2 = []
        for i in data:
            dic = {
   
   "name": i[1], "value": i[2]}
            series2.append(dic)
        seriseAll = [series1, series2]
        seriseAll01 = seriseAll[0]
        seriseAll02 = seriseAll[1]
        return seriseAll01, seriseAll02

    def map_data(self):
        """地图数据"""
        sql = """SELECT province_name,SUM(confirmed_count) FROM `covid_data` 
        where update_time = '2020-02-29' GROUP BY province_name"""
        data = self.get_data(sql)
        mapSeries = []
        # 去除掉不需要的字符串，只需要省的名称即可
        for i in data:
            dic = {
   
   "name": i[0].strip("省市自治区回族壮族维吾尔族"), "value": int(i[1])}
            mapSeries.append(dic)
        return mapSeries

    def pie_data(self):
        """饼图数据"""
        sql = """SELECT news_source ,COUNT(*) FROM news_info GROUP BY news_source"""
        data = self.get_data(sql)
        pie_data = []
        for i in data:
            dic = {
   
   "value": i[1], "name": i[0]}
            pie_data.append(dic)
        return pie_data

    def river_data(self):
        """河流图数据"""
        sql = """SELECT DATE_FORMAT(publish_time,'%Y-%m-%d'),count(*),news_source 
            FROM `news_info`where DATE_FORMAT(publish_time,'%Y-%m')='2020-02' 
            group by DATE_FORMAT(publish_time,'%Y-%m-%d'),news_source"""
        data = self.get_data(sql)
        river = list(data)
        for i, j in enumerate(river):
            river[i] = list(j)
        return river

    def word_data(self):
        """词云图数据"""
        sql = """SELECT news_summary FROM news_info"""
        data = self.get_data(sql)
        numlist = []
        for row in data:
            numlist.append(row[0])
        numlist = list(map(str, numlist))
        numlist = ''.join(numlist)
        cut_words = []
        for line in numlist:
            line.strip('\n')
            line = re.sub("[A-Za-z0-9\：\·\—\，\。\“ \”]", "", line)
            seg_list = jieba.cut(line, cut_all=False)
            cut_words.append(" ".join(seg_list))
        # 列表转为字符串
        cut_words = ''.join(cut_words)
        tf_idf_w = extract_tags(cut_words, topK=100, withWeight=True)
        key = []
        value = []
        for i, j in tf_idf_w:
            key.append(i)
            value.append(