本系统是可视化系统的开发,使用Django开发web应用,使用Echarts绘制基本图表,使用前台页面的布局。
搭建一个Django的项目,该项目具有登录校验功能,一开始进入的是登录页面,用户输入用户名和密码,提交数据后与数据库中的user_info表做比对,如果用户名或者密码错误,则重新回到登录页面,并提示用户。如果用户名和密码正确,则进入系统主页面,主页面展示图表。
目录
效果展示
登录界面:
登录验证,从数据库获取数据:
登录成功后跳转到平台:
代码
首先要搭建Django项目
数据库是visual_db_2018,有如下表格
将函数封装到sqlData.py模块
from pymysql import Connect
from jieba.analyse import extract_tags
import re
import jieba
"""处理数据"""
#封装connect
def connect_mysql(sql):
conn = Connect(host="127.0.0.1",
port=3306,
user="root",
password="123456",
database="visual_db_2018",
charset="utf8")
cur = conn.cursor()
cur.execute(sql)
conn.commit()
data = cur.fetchall()
cur.close()
return data
# 仪表盘
#查询全国感染新冠肺炎的死亡率,数据来源为表covid_data
def gaugeData():
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 = connect_mysql(sql)
gauge_data = round(float(data[0][0]) * 100, 2)
return gauge_data
# 折线图
#查询出二月份全国每天的新增确诊人数,数据来源为表covid_data。2data_x为二月份的每一天,data_y为全国每一天对应的全国新增确诊人数。注意:2月13日那天的新增确诊人数是一个特殊值,这个值不做统计。
def lineData():
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 = connect_mysql(sql)
data_x = []
data_y = []
j = 0
for i in data:
data_x.append(int(i[0]))
data_y.append(i[1])
j = j + 1
return data_x,data_y
# 散点图
#查询各省市从湖北迁入的人口比例与该省累计确诊人数的关系,数据来源为表migrate_data和表covid_data。data_x为各省市从湖北迁入的人口占比,data_y为各省市累计确诊人数(截止到2020年2月29日)。
def scatterData():
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 = connect_mysql(sql)
scatter_data = []
for i in data:
scatter_data.append([i[0], int(i[1])])
return scatter_data
# 迁徙图
#查询从湖北省迁入到各省市的人口占比数据,数据来源为表migrate_data
def geoLinesData():
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 = connect_mysql(sql)
target = [114.341861, 30.546498]
series1 = []
for i in data:
dic = {}
dic["fromName"] = i[0]
dic["toName"] = i[1]
dic["value"] = i[2]
dic["coords"] = [target, [i[3], i[4]]]
series1.append(dic)
serise2 = []
for i in data:
dic = {}
dic["name"] = i[1]
dic["value"] = i[2]
serise2.append(dic)
seriseAll = [series1, serise2]
seriseAll01 = seriseAll[0]
seriseAll02 = seriseAll[1]
return seriseAll01,seriseAll02
# 地图
#查询全国各省市的累计确诊人数,数据来源为表covid_data
def mapData():
sql = """SELECT province_name,SUM(confirmed_count) FROM `covid_data`
where update_time = '2020-02-29' GROUP BY province_name"""
data = connect_mysql(sql)
#清洗数据,去掉“省市自治区回族壮族维吾尔族”
mapSeries = []
for i in data:
dic = {}
dic["name"] = i[0].strip("省市自治区回族壮族维吾尔族")
dic["value"] = int(i[1])
mapSeries.append(dic)
return mapSeries
# 饼图
#统计每个新闻媒体发布的所有新闻的数量,数据来源为news_info
def pieData():
sql = """SELECT news_source as name,COUNT(*) FROM news_info GROUP BY news_source"""
data = connect_mysql(sql)
pie_data = []
for i in data:
dic = {}
dic["value"] = i[1]
dic["name"] = i[0]
pie_data.append(dic)
return pie_data
# 主题河流
#统计每个新闻媒体每天发布的新闻的数量,数据来源为news_info
def themeRiverData():
sql = """SELECT DATE_FORMAT(publish_time,'%Y-%m-%d'),count(news_source),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 = connect_mysql(sql)
river = list(data)
for i, j in enumerate(river):
river[i] = list(j)
return river
# 词云图
#从所有的新闻概要(news_info. news_summary)中,找出tf-idf权重值最大的100个单词绘制词云图,数据来源为news_info
def wordCloudData():
sql = """SELECT news_summary FROM news_info"""
data = connect_mysql(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)
#取权重值前100的词
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.py导入该模块
from django.shortcuts import render,redirect
from pymysql import Connect
from .sqlData import *
def getData(request):
gauge_data = gaugeData()
data_x,data_y = lineData()
scatter_data = scatterData()
data_geoLine_lines, data_geoLine_scatter = geoLinesData()
data_map = mapData()
pie_data = pieData()
river_data = themeRiverData()
key,value = wordCloudData()
return render(request,"mainBox.html",{"data_x":data_x,"data_y":data_y,
"scatter_data":scatter_data,
"gauge_data":gauge_data,
"data_geoLine_lines": data_geoLine_lines,
"data_geoLine_scatter": data_geoLine_scatter,
"data_map": data_map,
"pie_data":pie_data,
"river_data":river_data,
"key":key,"value":value})
def to_login(request):
return render(request, "login.html")
def check_login(request):
username = request.POST["username"]
password = request.POST.get("password")
conn = Connect(user="root",
password="123456",
host="127.0.0.1",
database="visual_db_2018",
port=3306,
charset="utf8")
cur = conn.cursor()
sql = """SELECT * FROM user_info WHERE username = %s AND password = %s"""
cur.execute(sql, (username, password))
data = cur.fetchall()
print(data)
cur.close()
conn.close()
if len(data) > 0:
return redirect("/req/")
else:
return render(request, "login.html", context={"msg": "用户名或密码错误"})
在urls.py配置路径
from django.contrib import admin
from django.urls import path
from .views import *
urlpatterns = [
path('admin/', admin.site.urls),
path('', to_login),
path('doLogin/', check_login),
path('req/',getData)
]
mainBox.html
在pycharm写html有点鸡肋,缩进不好调试
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Title</title>
<script src="../static/echarts.js"></script>
<script src="../static/china.js" charset="utf-8"></script>
<script src="../static/echarts-wordcloud.js"></script>
<link rel="stylesheet" href="../static/css/index.css">
<script src="../static/flexible.js"></script>
</head>
<body>
<header>
<h1>国内疫情数据综合可视化平台</h1>
</header>
<div class="mainbox">
<!-- 左边-->
<div class="column">
<!--仪表盘-->
<div class="panel">
<div id="gauge" style="width:500px;height:300px;margin:40px auto;"></div>
<script type="text/javascript">
var gauge=echarts.init(document.getElementById('gauge'));
var option = {
"title": [{
"text": "国内新冠疫情死亡率",
"left": "center",
"top": "auto",
"textStyle": {
"fontSize": 18,
color: "#fff",
},
"subtextStyle": {
"fontSize": 12,
color: "#fff",
}
}],
series: [{
type: "gauge",
color: "#fff",
detail: {
formatter: "{value}%",
color: "#fff",
},
data: [{
value: {{gauge_data}},
name: "死亡率",
detail: {
color:"#fff",
fontSize: 25
}
}],
max: 10,
progress: {
itemStyle: {
color: "#fff",}
},
splitLine: {
show: false,
distance: 15
}
}]
};
gauge.setOption(option);
</script>
</div>
<!--折线图-->
<div class="panel">
<div id="line" style="width: 500px; height: 300px;margin:30px 5px;"></div>
<script type="text/javascript">
var Line=echarts.init(document.getElementById('line'));
var option={
"title": [{
"text": "全国每日新增的确诊人数(二月份)",
"left": "center",
"top": "auto",
"textStyle": {
"fontSize": 18,
color: "#fff",
},
"subtextStyle": {
"fontSize": 12,
color: "#fff",
}
}],
xAxis: {
data: {{data_y}},
axisLine: {
lineStyle: {
// 设置x轴颜色
color: '#fff'
}
},
},
yAxis: {
show: true,
type: 'value',
axisLine: {
lineStyle: {
// 设置y轴颜色
color: '#fff'
}
},
},
series: [{
name:"确诊人数",
data: {{data_x}},
type: 'line',
itemStyle: {
color: "#fff",
normal: {
color: 'red',
lineStyle: {
color: 'red'
}
}
},
}],
tooltip : {
trigger: 'axis',
axisPointer: {
type: 'cross',
label: {
backgroundColor: '#6a7985'
},
formatter: function (params) {
var color = params.color;
<!-- //图例颜色-->
var htmlStr ='<div>';
htmlStr += params.name+'日'+'<br/>';
<!-- //x轴的名称-->
<!-- //为了保证和原来的效果一样,这里自己实现了一个点的效果-->
htmlStr += '<span style="margin-right:5px;display:inline-block;width:10px;height:10px;border-radius:5px;background-color:'+color+';"></span>';
<!-- // 文本颜色设置--2020-07-23(需要设置,请解注释下面一行)-->
//htmlStr += '<span style="color:'+color+'">';
<!-- //添加一个汉字,这里你可以格式你的数字或者自定义文本内容-->
htmlStr += params.seriesName + ':'+params.value + 人;
<!-- // 文本颜色设置--2020-07-23(需要设置,请解注释下面一行)-->
//htmlStr += '</span>';
htmlStr += '</div>';
return htmlStr;
},
}
}
};
Line.setOption(option);
</script>
</div>
<!--散点图-->
<div class="panel">
<div id="scatter" style="width: 500px; height: 300px;margin:30px auto"></div>
<script type="text/javascript">
var Scatter=echarts.init(document.getElementById('scatter'));
var option={
title: {
text: "各省市从湖北迁入的人口比例与该省累计确诊人数的关系",
left: "center",
textStyle: {
fontSize: 18,
color: "#fff",
}
},
tooltip: {
trigger: 'item',
formatter: function (params) {
var color = params.color;
<!-- //图例颜色-->
var htmlStr ='<div>';
htmlStr += params.name;
<!-- //x轴的名称-->
<!-- //为了保证和原来的效果一样,这里自己实现了一个点的效果-->
htmlStr += '<span style="margin-right:5px;display:inline-block;width:10px;height:10px;border-radius:5px;background-color:'+color+';"></span>';
<!-- // 文本颜色设置--2020-07-23(需要设置,请解注释下面一行)-->
//htmlStr += '<span style="color:'+color+'">';
<!-- //添加一个汉字,这里你可以格式你的数字或者自定义文本内容-->
htmlStr += '占比'+params.seriesName + ':'+params.value + '人'+ '<br/>';
<!-- // 文本颜色设置--2020-07-23(需要设置,请解注释下面一行)-->
//htmlStr += '</span>';
htmlStr += '</div>';
return htmlStr;
}
},
yAxis: {
show: true,
axisLine: {
lineStyle: {
// 设置x轴颜色
color: '#fff'
}
},
},
xAxis: {
type: "value",
axisLine: {
lineStyle: {
// 设置y轴颜色
color: '#fff'
}
},
},
series: [
{
type: "scatter",
name: "",
data: {{scatter_data | safe}}
},
]
};
Scatter.setOption(option);
</script>
</div>
</div>
<!--中间-->
<div class="column">
<!--迁徙图-->
<div class="a4">
<div id="map1" style="width:700px;height:500px;margin:30px auto;"></div>
<script type="text/javascript">
var myChart = echarts.init(document.getElementById("map1"));
var option = {
title: [{
text: "人口迁徙图",
left: "center",
top: "auto",
textStyle: {
fontSize: 18,
color: "#fff",
},
subtextStyle: {
fontSize: 18
}
}],
tooltip: {
trigger: 'item',
formatter: '{b}<br/>{c} (迁徙人数占比)'
},
series: [{
"type": "lines",
"zlevel": 1,
"effect": {
"show": true,
"period": 6,
"trailLength": 0,
"color": "#fff",
"symbol": "arrow",
"symbolSize": 6
},
"symbol": [
"none",
"arrow"
],
"symbolSize": 6,
"data": {{data_geoLine_lines | safe}},
"lineStyle": {
"normal": {
"width": 1,
"opacity": 1,
"curveness": 0.3,
"type": "solid",
"color": "red"
}
}
},
{
"type": "scatter",
<!-- "zlevel": 2,-->
"coordinateSystem": "geo",
"symbolSize": 5,
color: "red",
name:"人数:",
"data": {{data_geoLine_scatter|safe}},
"label": {
"normal": {
"show": true,
"position": "top",
"textStyle": {
"fontSize": 12
}
},
"emphasis": {
"show": true,
"textStyle": {
"fontSize": 12
}
}
},
<!-- "tooltip": {-->
<!-- "formatter": "{b}"-->
<!-- }-->
}
],
"geo": {
"map": "china",
<!-- 控制地图不缩放-->
"roam": false,
"label": {
"emphasis": {
"show": true,
"textStyle": {
"color": "#000"
}
}
},
"itemStyle": {
"normal": {
"areaColor": "#46A3FF",
"borderColor": "#111"
},
"emphasis": {
"areaColor": "#fff"
}
}
},
};
myChart.setOption(option);
</script>
</div>
<!--地图-->
<div class="a5">
<div id="map2" style="width:670px;height:490px;margin:0px auto;"></div>
<script type="text/javascript">
var myChart = echarts.init(document.getElementById("map2"));
var option = {
title: {
text: "各个省市累计确诊人数",
left: "center",
textStyle: {
fontSize: 18,
color:"#fff"
}
},
tooltip: {
trigger: 'item',
formatter: '{b}<br/>{c} (确诊人数)'
},
"series": [{
name:"确诊人数",
"type": "map",
"symbol": "circle",
"label": {
"normal": {
"show": false,
"position": "top",
"textStyle": {
"fontSize": 18,
color: "#fff",
}
},
"emphasis": {
"show": true,
"textStyle": {
"fontSize": 12
}
}
},
"mapType": "china",
name:"确诊人数:",
"data": {{data_map|safe}},
"roam": false,
}],
"visualMap": {
"type": "piecewise",
"min": 0,
"max": 100,
"text": [
"",
""
],
"textStyle": {color:"#fff"},
"inRange": {
"color": [
"pink",
"red",
"#560707"
]
},
<!-- "calculable": true,-->
"splitNumber": 5,
"orient": "horizontal",
"left": "center",
"top": "bottom",
"showLabel": true,
"pieces": [{
"max": 100,
"min": 0,
"label": "<100"
},
{
"max": 500,
"min": 101,
"label": "100-500"
},
{
"max": 800,
"min": 501,
"label": "500-800"
},
{
"max": 1000,
"min": 801,
"label": "800-1000"
},
{
"max": 1500,
"min": 1001,
"label": "1000-1500"
},
{
"max": 100000,
"min": 1501,
"label": ">1500"
}
]
}
};
myChart.setOption(option);
</script>
</div>
</div>
<!-- 右边-->
<div class="column">
<!--饼图-->
<div class="panel">
<div id="pie" style="width:500px;height:300px"></div>
<script type="text/javascript">
var myChart = echarts.init(document.getElementById("pie"));
option = {
title: {
text: '各媒体发布的新闻占比情况',
left: 'center',
textStyle: {
color: '#fff',
fontSize: 18,
}
},
tooltip: {
trigger: 'item'
},
legend: {
top: '5%',
right: '2%',
orient: 'vertical',
textStyle: {
color: '#fff',
}
},
series: [{
name: '访问来源',
type: 'pie',
radius: ['45%', '90%'],
center: ['50%', '55%'],
avoidLabelOverlap: false,
itemStyle: {
borderRadius: 10,
borderColor: '#fff',
borderWidth: 2
},
label: {
show: true,
position: 'inside',
formatter: '{d}%',
},
emphasis: {
label: {
show: true,
fontSize: '40',
fontWeight: 'bold'
}
},
labelLine: {
show: false
},
data: {{pie_data|safe}}
}]
};
myChart.setOption(option);
</script>
</div>
<!--主题河流-->
<div class="panel">
<div id="themeRiver" style="width:500px;height:300px;margin:20px auto;"></div>
<script type="text/javascript">
var myChart = echarts.init(document.getElementById("themeRiver"));
option = {
title: {
text: '各媒体每一天的新闻数量',
left: 'center',
textStyle: {
color: '#fff',
fontSize: 18,
}
},
tooltip: {
trigger: 'axis',
axisPointer: {
type: 'line',
lineStyle: {
color: 'rgba(0,0,0,0.2)',
width: 1,
type: 'solid'
}
}
},
legend: {
data: ['国家卫健委', '央视网', '人民日报', '新华社'],
top: '8%',
right: 'center',
textStyle: {
color: '#fff',
}
},
singleAxis: {
top: 50,
bottom: 50,
axisTick: {},
axisLabel: {},
type: 'time',
axisPointer: {
animation: true,
label: {
show: true
}
},
axisLine: {
lineStyle: {
color: "#fff"
}
},
splitLine: {
show: true,
lineStyle: {
type: 'dashed',
opacity: 0.2
}
}
},
series: [{
type: 'themeRiver',
emphasis: {
itemStyle: {
shadowBlur: 20,
shadowColor: 'rgba(0, 0, 0, 0.8)'
}
},
data: {{river_data|safe}},
}]
};
myChart.setOption(option);
</script>
</div>
<!--词云图-->
<div class="panel">
<div id="china" style="width:500px;height:300px;margin:20px auto;"></div>
<script>
var myChart1 = echarts.init(document.getElementById('china'));
key = {{key|safe}};
value = {{value|safe}};
var data = [];
for (var i=0;i<value.length;i++){
data.push(
{ value:value[i],
name:key[i]})}
var option1 ={
//设置标题,居中显示
title:{
text: '所有新闻的关键字',
left:'center',
textStyle: {
color: '#fff',
fontSize: 18,
}
},
//数据可以点击
tooltip:{
show:true
},
series:[
{
//词的类型
type: 'wordCloud',
//设置字符大小范围
gridSize:4,
sizeRange:[10,70],
<!-- shape:'star',-->
rotationRange:[-45,90],
textStyle: {
normal:{
//生成随机的字体颜色
color:function () {
return 'rgb(' + [
Math.round(Math.random() * 160),
Math.round(Math.random() * 160),
Math.round(Math.random() * 160)
].join(',')+')';
}
}
},
//不要忘记调用数据
data:data
}
]
};
myChart1.setOption(option1);
</script>
</div>
</div>
</div>
</body>
</html>
index.css
在vccode写好样式再复制过来的,用了rem适配,要下载插件cssrem
cssrem 插件的基准值是 80px
插件-配置按钮---配置扩展设置--Root Font Size 里面 设置。
但是别忘记重启vscode软件保证生效
* {
margin: 0;
padding: 0;
box-sizing: border-box;
}
body {
font-family: Arial, Helvetica, sans-serif;
margin: 0;
padding: 0;
/* 行高是字体的1.15倍 */
line-height: 1.15;
background-color: #161523;
}
header {
position: relative;
height: 1.25rem;
background: url(../img/header_bg.png) no-repeat top center;
background-size: 100% 100%;
}
header h1 {
font-size: 0.475rem;
color: #fff;
text-align: center;
line-height: 1rem;
}
.mainbox {
min-width: 1024px;
max-width: 1920px;
padding: 0.125rem 0.125rem 0;
display: flex;
margin:auto;
}
.mainbox .column {
flex: 3;
/* 整个屏幕平均分为3列 */
}
/* 设置中间列占5份 */
.mainbox .column:nth-child(2) {
flex: 5;
margin: 0 0.125rem 0.1875rem;
overflow: hidden;
}
.panel {
position: relative;
height: 3.875rem;
/*310px */
border: 1px solid rgba(25, 186, 139, 0.17);
background-color: rgba(255, 255, 255, 0.04);
border-radius: 10px;
padding: 0 0.1875rem 0.5rem;
margin-bottom: 0.1875rem;
}
.a4,
.a5{
position: relative;
height: 5.91rem;
border: 1px solid rgba(25, 186, 139, 0.17);
background-color: rgba(255, 255, 255, 0.04);
border-radius: 10px;
padding: 0 0.1875rem 0.5rem;
margin-bottom: 0.1875rem;
}
注意:导入的flexible.js 把屏幕分为 24 等份。