上次文章主要介绍了全球新闻数据的下载,以及一些基本的利用python进行处理操作,本次介绍相关数据的查询和可视化操作。
目录
一、俄乌冲突数据提取
1、筛选俄乌冲突
CREATE TABLE export_rus_ukr LIKE export;
INSERT INTO export_rus_ukr SELECT *
FROM
export
WHERE
(Actor1CountryCode = "RUS" AND Actor2CountryCode = "UKR" )
OR ( Actor1CountryCode = "UKR" AND Actor2CountryCode = "RUS" )
OR (SOURCEURL LIKE '%Russia%' AND SOURCEURL LIKE '%Ukrain%')
SELECT count(*) FROM export_rus_ukr;
SELECT Actor1CountryCode,Actor2CountryCode,SOURCEURL FROM export_rus_ukr WHERE
SOURCEURL LIKE '%Russia%' OR SOURCEURL LIKE '%Ukrainian%';
根据Actor1CountryCode、Actor2CountryCode、以及SOURCEFUL字段进行筛选,得到结果如下
2、俄乌冲突查询
(1)创建视图
视图可以加跨我们的查询,并占用较少的内存空间
create view AvgTone_mod AS
select GLOBALEVENTID, MonthYear, Actor1CountryCode as Country, quadclass,
GoldsteinScale, AvgTone, SOURCEURL
FROM export_rus_ukr
where Actor1CountryCode is not null;
(2)相关信息查询
主要查询了每月的新闻总数,每月的不同情绪新闻数量,情绪占比等等,为搭建可视化大屏提供后端技术栈支持
-- 1、每月乌克兰、俄罗斯新闻数
select MonthYear,count(GLOBALEVENTID) as count_event
FROM avgtone_mod
GROUP BY MonthYear
-- 2、每月不同情绪的新闻数
select MonthYear, AvgTone, Count(GLOBALEVENTID) as count_event
FROM avgtone_mod
GROUP BY MonthYear, AvgTone
ORDER BY AvgTone,MonthYear
-- 3、总体不同情绪的新闻占比
SELECT p.AvgTone, p.count_event/s.sum
FROM
(select AvgTone, Count(GLOBALEVENTID) as count_event
FROM avgtone_mod
GROUP BY AvgTone) p,
(select count(GLOBALEVENTID) as sum
FROM avgtone_mod) s
-- 4、每月不同情绪的新闻占比
SELECT p.MonthYear, p.AvgTone, p.count_event/s.sum
FROM
(select MonthYear, AvgTone, Count(GLOBALEVENTID) as count_event
FROM avgtone_mod
GROUP BY MonthYear, AvgTone) p,
(select MonthYear,count(GLOBALEVENTID) as sum
FROM avgtone_mod
GROUP BY MonthYear) s
WHERE p.MonthYear=s.MonthYear
-- 5、每月冲突与合作的数量
SELECT monthyear, quadclass, count(GLOBALEVENTID) as count_event
FROM AvgTone_mod
GROUP BY QuadClass,MonthYear
二、可视化与数据库连接
1、可视化
本次可视化分为纯html页面和php页面两种,我们首先利用php连接数据库进行可视化操作,这样使得 可视化图像能够随着数据库内数据改变而改变。但由于数据量大,在进行可视化图像加载时需要花费较 大的时间。因此我们制作了html静态页面,将查询所得数据直接写入表中,由此得到纯html静态页面。 并最终将页面部署到服务器。
主要用到echarts
echarts链接:https://echarts.apache.org/zh/index.html
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Title</title>
</head>
<body>
<div id="main" style="height:300px;"></div>
</body>
例如这里添加一个名为main的盒子,然后再script中使用echarts
<script>
var myChart = echarts.init(document.getElementById('main'));
var option = {
series: [{
radius:'80%',
type: 'liquidFill',
data: [0.94,0.05,0.04,0.03,0.02,0.01],
label: {
normal:{
textStyle:{
fontSize:30,
fontWeight: 'normal',
}}
},
}]
};
// 使用刚指定的配置项和数据显示图表。
myChart.setOption(option);
</script>
此处记得在html的头文件中引入echarts,有两种方法:
(1)直接从GitHub下载源码,然后将源码放置在自己的js文件夹下进行引入
根据自己的需要下载即可
引用方法:
<script src="../js/echarts.js"></script>
(2)从cdn线上引入,这种方法不需要下载源码
<script src="https://cdn.bootcdn.net/ajax/libs/echarts/5.4.2/echarts.js"></script>
绘制出来的水球图:
可视化整体效果如图所示:
2、PHP连接数据库
通过php连接数据库能够进行实时查询,得到数据库中的最新数据,只需要将文件在Apache相关文件夹下,在网页中输入地址即可得到连接数据库的数据,这样,数据库更新图表也能实时更新。
(1)php连接数据库
coon.php文件
<?php
$link=mysqli_connect("localhost","root","password");
mysqli_select_db($link,"news");
mysqli_query($link,"set names 'utf8'");
?>
(2)读取数据库信息并传递
totalnews.php文件
<?php
session_start();
require("D:/PHP/htdocs/news/componets/coon.php");
//查询代码
$sql = "SELECT MonthYear,COUNT(GLOBALEVENTID) num
FROM export_rus_ukr
GROUP BY MonthYear
ORDER BY MonthYear";
$result = mysqli_query($link, $sql);
$data = array();
$as = "";
class User
{
public $monthyear;
public $num;
}
while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
$user = new User();
$user->monthyear = $row['MonthYear'];
$user->num = $row['num'];
$data[] = $user;
}
echo json_encode($data);
//mysqli_close($conn);// 关闭MySQL数据库连接
?>
(3)前端获取数据
利用ajax获取php传来的数据,并将数据传给echarts进行绘图
index.html文件
<script type="text/javascript">
var arr = new Array();//数字
var res = new Array();//名字
var option;
function getNumber() {
$.ajax({
type: "post",
async: false, //异步执行
url: "../sql/totalnews.php", //SQL数据库文件
data: {}, //发送给数据库的数据
dataType: "json", //json类型
success: function(result) {
if (result) {
for (var i = 0; i < result.length; i++) {
res.push(result[i].monthyear);
arr.push(result[i].num);
}
}
},
error: function() {
alert('Ajax request 发生错误');
}
})
return res, arr;
}
</script>
至此便完成了数据库的连接和可视化工作。
下次将介绍利用Bert和LSTM进行新闻文本的情感分析