组长要求把gerrit上所有的提交和comment数据都收集起来写到excel里面。现学python把这事给自动化了。之前连python语法都只知道一点点，大部分是现学，连中午饭都没时间吃。写完只有一个字：爽！于是晚上沙县加了个鸡腿。
大致思路
先说下，这个python脚本是根据BrainZou在github上的代码改写的https://github.com/BrainZou/PythonSpider/tree/master/Gerrit，非常感谢这位仁兄，不然我今天肯定下不了班了- -
用chrome打开gerrit，F12打开开发者工具点击Network->XHR可以看到页面的HTTP请求，没有就刷新一下，如下图：
查找页面请求
然后用python的第三方库requests来进行请求，得到结果后进行处理，处理完成后写入csv文件，csv可以用任何类似excel的软件打开。
运行环境：安装python3，再安装pip3，然后运行pip3 install requests来安装第三方库requests，最后使用python3 gerrit.py运行
通过requests可以爬取所有的前后端分离的网站，不分离的网站只能爬网页源码了。
完整代码
代码比较简单，都加了注释所以就直接上代码了，注意加上自己公司gerrit网站的cookie。
#coding:utf-8
import requests
import json
import re
import csv
#gerrit的默认url是下面这个url，然后下一页按钮的url是这个url加上这一页最后一项的_sortkey(形如0049b98c0000f3b8)
UNMERGE_URL = 'https://gerrit-sin.xxx.biz/changes/?n=25&O=1'
MERGED_URL = 'https://gerrit-sin.xxx.biz/changes/?q=status:merged&n=25&O=1'
#用来判断是哪个项目组添加的comment
OUR_IDS = [9,10,11,12,13,16,22]
CUSTOMERS_IDS = [2,5]
# 请求访问接口，获取到json格式数据，稍加处理，写入表格。
def requesst(url,limit_time,start,merged):
continue_flag = 1
headers = {
'User-Agent': 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_14_1) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/71.0.3578.98 Safari/537.36',
'Cookie': 'GerritAccount=aHyuzmj3aQisN6CyELrhcU1JnxOEcJK; XSRF_TOKEN=aHyuS4.LW.5ZOwZizn7DMLUV7IfZ-Pi'
}
request_url = url + "&S=" + str(start)
data = requests.get(request_url, headers=headers, verify=True).text
# content = data.text #你要的数据,JSON格式的
#去掉多余的几个垃圾字符
remove = re.compile('\)\]\}\'')
# data = data.replace(')]}\'',"")
data = re.sub(remove, "", data)
data_json = json.loads(data)
if(len(data_json) <=0):
continue_flag = 0
#打开csv文件，准备写入
with open("gerrit_commits.csv", "a",newline='') as csvfile:
writer = csv.writer(csvfile)
#遍历所有commit数据
for one in data_json:
one["created"] = time_format(one["created"])
if (time_cmp(one["created"], limit_time) < 0):
print("已经发现有超过时间的数据")
continue_flag = 0
break
else:
project = one["project"];
owner = get_owner(one["owner"]["_account_id"])
#获取未merge代码的预估review时间
if(merged != True):
advice_url = "https://gerrit-sin.xxx.biz/changes/"+str(one["_number"])+"/revisions/1/reviewassistant~advice"
advice_data = requests.get(advice_url, headers=headers, verify=True).text
advice_data = re.sub(remove, "", advice_data)
estimated = advice_data[advice_data.index("\\u003cstrong\\u003e")+len("\\u003cstrong\\u003e"):advice_data.index("\\u003c/strong\\u003e reviewing")]
#获取commit的comment信息
comment_url = "https://gerrit-sin.xxx.biz/changes/"+str(one["_number"])+"/comments"
comment_data = requests.get(comment_url, headers=headers, verify=True).text
comment_data = re.sub(remove, "", comment_data)
comment_dict = json.loads(comment_data)
ts_size, customer_size = get_comment_size(comment_dict)
print("comment size = " + str(ts_size) + ":" + str(customer_size))
#将数据写入表格中
writer.writerow([
owner,
project[project.rfind("/"):],
one["subject"],
"https://gerrit-sin.xxx.biz/"+str(one["_number"]),
one["insertions"],
one["deletions"],
"NA" if merged==True else estimated,
customer_size + ts_size,
customer_size,
ts_size,
merged
])
#打印日志
print(
owner,
project[project.rfind("/"):],
one["subject"],
"https://gerrit-sin.xxx.biz/"+str(one["_number"]),
one["insertions"],
one["deletions"],
"NA" if merged==True else estimated,
customer_size + ts_size,
customer_size,
ts_size,
merged
)
return continue_flag, start+len(data_json)
#通过id获取owner姓名
def get_owner(account_id):
return {
1: 'memer1',
5: 'member5',
9: 'member9',
10: 'member10',
11: 'member11',
12: 'member12',
13: 'member13',
16: 'member16',
22: 'member22'
}.get(account_id, 'error')
#解析comment json获取comment个数
def get_comment_size(comment_dict):
ts_size = 0
customer_size = 0
for k,v in comment_dict.items():
for comment in v:
_account_id = comment["author"]["_account_id"]
if(_account_id in TS_IDS):
ts_size += 1
if(_account_id in CUSTOMER_IDS):
customer_size += 1
return ts_size, customer_size
#对时间"2017-12-11 02:07:27.000000000"格式化为20171211020727
def time_format(time):
time = time.replace('.000000000', "")
time = time.replace(' ', "")
time = time.replace(':', "")
time = time.replace('-', "")
return time
#两个字符串转int 相减计算大小
def time_cmp(first_time, second_time):
print(first_time)
print(second_time)
return int(first_time) - int(second_time)
def main():
limit_time = input("请输入截止时间 格式：20171211000000\n")
url = UNMERGE_URL
continue_flag = 1
start = 0
#写标题
with open("gerrit_commits.csv", "w",newline='') as csvfile:
writer = csv.writer(csvfile)
writer.writerow(["Owner", "Module", "Title", "Gerrit link", "Insertions", "Deletions",
"Gerrit estimated review time", "Total comments", "Total Customer comments",
"Total TS comments", "Merged"])
# 循环爬下一页
while (continue_flag):
continue_flag, start = requesst(url,limit_time,start,False)
url = MERGED_URL
continue_flag = 1
start = 0
while (continue_flag):
continue_flag, start = requesst(url,limit_time,start,True)
if __name__ == '__main__':
main()
网友评论