基于第一个项目爬虫爬取的数据（3-5个数据源），完成数据展示网站。 基本要求： 1、用户可注册登录网站，非注册用户不可登录查看数据 2、用户注册、登录、查询等操作记入数据库中的日志 3、实现查询词支持布尔表达式 （比如“新冠 AND 肺炎”或者“新冠 OR 肺炎”） 4、爬虫数据查询结果列表支持分页和排序 5、用Echarts或者D3实现3个以上的数据分析图表展示在网站中
对登录与注册提出的要求：
1、对用户要有适当提示
2、登录
（1）提示用户名或密码输错
（2）用户不存在
3、注册
（1）两次密码不一致
（2）用户已存在
（3）注册成功跳转登陆页面
创建用户信息数据表
CREATE TABLE `crawl`.`user`( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `username` VARCHAR(45) NOT NULL, `password` VARCHAR(45) NOT NULL, `registertime` datetime DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY(`id`), UNIQUE KEY `username_UNIQUE`(`username`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;记录用户的登陆，查询（具体查询语句）操作
CREATE TABLE `crawl`.`user_action`( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `username` VARCHAR(45) NOT NULL, `request_time` VARCHAR(45) NOT NULL, `request_method` VARCHAR(20) NOT NULL, `request_url` VARCHAR(300) NOT NULL, `status` int(4), `remote_addr` VARCHAR(1000) NOT NULL, PRIMARY KEY(`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;建立连接mysql配置文件
module.exports = { mysql: { host: 'localhost', user: 'root', password: 'root' database: 'crawl' connectionLimit: 10//最大连接数，默认为10 } };登录页的实现
检查用户所输入的账户密码是否与数据库中的相匹配，如果一致则登陆成功，页面跳转至news.html，否则无法跳转。
<link rel="stylesheet" type="text/css" href="stylesheets/index.css"> <script type="text/javascript" src="javascripts/index.js"></script> <script> var app = angular.module('login', []); app.controller('loginCtrl', function ($scope, $http, $timeout) { // 登录时，检查用户输入的账户密码是否与数据库中的一致 $scope.check_pwd = function () { var data = JSON.stringify({ username: $scope.username, password: $scope.password }); $http.post("/users/login", data) .then( function (res) { if(res.data.msg=='ok') { window.location.href='/news.html';//如果登陆成功则页面跳转 }else{ $scope.msg=res.data.msg; } }, function (err) { $scope.msg = err.data; }); };接着设置登陆页面的信息
<form id="login-form" method="post" role="form" style="display: block;"> <!-- 登陆部分--> <div class="form-group"> <input ng-model="username" tabindex="1" class="form-control" placeholder="Username" value=""/> </div> <div class="form-group"> <input type="password" ng-model="password" tabindex="2" class="form-control" placeholder="Password"> </div> <!-- <div class="form-group text-center">--> <!-- <input type="checkbox" tabindex="3" class="" name="remember" id="remember">--> <!-- <label for="remember"> Remember Me</label>--> <!-- </div>--> <div class="form-group"> <div class="row"> <div class="col-sm-6 col-sm-offset-3"> <button id="login-submit" tabindex="4" class="form-control btn btn-login" ng-click="check_pwd()">LOG IN</button> </div> </div> </div> </form>再设置登录页的路由
var express = require('express'); var router = express.Router(); var userDAO = require('../dao/userDAO'); router.post('/login', function(req, res) { var username = req.body.username; var password = req.body.password; // var sess = req.session; userDAO.getByUsername(username, function (user) { if(user.length==0){ res.json({msg:'用户不存在！请检查后输入'}); }else { if(password===user[0].password){ req.session['username'] = username; res.cookie('username', username); res.json({msg: 'ok'}); // res.json({msg:'ok'}); }else{ res.json({msg:'用户名或密码错误！请检查后输入'}); } } }); });设置session信息，以确保知晓是哪个用户正在对日志进行操作
//设置session app.use(session({ secret: 'sessiontest',//与cookieParser中的一致 resave: true, saveUninitialized: false, // 是否保存未初始化的会话 cookie : { maxAge : 1000 * 60 * 60, // 设置 session 的有效时间，单位毫秒 }, }));其中调用的userDAO
var mysql = require('mysql'); var mysqlConf = require('../conf/mysqlConf'); var userSqlMap = require('./userSqlMap'); var pool = mysql.createPool(mysqlConf.mysql); // 使用了连接池，重复使用数据库连接，而不必每执行一次CRUD操作就获取、释放一次数据库连接，从而提高了对数据库操作的性能。 module.exports = { add: function (user, callback) { pool.query(userSqlMap.add, [user.username, user.password], function (error, result) { if (error) throw error; callback(result.affectedRows > 0); }); }, getByUsername: function (username, callback) { pool.query(userSqlMap.getByUsername, [username], function (error, result) { if (error) throw error; callback(result); }); }, };于是便能得到登陆界面
注册页面的实现
首先检测用户输入的两次密码是否一致，若一致则能成功注册，否则注册失败。
//增加注册用户 $scope.doAdd = function () { // 检查用户注册时，输入的两次密码是否一致 if($scope.add_password!==$scope.confirm_password){ // $timeout(function () { // $scope.msg = '两次密码不一致！'; // },100); $scope.msg = '两次密码不一致！'; } else { var data = JSON.stringify({ username: $scope.add_username, password: $scope.add_password }); $http.post("/users/register", data) .then(function (res) { if(res.data.msg=='成功注册！请登录') { $scope.msg=res.data.msg; $timeout(function () { window.location.href='index.html'; },2000); } else { $scope.msg = res.data.msg; } }, function (err) { $scope.msg = err.data; }); } };设置注册页面的信息
<form id="register-form" method="post" role="form" style="display: none;"> <div class="form-group"> <input ng-model="add_username" tabindex="1" class="form-control" placeholder="Username" value=""/> </div> <div class="form-group"> <input type="password" ng-model="add_password" tabindex="2" class="form-control" placeholder="Password"> </div> <div class="form-group"> <input type="password" ng-model="confirm_password" tabindex="2" class="form-control" placeholder="Confirm Password"> </div> <div class="form-group"> <div class="row"> <div class="col-sm-6 col-sm-offset-3"> <button tabindex="4" class="form-control btn btn-register" ng-click="doAdd()">Register Now</button> </div> </div> </div> </form>设置注册页面的路由
/* add users */ router.post('/register', function (req, res) { var add_user = req.body; // 先检查用户是否存在 userDAO.getByUsername(add_user.username, function (user) { if (user.length != 0) { // res.render('index', {msg:'用户不存在！'}); res.json({msg: '用户已存在！'}); }else { userDAO.add(add_user, function (success) { res.json({msg: '成功注册！请登录'}); }) } }); });
退出登录
退出登录的页面设置
<li> <a href="#" class="dropdown-toggle" data-toggle="dropdown">账号管理<span class="caret"></span></a> <ul class="dropdown-menu"> <li class="dropdown-header">账号</li> <li><a ng-click="logout()">退出登录</a></li> </ul> </li>设置路由
// 退出登录 router.get('/logout', function(req, res, next){ // 备注：这里用的 session-file-store 在destroy 方法里，并没有销毁cookie // 所以客户端的 cookie 还是存在，导致的问题 --> 退出登陆后，服务端检测到cookie // 然后去查找对应的 session 文件，报错 // session-file-store 本身的bug req.session.destroy(function(err) { if(err){ res.json('退出登录失败'); return; } // req.session.loginUser = null; res.clearCookie('username'); res.json({result:'/index.html'}); }); }); module.exports = router;首先引入查询页面
<div ng-include="'search.html'"></div>处理查询页面后端
// 查询数据 $scope.search = function () { var title1 = $scope.title1; var title2 = $scope.title2; var selectTitle = $scope.selectTitle; var content1 = $scope.content1; var content2 = $scope.content2; var selectContent = $scope.selectContent; var sorttime = $scope.sorttime; // 检查用户传的参数是否有问题 //用户有可能这样输入：___ and/or 新冠（直接把查询词输在了第二个位置） if(typeof title1=="undefined" && typeof title2!="undefined" && title2.length>0){ title1 = title2; } if(typeof content1=="undefined" && typeof content2!="undefined" && content2.length>0){ content1 = content2; } // 用户可能一个查询词都不输入，默认就是查找全部数据 var myurl = `/news/search?t1=${title1}&ts=${selectTitle}&t2=${title2}&c1=${content1}&cs=${selectContent}&c2=${content2}&stime=${sorttime}`; $http.get(myurl).then( function (res) { if(res.data.message=='data'){ $scope.isisshowresult = true; //显示表格查询结果 // $scope.searchdata = res.data; $scope.initPageSort(res.data.result) }else { window.location.href=res.data.result; } },function (err) { $scope.msg = err.data; }); };
查询页路由
router.get('/search', function(request, response) { console.log(request.session['username']); //sql字符串和参数 if (request.session['username']===undefined) { // response.redirect('/index.html') response.json({message:'url',result:'/index.html'}); }else { var param = request.query; newsDAO.search(param,function (err, result, fields) { response.json({message:'data',result:result}); }) } });其中调用的newsDAO.search函数
search :function(searchparam, callback) { // 组合查询条件 var sql = 'select * from fetches '; if(searchparam["t2"]!="undefined"){ sql +=(`where title like '%${searchparam["t1"]}%' ${searchparam['ts']} title like '%${searchparam["t2"]}%' `); }else if(searchparam["t1"]!="undefined"){ sql +=(`where title like '%${searchparam["t1"]}%' `); }; if(searchparam["t1"]=="undefined"&&searchparam["t2"]=="undefined"&&searchparam["c1"]!="undefined"){ sql+='where '; }else if(searchparam["t1"]!="undefined"&&searchparam["c1"]!="undefined"){ sql+='and '; } if(searchparam["c2"]!="undefined"){ sql +=(`content like '%${searchparam["c1"]}%' ${searchparam['cs']} content like '%${searchparam["c2"]}%' `); }else if(searchparam["c1"]!="undefined"){ sql +=(`content like '%${searchparam["c1"]}%' `); } if(searchparam['stime']!="undefined"){ if(searchparam['stime']=="1"){ sql+='ORDER BY publish_date ASC '; }else { sql+='ORDER BY publish_date DESC '; } } sql+=';'; pool.getConnection(function(err, conn) { if (err) { callback(err, null, null); } else { conn.query(sql, function(qerr, vals, fields) { conn.release(); //释放连接 callback(qerr, vals, fields); //事件驱动回调 }); } }); }, };查询结果显示
<!--显示查询结果--> <div ng-show="isisshowresult"> <table class="table table-striped"> <thead> <tr> <td>序号</td> <td>标题</td> <td>作者</td> <!-- <td>内容</td>--> <td>关键词</td> <td>链接</td> <td>发布时间</td> </tr> </thead> <tbody> <tr ng-repeat="(key, item) in items"> <td>{{index+key}}</td> <td>{{item.title}}</td> <td>{{item.author}}</td> <!-- <td>{{item.content}}</td>--> <td>{{item.keywords}}</td> <td>{{item.url}}</td> <td>{{item.publish_date}}</td> </tr> </tbody> </table>设置前端
<div class="pull-right"> <nav> <ul class="pagination"> <li> <a ng-click="Previous()" role="button"><span role="button">上一页</span></a> </li> <li ng-repeat="page in pageList" ng-class="{active:isActivePage(page)}" role="button"> <a ng-click="selectPage(page)" >{{ page }}</a> </li> <li> <a ng-click="Next()" role="button"><span role="button">下一页</span></a> </li> </ul> </nav> </div>用angular写分页代码
// 分页 $scope.initPageSort=function(item){ $scope.pageSize=5; //每页显示的数据量，可以随意更改 $scope.selPage = 1; $scope.data = item; $scope.pages = Math.ceil($scope.data.length / $scope.pageSize); //分页数 $scope.pageList = [];//最多显示5页，后面6页之后不会全部列出页码来 $scope.index = 1; // var page = 1; // for (var i = page; i < $scope.pages+1 && i < page+5; i++) { // $scope.pageList.push(i); // } var len = $scope.pages> 5 ? 5:$scope.pages; $scope.pageList = Array.from({length: len}, (x,i) => i+1); //设置表格数据源(分页) $scope.items = $scope.data.slice(0, $scope.pageSize); };显示当前页面
//打印当前选中页 $scope.selectPage = function (page) { //不能小于1大于最大（第一页不会有前一页，最后一页不会有后一页） if (page < 1 || page > $scope.pages) return; //最多显示分页数5，开始分页转换 var pageList = []; if(page>2){ for (var i = page-2; i <= $scope.pages && i < page+3; i++) { pageList.push(i); } }else { for (var i = page; i <= $scope.pages && i < page+5; i++) { pageList.push(i); } } $scope.index =(page-1)*$scope.pageSize+1; $scope.pageList = pageList; $scope.selPage = page; $scope.items = $scope.data.slice(($scope.pageSize * (page - 1)), (page * $scope.pageSize));//通过当前页数筛选出表格当前显示数据 console.log("选择的页：" + page); };设置选中页面的样式
//设置当前选中页样式 $scope.isActivePage = function (page) { return $scope.selPage == page; }; //上一页 $scope.Previous = function () { $scope.selectPage($scope.selPage - 1); }; //下一页 $scope.Next = function () { $scope.selectPage($scope.selPage + 1); };
饼状图
$scope.pie = function () { $scope.isShow = false; $http.get("/news/pie").then( function (res) { if(res.data.message=='url'){ window.location.href=res.data.result; }else { let newdata = []; var pattern = /责任编辑：(.+)/;//匹配名字 res.data.result.forEach(function (element) { // "x": 责任编辑：李夏君 ,对x进行处理,只取 名字 newdata.push({name: pattern.exec(element["x"])[1], value: element["y"]}); }); var myChart = echarts.init(document.getElementById('main1')); var app = {}; option = null; // 指定图表的配置项和数据 var option = { title: { text: '作者发布新闻数量', x: 'center' }, tooltip: { trigger: 'item', formatter: "{a} <br/>{b} : {c} ({d}%)" }, legend: { orient: 'vertical', left: 'left', // data: ['直接访问', '邮件营销', '联盟广告', '视频广告', '搜索引擎'] }, series: [ { name: '访问来源', type: 'pie', radius: '55%', center: ['50%', '60%'], data: newdata, itemStyle: { emphasis: { shadowBlur: 10, shadowOffsetX: 0, shadowColor: 'rgba(0, 0, 0, 0.5)' } } } ] }; // myChart.setOption(option); app.currentIndex = -1; setInterval(function () { var dataLen = option.series[0].data.length; // 取消之前高亮的图形 myChart.dispatchAction({ type: 'downplay', seriesIndex: 0, dataIndex: app.currentIndex }); app.currentIndex = (app.currentIndex + 1) % dataLen; // 高亮当前图形 myChart.dispatchAction({ type: 'highlight', seriesIndex: 0, dataIndex: app.currentIndex }); // 显示 tooltip myChart.dispatchAction({ type: 'showTip', seriesIndex: 0, dataIndex: app.currentIndex }); }, 1000); if (option && typeof option === "object") { myChart.setOption(option, true); } ; } }); };折线图
$scope.line = function () { $scope.isShow = false; $http.get("/news/line").then( function (res) { if(res.data.message=='url'){ window.location.href=res.data.result; }else { var myChart = echarts.init(document.getElementById("main1")); option = { title: { text: '"疫情"该词在新闻中的出现次数随时间变化图' }, xAxis: { type: 'category', data: Object.keys(res.data.result) }, yAxis: { type: 'value' }, series: [{ data: Object.values(res.data.result), type: 'line', itemStyle: {normal: {label: {show: true}}} }], }; if (option && typeof option === "object") { myChart.setOption(option, true); } } }); };柱状图
$scope.histogram = function () { $scope.isShow = false; $http.get("/news/histogram") .then( function (res) { if(res.data.message=='url'){ window.location.href=res.data.result; }else { // var newdata = washdata(data); let xdata = [], ydata = [], newdata; var pattern = /\d{4}-(\d{2}-\d{2})/; res.data.result.forEach(function (element) { // "x":"2020-04-28T16:00:00.000Z" ,对x进行处理,只取 月日 xdata.push(pattern.exec(element["x"])[1]); ydata.push(element["y"]); }); newdata = {"xdata": xdata, "ydata": ydata}; var myChart = echarts.init(document.getElementById('main1')); // 指定图表的配置项和数据 var option = { title: { text: '新闻发布数 随时间变化' }, tooltip: {}, legend: { data: ['新闻发布数'] }, xAxis: { data: newdata["xdata"] }, yAxis: {}, series: [{ name: '新闻数目', type: 'bar', data: newdata["ydata"] }] }; // 使用刚指定的配置项和数据显示图表。 myChart.setOption(option); } }, function (err) { $scope.msg = err.data; }); };后端路由
router.get('/histogram', function(request, response) { //sql字符串和参数 console.log(request.session['username']); //sql字符串和参数 if (request.session['username']===undefined) { // response.redirect('/index.html') response.json({message:'url',result:'/index.html'}); }else { var fetchSql = "select publish_date as x,count(publish_date) as y from fetches group by publish_date order by publish_date;"; newsDAO.query_noparam(fetchSql, function (err, result, fields) { response.writeHead(200, { "Content-Type": "application/json", "Cache-Control": "no-cache, no-store, must-revalidate", "Pragma": "no-cache", "Expires": 0 }); response.write(JSON.stringify({message:'data',result:result})); response.end(); }); } });图云
$scope.wordcloud = function () { $scope.isShow = false; $http.get("/news/wordcloud").then( function (res) { if(res.data.message=='url'){ window.location.href=res.data.result; }else { var mainContainer = document.getElementById('main1'); var chart = echarts.init(mainContainer); var data = []; for (var name in res.data.result) { data.push({ name: name, value: Math.sqrt(res.data.result[name]) }) } var maskImage = new Image(); maskImage.src = './images/logo.png'; var option = { title: { text: '所有新闻内容 jieba分词 的词云展示' }, series: [{ type: 'wordCloud', sizeRange: [12, 60], rotationRange: [-90, 90], rotationStep: 45, gridSize: 2, shape: 'circle', maskImage: maskImage, drawOutOfBound: false, textStyle: { normal: { fontFamily: 'sans-serif', fontWeight: 'bold', // Color can be a callback function or a color string color: function () { // Random color return 'rgb(' + [ Math.round(Math.random() * 160), Math.round(Math.random() * 160), Math.round(Math.random() * 160) ].join(',') + ')'; } }, emphasis: { shadowBlur: 10, shadowColor: '#333' } }, data: data }] }; maskImage.onload = function () { // option.series[0].data = data; chart.clear(); chart.setOption(option); }; window.onresize = function () { chart.resize(); }; } }); } });
数据库日志
