之前弄的程序都是将获得的结果打印到终端或者输出到文件里，这样下一次用的时候，要不就要重新运行之前的程序，要不就要重新读取文件获得，不免有点复杂。这次决定学下数据库，使用也方便。Python 自带了sqlite3，直接 import sqlite3 就能用了。
数据库的使用
import sqlite3
conn = sqlite3.connect('test.db')
cursor = conn.cursor()
cursor.execute('create table user (id varchar(20) primary key, name varchar(20))')
cursor.execute('insert into user (id, name) values (\'1\', \'Michael\')')
# 也可以这样插入，用问号代替相应部分内容
# cursor.execute('insert into user(id, name) values (? , ? ) ' , ('1','Michael'))
# 通过rowcount获得插入的行数: 通用来说就是SQL 的 insert update delete 操作影响了的行数
print(cursor.rowcount)
# 关闭Cursor:
cursor.close()
# 提交事务:
conn.commit()
# 关闭Connection:
conn.close()
分清Connection 和 Cursor，SQL 的具体执行都是后者完成。
cursor.execute('select * from user where id=?', ('1',))
# 获得所有查询结果:
values = cursor.fetchall()
# 获得一个查询结果
value = cursor.fetchone()
# 获取指定数值的结果，默认是一个
value = cursor.fetchmany([size=s])
## 如果先获得所有结果再尝试获取一个结果，后来的尝试得到的数据为空
## 如果先获得一个结果再尝试获取所有结果，后来的尝试得到的数据为除去第一个以外的数据
过程出错也要保证关闭了数据库，可以用 try:...except:...finally:...
数据的展现
这次是打算获取下某部日剧的收视率与播出后一个星期内 up 主上传视频数之间的比较，用的是折线图表现收视率，柱状图表示上传的视频数目。
Python 画图比较麻烦，而且展示效果不是很美观，下次如果有类似的需要还是尝试下用 excel
画图的代码
import numpy as np
import matplotlib.pyplot as plt
plt.title("Upload Number in Bilibili & Audience Rating")
x = range(1, len(release_count) + 1)
ynum = np.array(release_count)
plt.bar(x, ynum, width=0.5, align="center", color="gray")
plt.xticks(x, x)
for a, b in zip(x, ynum):
plt.text(a, 5, "%.0f" % (b), ha="center", va="bottom", fontsize=10)
plt.xlabel("Week")
plt.ylabel("Upload Number")
plt2 = plt.twinx()
yrate = np.array(release_rate)
plt2.plot(x, yrate, color="b", linestyle="-", marker="o")
for a, b in zip(x, yrate):
plt2.text(a, b, "%.2f%%" % b, ha="center", va="bottom", fontsize=10)
plt.show()
最后的效果如下
日剧《逃避虽可耻但有用》收视率与Bilibili 上相关话题视频的比较.png
这里只选取了一个关键词，结果只是能当做参考，可以多加关键词获取更多更准确的数量。
总结
发现自己对于 itertools 和 functools 这两个模块不太熟练，还是要多学习
博客里看代码不是太方便，我 Github 上已经有了，来 这里 看。以后的修改博客就不更了，欢迎关注我的 Github
#!/usr/bin/python3
# -*- coding:utf-8 -*-
import os
import re
import itertools
import sqlite3
from bs4 import BeautifulSoup
import requests
import numpy as np
import matplotlib.pyplot as plt
class Bilibili:
def __init__(self, name, keywords):
self.con = sqlite3.connect(name)
self.db = self.con.cursor()
self.keywords = keywords
def crawl(self):
session = requests.Session()
types = ['totalrank', 'click', 'pubdate', 'dm', 'stow']
self.db.execute('''
create table koi_information
(id int primary key,
link text,
uploader text,
uploadtime text,
title text,
description text,
duration int,
watch int,
dm int)
''')
self.con.commit()
for keyword in self.keywords:
page = 1
typeid = 0
for tp in types:
os.mkdir(keyword+" "+tp)
while typeid < 5:
search = session.get("https://search.bilibili.com/all?keyword=" +
keyword+"&page="+str(page)+"&order="+types[typeid])
if search:
with open(keyword+" "+types[typeid]+"/"+str(page)+".html", "w") as file:
file.write(search.content.decode(encoding="utf-8"))
if page < 50:
page = page + 1
else:
typeid = typeid + 1
page = 1
for tp in types:
allfile = os.listdir(keyword+" "+tp)
for file in allfile:
with open(keyword+" "+tp+"/"+file, "r") as source:
soup = BeautifulSoup(source.read(), "lxml")
matrixs = soup.find_all("li", attrs={"class": "video matrix "})
for matrix in matrixs:
head = matrix.find("a", attrs={"class": "title"})
link, vid = self.__href_format(head['href'])
title = self.__str_format(head['title'])
duration_text = matrix.find("span", attrs={"class": "so-imgTag_rb"}).text
duration = self.__to_second(self.__str_format(duration_text))
description = self.__str_format(matrix.find("div", attrs={"class": "des hide"}).text)
watch_text = matrix.find("span", attrs={"title": "观看"}).text
watch = self.__num_format(self.__str_format(watch_text))
dm_text = matrix.find("span", attrs={"title": "弹幕"}).text
dm = self.__num_format(self.__str_format(dm_text))
uploadtime_text = matrix.find("span", attrs={"title": "上传时间"}).text
uploadtime = self.__str_format(uploadtime_text)
uploader_text = matrix.find("span", attrs={"title": "up主"}).text
uploader = self.__str_format(uploader_text)
try:
print("try saving " + vid)
self.db.execute("insert into koi_information values(?,?,?,?,?,?,?,?,?)",
(vid, link, uploader, uploadtime, title,
description, duration, watch, dm))
except Exception as e:
print("exist or something wrong : " ,e)
self.con.commit()
def show(self):
release_date = [
"2016-10-11", "2016-10-18", "2016-10-25", "2016-11-01", "2016-11-08", "2016-11-15",
"2016-11-22", "2016-11-29", "2016-12-06", "2016-12-13", "2016-12-20"
]
release_rate = [10.2, 12.1, 12.5, 13.0, 13.3, 13.6, 13.6, 16.1, 16.9, 17.1, 20.8]
release_count = []
for val in release_date:
self.db.execute(
"select title,uploadtime,link from koi_information "
"where julianday(uploadtime) - julianday(?) < 7 and julianday(uploadtime) - julianday(?) >= 0",
(val, val))
cnt = len(self.db.fetchall())
release_count.append(cnt)
diff = 7
all_count = []
for val in release_count:
all_count.append(val)
while diff < 365:
self.db.execute(
"select title,uploadtime,link from koi_information "
"where julianday(uploadtime) - julianday(?) < ? and julianday(uploadtime) - julianday(?) >= ?",
(release_date[-1], 7 + diff, release_date[-1], 0 + diff))
cnt = len(self.db.fetchall())
all_count.append(cnt)
diff = diff + 7
self.db.close()
plt.title("Upload Number in Bilibili & Audience Rating")
x = range(1, len(release_count) + 1)
ynum = np.array(release_count)
plt.bar(x, ynum, width=0.5, align="center", color="gray")
plt.xticks(x, x)
for a, b in zip(x, ynum):
plt.text(a, 5, "%.0f" % (b), ha="center", va="bottom", fontsize=10)
plt.xlabel("Week")
plt.ylabel("Upload Number")
plt2 = plt.twinx()
yrate = np.array(release_rate)
plt2.plot(x, yrate, color="b", linestyle="-", marker="o")
for a, b in zip(x, yrate):
plt2.text(a, b, "%.2f%%" % b, ha="center", va="bottom", fontsize=10)
plt.show()
@staticmethod
def __str_format(val):
if not val:
return None
return val.replace("\t", "").replace("\n", "")
@staticmethod
def __href_format(val):
if not val:
return None
pattern = re.compile(".*(www.bilibili.com/video/av([0-9]+)).*")
result = pattern.match(val)
if result:
return result.group(1), result.group(2)
else:
return None
@staticmethod
def __to_second(val):
if not val:
return 0
num = val.split(":")
## 这里弄错了，如果 num 里只有两个数字还是对的，一长就错了，自己还是想复杂了，用functools.reduce(lambda x,y : int(x)*60 + int(y),num)
return int(list(itertools.accumulate(num, lambda a, b: int(a) * 60 + int(b)))[1])
@staticmethod
def __num_format(val):
if not val:
return 0
if "万" in val:
num = val.split("万")
return int(float(num[0]) * 10000)
else:
return int(val)
if __name__ == "__main__":
b = Bilibili("test.db", ["gakki舞"])
b.crawl()
b.show()
来自个人 Python 文集
