【Python】大数据挖掘课程作业2——使用SQLAlchemy将爬虫获得的数据储存进数据库
上一篇博客中,我们详解了从B站爬取相关数据的流程,现在,我们要将数据储存进数据库中。
本文写作于2020-06,B站正处于AV向BV过渡的阶段,日后B站后台的数据库设计可能发生变化导致本文的内容不在适用,请读者注意。
数据表的定义
根据我的课程作业的需要,将定义四个数据表,分别表示UP主、视频、评论、弹幕。
定义表单的代码
from sqlalchemy import create_engine, MetaData
from sqlalchemy import Table, Column, ForeignKey
from sqlalchemy import String, DateTime, Integer, Text
metadata = MetaData()
uploader = Table(
'uploader', metadata,
Column('uid', Integer(), primary_key=True),
Column('name', String(255), nullable=False)
)
video = Table(
'video', metadata,
Column('av', Integer(), primary_key=True),
Column('bv', String(20), nullable=False, index=True, unique=True),
Column('comment_count', Integer(), nullable=False),
Column('play_count', Integer(), nullable=False),
Column('title', String(255), nullable=False),
Column('description', Text(), nullable=False),
Column('uploader_id', Integer(), ForeignKey(column='uploader.uid', ondelete='CASCADE'), nullable=False),
Column('upload_time', DateTime(), nullable=False),
)
comments = Table(
'comments', metadata,
Column('rp_id', Integer(), primary_key=True),
Column('video_id', Integer(), ForeignKey(column='video.av', ondelete='CASCADE'), nullable=False, index=True),
Column('likes', Integer(), nullable=False),
Column('root_comment', Integer(), ForeignKey(column='comments.rp_id', ondelete='CASCADE'), nullable=True),
Column('content', String(255), nullable=False),
Column('comment_time', DateTime(), nullable=False),
)
dm = Table(
'dm', metadata,
Column('id', Integer(), autoincrement=True, primary_key=True),
Column('video_id', Integer(), ForeignKey(column='video.av', ondelete='CASCADE'), nullable=False, index=True),
Column('content', String(255), nullable=False),
Column('property', String(255), nullable=False),
)
if __name__ == '__main__':
engine = create_engine('sqlite:///../bilibili.db', echo=True, encoding='utf-8')
metadata.create_all(engine)
uploader各字段的含义
字段 | 含义 |
---|---|
uid | 用户的数字UID |
name | 用户昵称 |
video各字段的含义
字段 | 含义 |
---|---|
av | 视频的AV号 |
bv | 视频的BV号 |
comment_count | 评论总数 |
play_count | 播放量 |
title | 视频标题 |
description | 视频简介 |
uploader_id | UP主的uid,外键 |
upload_time | 视频上传时间 |
comments各字段的含义
字段 | 含义 |
---|---|
rp_id | 唯一的标识每一条评论的id |
video_id | 对应视频的AV号,外键 |
likes | 评论的点赞数 |
root_comment | 如果此条评论是另一条评论下的回复,则此字段为那一条评论的rp_id,外键 |
content | 评论的具体内容 |
comment_time | 评论时间 |
dm各字段的含义
字段 | 含义 |
---|---|
id | 数据库的自增id |
video_id | 对应视频的AV号,外键 |
content | 弹幕的具体内容 |
property | 原始弹幕数据中表示弹幕各种属性的一个字符串 |
获取数据并插入数据库
关于获取数据的部分,使用上一篇博客中的代码,需要注意的是,在获取数据的过程中,由于一个视频下的评论需要分成很多次获取,而在这个过程中,由于数据本身可能会发生变化(比如在我们爬数据的过程中,有其他正常的用户在进行评论和点赞操作,导致数据发生变化),我们获取的数据中有可能会存在重复的部分,为了防止在数据库中插入重复数据导致的异常,我们在插入数据之前需要先进行一次检查(我这里直接对每个要插入的数据进行一次select确保没有重复,如果读者对数据库相关知识掌握的更加深入,请忽略我简单粗暴的做法)。
注意,在这里由于我的作业选题,我指定了一个确定的UP主列表,并且给出了一个标签列表用于过滤出和COVID19相关的视频。
相关代码如下:
from .CreateTable import uploader, video, comments, dm
from .CreateTable import metadata
from GetBilibiliData.GetBilibiliUploaderInfo import get_video_list_from_uploader_id
from GetBilibiliData.GetBilibiliVideoInfo import get_av_vid_comment_number_and_tags_from_bv
from GetBilibiliData.GetBilibiliVideoInfo import get_comments_and_replies_from_av_and_bv
from GetBilibiliData.GetBilibiliVideoInfo import get_dm_from_vid_and_bv
from sqlalchemy import create_engine
from sqlalchemy import insert, select, update, and_
from sqlalchemy.sql.dml import Insert, Update
from sqlalchemy.sql.selectable import Select
from sqlalchemy.engine.result import ResultProxy, RowProxy
from sqlalchemy.engine.base import Engine, Connection
import datetime
def gather_uploader_info(connection: Connection) -> None:
"""
将我需要的UP主的信息插入数据库中。
:param connection: 一个数据库连接,数据库中必须已经创建好了对应的表(up,video,comments,dm)
:return: None
"""
up = {
10330740: '观察者网',
456664753: '央视新闻',
10303206: '环球时报',
483787858: '环球网',
222103174: '小央视频',
54992199: '观视频工作室',
}
for uid in up:
name = up[uid]
sel = select([uploader]).where(uploader.c.uid == uid) # type: Select
sel_rp = connection.execute(sel) # type: ResultProxy
if sel_rp.first():
continue
ins = insert(uploader).values( # type: Insert
uid=uid,
name=name
)
res = connection.execute(ins) # type: ResultProxy
print('up主信息插入:' + str(res.inserted_primary_key))
def gather_video_info_for_single_uploader(connection: Connection, uid: int, required_tags: list,
start_time: datetime.datetime, end_time: datetime.datetime) -> None:
"""
根据UP主的UID,爬取一定之间段内,这个UP上传的包含指定标签的所有视频的信息,并储存。
:param connection: 一个数据库连接,必须已经创建好了相关数据表
:param uid: UP主的UID
:param required_tags: 最终插入数据库的视频的标签至少有一个出现在required_tags中
:param start_time: 需要的视频的最早上传时间
:param end_time: 需要的视频的最晚上传时间
:return: None
"""
def __filter_video_tags(bv: str, wanted_tags: list) -> bool:
_, _, cnt, tags = get_av_vid_comment_number_and_tags_from_bv(bv=bv)
if cnt == -1:
return False
real_tags = []
for t in tags: # type: dict
real_tags.append(t['tag_name'])
for t1 in real_tags: # type: str
for t2 in wanted_tags: # type: str
if t1.find(t2) != -1 or t2.find(t1) != -1:
print(real_tags)
return True
return False
res = get_video_list_from_uploader_id(uid=f'{uid}', start_time=start_time, end_time=end_time)
for v in res: # type: dict
if __filter_video_tags(bv=v['bvid'], wanted_tags=required_tags):
sel = select([video.c.av]).where(video.c.av == v['aid']) # type: Select
sel_rp = connection.execute(sel) # type: ResultProxy
if sel_rp.first():
upd = update(video).values( # type: Update
comment_count=v['comment'],
play_count=v['play'],
title=v['title'],
description=v['description'],
)
upd = upd.where(video.c.av == v['aid'])
upd_rp = connection.execute(upd) # type: ResultProxy
print(upd_rp.last_updated_params())
else:
ins = insert(video).values( # type: Insert
av=v['aid'],
bv=v['bvid'],
comment_count=v['comment'],
play_count=v['play'],
title=v['title'],
description=v['description'],
uploader_id=uid,
upload_time=datetime.datetime.fromtimestamp(v['created']),
)
ins_res = connection.execute(ins) # type: ResultProxy
print(ins_res.inserted_primary_key)
def gather_video_info_for_all_uploader(connection: Connection, start_time: datetime.datetime,
end_time: datetime.datetime) -> None:
"""
对于数据库中已经存在的所有UP主,爬取他们在一定时间范围内上传的视频的信息,并储存。
:param connection: 数据库连接,相关数据表必须已经创建好
:param start_time: 开始时间
:param end_time: 结束时间
:return: None
"""
up_sel = select([uploader.c.uid, uploader.c.name]) # type: Select
rp = connection.execute(up_sel) # type: ResultProxy
required_tags = ['福奇', '肺炎', '新冠', '疫情', '病毒', '蝙蝠', 'COVID-19', 'COVID19'] # 用这些标签来识别与COVID19相关的视频
for r in rp: # type: RowProxy
print(f'现在获取 {r.name} 的视频列表')
gather_video_info_for_single_uploader(connection=connection, uid=r.uid, required_tags=required_tags,
start_time=start_time, end_time=end_time)
def gather_comment_info_for_single_video(connection: Connection, av: int, bv: str, comment_total: int) -> None:
"""
对于单个视频,爬取它的所有评论并储存。
:param connection: 数据库连接,相关数据表必须已经创建
:param av: 视频的AV号
:param bv: 视频的BV号
:param comment_total: 视频评论总数(作为识别数据是否已经获取完整的依据)
:return: None
"""
def __insert_comment(__rp_id: int, __video_id: int, __likes: int, __root_comment: int, __content: str,
__comment_time: datetime.datetime) -> int:
sel = select([comments]).where(comments.c.rp_id == __rp_id) # type: Select
rp = connection.execute(sel) # type: ResultProxy
if rp.first():
upd = update(comments).values( # type: Update
likes=__likes,
)
upd = upd.where(comments.c.rp_id == __rp_id) # type: Update
connection.execute(upd)
return __rp_id
ins = insert(comments).values( # type: Insert
rp_id=__rp_id,
video_id=__video_id,
likes=__likes,
root_comment=__root_comment,
content=__content,
comment_time=__comment_time,
)
rp = connection.execute(ins) # type: ResultProxy
return rp.inserted_primary_key
cts = get_comments_and_replies_from_av_and_bv(av=str(av), bv=bv, comment_total=comment_total)
for c in cts: # type: dict
ins_id = __insert_comment(
__rp_id=c['rpid'],
__video_id=c['oid'],
__likes=c['like'],
__root_comment=-1,
__content=c['content']['message'],
__comment_time=datetime.datetime.fromtimestamp(c['ctime']),
)
print(ins_id)
if c.get('replies'):
for r in c['replies']: # type: dict
ins_id = __insert_comment(
__rp_id=r['rpid'],
__video_id=r['oid'],
__likes=r['like'],
__root_comment=r['root'],
__content=r['content']['message'],
__comment_time=datetime.datetime.fromtimestamp(r['ctime'])
)
print(ins_id)
def gather_comment_info_for_all_video(connection: Connection) -> None:
"""
对于数据库中已经存在的所有视频信息,爬取他们的评论并储存。
:param connection: 数据库连接,相关数据表必须已经创建完成。
:return: None
"""
video_sel = select([video.c.av, video.c.bv, video.c.comment_count]) # type: Select
video_rp = connection.execute(video_sel) # type: ResultProxy
for v in video_rp: # type: RowProxy
gather_comment_info_for_single_video(connection=connection, av=v.av, bv=v.bv, comment_total=v.comment_count)
def gather_dm_info_for_single_video(connection: Connection, av: int, bv: str) -> None:
"""
爬取某一个视频的弹幕并储存。
:param connection: 数据库连接,相关数据表必须已经创建。
:param av: 视频的AV号
:param bv: 视频的BV号
:return: None
"""
_, vid, _, _ = get_av_vid_comment_number_and_tags_from_bv(bv=bv)
if vid == '':
return
dms = get_dm_from_vid_and_bv(vid=vid, bv=bv)
for d in dms:
text = d[0]
prop = d[1]
sel = select([dm.c.content]).where(and_(dm.c.content == text, dm.c.property == prop)) # type: Select
rp = connection.execute(sel) # type: ResultProxy
if rp.first():
continue
ins = insert(dm).values( # type: Insert
video_id=av,
content=text,
property=prop,
)
rp = connection.execute(ins) # type: ResultProxy
print(rp.inserted_primary_key)
def gather_dm_info_for_all_video(connection: Connection) -> None:
"""
对于数据库中已经存在的所有视频,爬取他们的弹幕数据,并储存。
:param connection:
:return:
"""
video_sel = select([video.c.av, video.c.bv]) # type: Select
video_rp = connection.execute(video_sel) # type: ResultProxy
for r in video_rp: # type: RowProxy
gather_dm_info_for_single_video(connection=connection, av=r.av, bv=r.bv)
if __name__ == '__main__':
pass
杂谈
在做这次作业的过程中,为了方便和同组的人分享数据(疫情期间不能返校),我使用了sqlite3这个数据库,因为它是直接基于文件的,但在使用中我发现这个数据库如果进行密集的读写的话,对硬盘施加的负载很大,如果将数据库文件放在机械硬盘上,很可能机械硬盘的性能会成为整个程序运行性能的瓶颈。
我在实际爬取数据的过程中,考虑到可能会发生的网络异常或是程序运行异常,在程序的一次运行中我只让它爬取五天的数据并形成一个单独的db文件(虽然最后爬完了半年的数据也没有发生什么意外),这就带来了合并数据库的需要。这里我选择了使用SQLAlchemy进行数据库合并(而不是在sqlite的命令行中合并),在使用SQLAlchemy合并数据库的过程中,我了解到sqlite3支持内存数据库,于是决定使用内存数据库储存中间结果,等到所有数据在内存中合并完成后,在一并写入硬盘,带来了一定的效率提升。