经过（一）中的操作，我们已经将登录/注册的功能基本实现。下面就是设计可视化页面了，一共会画8张图，我初步的设计是这样的：
{% load static %}
Title
国内疫情可视化平台
>
分成8个区域，每个区域放一张图
这里同样也会用到我们之前提到的后端给前端传数据功能，由于摆烂不想写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(j)
return key, value
然后将这个类在views中调用from web.utils.sqldata import SqlData
写上可视化页面对应的函数，将这些死数据都丢给前端（同样也别忘记在urls中添加）
def canvas(request):
"""可视化界面"""
sql_data = SqlData()
line_x, line_y = sql_data.line_data()
scatter_data = sql_data.scatter_data()
panel_data = sql_data.panel_data()
geolines_data, geoscatter_data = sql_data.geoLines_data()
data_map = sql_data.map_data()
pie_data = sql_data.pie_data()
river_data = sql_data.river_data()
key, value = sql_data.word_data()
return render(request, "canvas.html", {"line_x": line_x, "line_y": line_y,
"scatter_data": scatter_data,
"panel_data": panel_data,
"geolines_data": geolines_data,
"geoscatter_data": geoscatter_data,
"data_map": data_map,
"pie_data": pie_data,
"river_data": river_data,
"key": key, "value": value})
3.3 echarts
可视化使用到了echarts，这个我就不多介绍了因为我也是不是太会，我只学过pyecharts，echarts看完官网的初级教程就可以画出图了，好看的模板就去点击跳转echarts社区中看看大佬的作品就行。
3.4最终实现在html中额外引入
这些插件
每一个图写一个script标签来装
下面是html源码，画图的代码也借鉴了很多大佬的思路
{% load static %}
Title
国
内
疫
情
可
视
化
平
台
>
{#折线图#}
{#仪表盘#}
{#散点图#}
{#地图——迁徙图#}
{#地图——热力图#}
{#洋流图#}
{#饼图#}
{#词云图#}
最终呈现出的效果（其实有点难看 ）：
经过几天的学习，我的收益也良多，从一点不通的小白，到了现在能自己设计布局，设计简单的动态效果以及在后端对前端发回的请求表单等等进行逻辑处理。看到GitHub上大佬的各种炫酷网页动态效果，我还是差的很多很多，本次实验中我最大的困难就在设计登录成功和注册成功显示的弹窗，我的想法是从后端根据if传入一个标志数给html，但是html好像是先解析页面，再读我的参数，逼的我最后不得不加了个过渡的html，而放弃了弹窗，这大概也是因为我来不及学seesion，ajax的原因有关。还有页面的布局设计，动态效果设计，花掉了我大量的时间，最终sql语句不得不借鉴了别人的开源项目来实现，本来是想搞一个爬虫的实时数据。当然，简单一点的数据库操作我们直接用from或者modelfrom就能ko。最后，这次的学习确实是让我彻底理解了django框架的运行。
