最近镇江出了一粒新冠疫情感染者，是国外回来的，已经隔离14天之后，检测到了发热，这两天人心惶惶，我们去看看论坛上大家的风向
本次分享主要包含三个分块：
论坛模拟登陆与数据抓取
数据入库与更新
数据分析与可视化
我这边的话设计思路是这样的：
每隔10分钟刷新论坛百姓话题，记录第一页的帖子，因为只要用户一直在这个帖子回复，那么这个帖子一直会在第一页，然后查看回复，抓取一段时间，把帖子和回复都存入数据库，这样就可以去分析论坛的动态了
首先：需要准备的环境有 Python，MySQL，jupyter notebook，还有代码编辑器
1.论坛模拟登陆与数据抓取
打开梦溪论坛-百姓话题 随便点开一个帖子，发现需要登录才能查看
我们点开登录页面，打开开发者模式，输入用户名密码，点击登录，发现其发送了一个post请求到服务器，并且返回了有cookie
那我们想要使用python请求的时候，需要得到cookie， 那我们就直接使用requests的post请求模拟一下，看是否能登陆成功
def login(self):
url = "https://passport.my0511.com/lphysignin.php?action=login"
headers = {
"User-agent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 11_1_0) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/87.0.4280.141 Safari/537.36",
"Referer": "http://www.my0511.com/",
}
data = {
"formhash": "4f27472c",
"referer": "index.php",
"goto":"",
"loginstep": "1",
"username": "你的用户名",
"password": "你的密码",
"loginsubmit": "(unable to decode value)",
}
r=requests.post(url,headers=headers,data=data)
# print(r.content.decode('gb18030'))
cookies = requests.utils.dict_from_cookiejar(r.cookies)
open("mengxicookie.txt",'w',encoding='utf-8').write(json.dumps(cookies))
# 登陆成功后把cookie写入到文件中，下次可以直接使用，不需要再次登陆然后我们就可以试着请求页面和详情页面
感觉有点麻烦，我这边直接把代码贴上来
# coding=utf-8
import requests,json,os,re,math,time
from lxml import etree
import pymysql
class MengXi():
def __init__(self):
self.cookies = json.loads(open('mengxicookie.txt', 'r', encoding='utf-8').read())
self.db = pymysql.connect("数据库",'用户名','密码','数据库名')
self.cursor = self.db.cursor()
self.save_dir = "./mengxi_imgs/"
if not os.path.exists(self.save_dir):
os.mkdir(self.save_dir)
def login(self):
url = "https://passport.my0511.com/lphysignin.php?action=login"
headers = {
"User-agent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 11_1_0) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/87.0.4280.141 Safari/537.36",
"Referer": "http://www.my0511.com/",
}
data = {
"formhash": "4f27472c",
"referer": "index.php",
"goto":"",
"loginstep": "1",
"username": "用户名",
"password": "密码",
"loginsubmit": "(unable to decode value)",
}
r=requests.post(url,headers=headers,data=data)
# print(r.content.decode('gb18030'))
cookies = requests.utils.dict_from_cookiejar(r.cookies)
open("mengxicookie.txt",'w',encoding='utf-8').write(json.dumps(cookies))
def index(self):
url = "http://bbs.my0511.com/f152b"
headers = {
"User-agent":"Mozilla/5.0 (Macintosh; Intel Mac OS X 11_1_0) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/87.0.4280.141 Safari/537.36",
"Referer":"http://www.my0511.com/",
}
r=requests.get(url,headers=headers,cookies=self.cookies)
html = r.content.decode("gb18030")
# open("0511.html",'w',encoding='gb18030').write(html)
html = etree.HTML(html)
dls = html.xpath("//div[@id='subcontent']/dl[@class='list_dl']")
for dl in dls:
title = ''.join(dl.xpath("./dt/a[@class='a_topic']/text()"))
url = "http://bbs.my0511.com" + ''.join(dl.xpath("./dt/a[@class='a_topic']/@href"))
author = ''.join(dl.xpath("./dd[1]/a/text()"))
author_id = ''.join(dl.xpath("./dd[1]/a/@href"))
create_time = ''.join(dl.xpath("./dd[1]/span/text()"))
comment_num = ''.join(dl.xpath("./dd[2]/span[@class='fontblue']/text()"))
read_num = ''.join(dl.xpath("./dd[2]/span[@class='tcount']/text()"))
last_report = ''.join(dl.xpath("./dd[3]/a/text()"))
last_time = ''.join(dl.xpath("./dd[3]/span/text()"))
select_sql = "select * from mengxi_title where url = '%s';"%(url)
num = self.cursor.execute(select_sql)
# print(num)
if num == 0:
sql = "insert into mengxi_title(title,url,author,author_id,comment_num,read_num,last_report,last_time,create_time,status) values('%s','%s','%s','%s',%s,%s,'%s','%s','%s','0');" % (
title, url, author, author_id.split("uid=")[-1], comment_num, read_num,
last_report, '2021-' + last_time, create_time)
else:
info = self.cursor.fetchone()
if info[5]<int(comment_num):
# print(info[5], comment_num)
sql = "update mengxi_title set status = '2',comment_num=%s,read_num=%s,last_report='%s',last_time='%s' where url = '%s';"%(comment_num,read_num,last_report,'2021-' +last_time,url)
else:
sql = ""
# print(sql)
try:
self.cursor.execute(sql)
self.db.commit()
except BaseException as e:
# print(e)
self.db.rollback()
def details(self,now):
baseurl = '-'.join(now[1].split("-")[:-1])
# print(baseurl)
page = now[2]
while True:
# url = "http://bbs.my0511.com/f152b-t7843853z-1"
url = baseurl+"-"+str(page)
# print(url)
headers = {
"User-agent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 11_1_0) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/87.0.4280.141 Safari/537.36",
"Referer": "http://www.my0511.com/f152b",
}
r=requests.get(url,headers=headers,cookies=self.cookies)
html = r.content.decode('gb18030')
if r.status_code == 404 or "审核中" in html or "您无权进行当前操作" in html:
update_sql = "update mengxi_title set status = '-1' where id = %s;"%(now[0])
self.cursor.execute(update_sql)
self.db.commit()
return False
total_page = math.ceil(int(re.search(r"回复: (\d+)", html).group(1)) / 50)
# print(total_page)
html = etree.HTML(html)
# 回复部分
reports = html.xpath("//div[contains(@class,'smalltxt outer-section altbg')]")
for once in reports:
author = ''.join(once.xpath("./div[@class='conleft dawn-fl']/ul[@class='maxw']/li[1]/a[1]/text()"))
author_id = ''.join(once.xpath("./div[@class='conleft dawn-fl']/ul[@class='maxw']/li[1]/a[1]/@href")).split("uid=")[-1]
post_content = once.xpath("string(.//div[@class='post-content'])").replace("'",'')
post_imgs = once.xpath(".//div[@class='post-content']//img[contains(@data-src,'bbsatt')]/@data-src")
report_time = ''.join(once.xpath("./div[@class='rtopconnext']/div[@class='connext-left']/span[1]/text()"))
floor = ''.join(once.xpath("./div[@class='rtopconnext']/div[@class='connext-left']/span[2]/a/font/text()")).replace("楼",'')
# print(author,author_id,report_time,floor)
# 下载图片， 保存的时候，存储外部id+楼层id+图片名.jpg
for img in post_imgs:
post_content += "%s\n"%img
save_path = self.save_dir+"%s_%s_%s"%(now[0],floor,img.split("/")[-1])
img_r =requests.get("http:"+img,headers=headers,stream = True)
open(save_path,'wb').write(img_r.content)
insert_sql = "insert into mengxi_detail(pid,author,author_id,content,floor,report_time) values(%s,'%s','%s','%s',%s,'%s');"%(now[0],author,author_id,post_content,floor,report_time)
try:
self.cursor.execute(insert_sql)
self.db.commit()
except BaseException as e:
# print(e)
self.db.rollback()
if page >= total_page:
break
else:
page+=1
# 更新最后一页页码
update_sql = "update mengxi_title set last_page = %s,status='1' where id = %s;"%(page,now[0])
self.cursor.execute(update_sql)
self.db.commit()
def main(self):
select_sql = "select id,url,last_page from mengxi_title where status in ('0','2');"
self.cursor.execute(select_sql)
titles = self.cursor.fetchall()
for once in titles:
self.details(once)
# break
self.cursor.close()
self.db.close()
# a = MengXi()
# a.login()
while True:
a = MengXi()
print("%s开始一轮循环"%(time.asctime()))
a.index()
# a.details()
a.main()
print("%s结束一轮循环" % (time.asctime()))
time.sleep(600)数据库设计
标题表
create table zhilian.mengxi_title
(
id int auto_increment
primary key,
title varchar(255) not null,
url varchar(255) null,
author varchar(255) null,
author_id varchar(255) null,
comment_num int null,
read_num int null,
last_report varchar(100) null,
last_time datetime null,
create_time date null,
last_page int default '1' null comment '最后抓取的页码',
status enum('-1', '0', '1', '2') default '0' null comment '-1：已被删除0：新增1：未改动过2：有新评论',
constraint mengxi_title_url_uindex
unique (url)
)
comment '梦溪论坛帖子'; 详情表
create table zhilian.mengxi_detail ( id int auto_increment primary key, pid int null, author varchar(255) null, author_id varchar(50) null, content text null, floor int null, report_time datetime null, finish_time datetime default CURRENT_TIMESTAMP null, constraint pid_floor_unique unique (pid, floor) );
接下来就是数据分析阶段
首先打开jupyter notebook 新建一个页面
引入模块和连接数据库
import pandas as pd
import pymysql,re
import matplotlib.pyplot as plot
from pyecharts.charts import Bar, Line,Pie
from pyecharts import options as opts
db = pymysql.connect("",'','','')
cursor = db.cursor()读取数据
data = pd.read_sql("select * from mengxi_detail;",con=db)
data
title = pd.read_sql("select * from mengxi_title;",con=db)
title从帖子回复中查找到设备和去除一些回复内容
def get_device(x):
try:
device = re.search(r"---发自(.*?)---",x['content']).group(1)
except AttributeError:
device = 'PC'
x['device'] = device
x['content'] = re.sub(r"(QUOTE:.*?发表)",'',x['content'])
x['content'] = re.sub(r"(---发自.*?---)",'',x['content']).replace("\n",'').replace('\r','')
return x
data = data.apply(get_device,axis=1)开始分析阶段
获取今日热度最高的帖子
hot_title = data.loc[(data['report_time']>'2021-01-25 00:00') & (data['report_time']<'2021-01-25 23:59:59'),'pid'].value_counts().reset_index()
today_hot_top_10 = pd.merge(title,hot_title,left_on = 'id',right_on = 'index')
top20 = today_hot_top_10.sort_values(by='pid',ascending=False).loc[:,['title','pid']].head(20)
top20 = top20.rename(columns={"title":"标题","pid":"回帖数"})
top20['排名'] = range(1,21)
top20
2.今日活跃用户
# 今日最活跃用户
today_data = data.loc[(data['report_time']>'2021-01-25 00:00') & (data['report_time']<'2021-01-25 23:59:59')]
today_user = today_data['author'].value_counts().head(6)
bar = Bar()
bar.add_xaxis(today_user.index.tolist())
bar.add_yaxis("今日回复量排名",today_user.values.tolist())
bar.render_notebook()
3.设备占比
# 各型号数量
device = today_data['device'].value_counts()
pie = Pie()
data1 = [[x,y] for x,y in zip(device.index.tolist(),device.values.tolist())]
pie.add('',data_pair = data1)
pie.set_series_opts(label_opts=opts.LabelOpts(formatter="{b}: {d}%"))
pie.set_global_opts(legend_opts=opts.LegendOpts(is_show=False))
pie.render_notebook()
4.统计各个时段活跃数量
已有 0 位网友参与，快来吐槽：
发表评论