当前位置：首页 > news >正文

Python3按post方式爬取邮箱并按规则放入Excel

news 来源：原创 2024/4/28 13:42:33

记录一下获取邮箱信息

一开始使用Python+selenium的方式模拟点击登录

（之前已经记录过 https://blog.csdn.net/zhangvalue/article/details/102859134），在获取邮件数量存在问题

最终还是使用接口请求的方式。

发现获取接口邮件的时候，最理想的情况是通过两段不同的接口来获取（邮件接口1和最新邮件接口），进行拼接，最后将所有的邮件合并在一起。

①、首先就是找到两部分的接口，header的参数，

②、使用postman模拟登陆 https://blog.csdn.net/zhangvalue/article/details/102971114

③、Python的post请求

④、请求数据处理

⑤、数据按规则导入Excel表格 https://blog.csdn.net/zhangvalue/article/details/102998022

最终的code如下：

# *===================================*
# -*- coding: utf-8 -*-
# * Time : 2019/11/8 15:25
# * Author : zhangsf
# *===================================*
import datetime
import requests
import re
import json
import ssl
import xlwt
#关闭了verify之后，引入urllib3的disable_warnings
import urllib3
urllib3.disable_warnings(urllib3.exceptions.InsecureRequestWarning)
ssl._create_default_https_context = ssl._create_unverified_context
class JDEmail:
    def __init__(self):
        pass
#抽成一个方法将返回归一化之后的list
def outputToList(data1):
    json_str = json.loads(data1.text)
    pattern="(?<='Subject': ').*?(?=',)"
    #正则匹配到所有的主题
    #findall需要传入的格式为str格式，需要str（）
    all_subject=re.findall(pattern, str(json_str))
    print("获取到的主题数："+str(len(all_subject)))
    RULE_1="【用例评审】"
    RULE_2="【用例评审结果】"
    RULE_3="【提测】"
    RULE_4="【测试报告】"
    RULE_5="【上线自查结果】"
    RULE=[RULE_1,RULE_2,RULE_3,RULE_4,RULE_5]
    out_subject=[]
    value=[0,0,0,0,0]
    key=''
    index=0
    for subject in all_subject:
        print(subject)
        #针对一个邮件主题进行五个rule的检测，最终生成key为邮件，
        # value为5个数字（0,0,0,0,0）代表五个规则一个也没有匹配上
        for rule in RULE:
            if rule in subject:
                #获取到当前邮件的项目名
                key=subject.split(rule)[0]
                #第几个规则匹配成功就将第几个的value置为1
                value[index]=1
            #每过一个规则就下标+1
            index = index + 1
        #一封邮件匹配完就将下标重新置为0
        index = 0
        #能获取到项目名的就开始转化
        if key!="":
            #必须使用不可变的，需要转为tuple类型
            value2=tuple(value)
            map1=[key,value2]
            #将每一个项目添加到最终需要添加到Excel表格中
            out_subject.append(map1)
        #重新归为初始值
        value=[0,0,0,0,0]
        key=''
    return out_subject
#将获取到的list数据导入到Excel表格中
def ListToExcel(email_dict):
    print("开始构建Excel表格")
    myxls = xlwt.Workbook()
    sheet1 = myxls.add_sheet(u'sheet1', cell_overwrite_ok=True)
    #先初始化Excel表格的表头的内容
    sheet1.write(0, 0, "编号")
    sheet1.write(0, 1, "项目")
    sheet1.write(0, 2, "需求")
    sheet1.write(0, 3, "用例评审邮件")
    sheet1.write(0, 4, "用例评审结果邮件")
    sheet1.write(0, 5, "提测邮件")
    sheet1.write(0, 6, "测试报告邮件")
    sheet1.write(0, 7, "上线自查邮件")
    sheet1.write(0, 8, "负责人")
    sheet1.write(0, 9, "流程规范打分")
    sheet1.write(0, 10, "总分")
    #将所有的搜集好的邮件信息填入到Excel表格中
    count = 0
    # 遍历email_dict
    for key in email_dict:
        # print(count)
        # print(key + ':' + str(email_dict[key]))
        subject = key
        subject_value = list(email_dict[key])
        sheet1.write(count + 1, 0, count + 1)
        sheet1.write(count + 1, 1, subject)
        sheet1.write(count + 1, 3, subject_value[0])
        sheet1.write(count + 1, 4, subject_value[1])
        sheet1.write(count + 1, 5, subject_value[2])
        sheet1.write(count + 1, 6, subject_value[3])
        sheet1.write(count + 1, 7, subject_value[4])
        count = count + 1
    file_name=datetime.date.today()
    print(file_name)
    myxls.save('E:\\python_file\\web_spider\\'+str(file_name)+'.xls')
    print("结果统计完成！！！")
#将两部分的list数据汇总key相同的部分合并一下，保证最终导入到Excel表中的key唯一
def MergeData(data):
    empty_dict = dict()
    for d in data:
        a = list(d[1])
        #判断字典中是否存在key存在就更新value，否则添加k-v
        if(empty_dict.__contains__(d[0])):
            empty_dict[d[0]][0] = empty_dict[d[0]][0] + a[0]
            empty_dict[d[0]][1] = empty_dict[d[0]][1] + a[1]
            empty_dict[d[0]][2] = empty_dict[d[0]][2] + a[2]
            empty_dict[d[0]][3] = empty_dict[d[0]][3] + a[3]
            empty_dict[d[0]][4] = empty_dict[d[0]][4] + a[4]
        else:
            empty_dict[d[0]]=a
    print("遍历字典")
    for key in empty_dict:
        print(key + ':' + str(empty_dict[key]))
    return  empty_dict
if __name__ =='__main__':
    #第一部分数据
    url = "https://xxxx"
    header = {
        'Cookie': ''
    }
    response = requests.post(url=url, headers=header, verify=False)
    data1=outputToList(response)

    # 第二部分数据
    url2 = "https://xxxxc"
    # 更改获取邮件的数量
    # 查找 %7D%2C%22ViewFilter 部分前面的数据，一般默认数据为25
    # 更改前面的数据为200即可，超过了200也只能获取200
    header2 = {
        'Action': 'FindItem',
        'Cookie': '',
        'X-OWA-CANARY': '',
        'X-OWA-UrlPostData': '%7B%22__type%22%3A%22FindItemJsonRequest%3A%23Exchange%22%2C%22Header%22%3A%7B%22__type%22%3A%22JsonRequestHeaders%3A%23Exchange%22%2C%22RequestServerVersion%22%3A%22Exchange2016%22%2C%22TimeZoneContext%22%3A%7B%22__type%22%3A%22TimeZoneContext%3A%23Exchange%22%2C%22TimeZoneDefinition%22%3A%7B%22__type%22%3A%22TimeZoneDefinitionType%3A%23Exchange%22%2C%22Id%22%3A%22China%20Standard%20Time%22%7D%7D%7D%2C%22Body%22%3A%7B%22__type%22%3A%22FindItemRequest%3A%23Exchange%22%2C%22ItemShape%22%3A%7B%22__type%22%3A%22ItemResponseShape%3A%23Exchange%22%2C%22BaseShape%22%3A%22IdOnly%22%7D%2C%22ParentFolderIds%22%3A%5B%7B%22__type%22%3A%22DistinguishedFolderId%3A%23Exchange%22%2C%22Id%22%3A%22inbox%22%7D%5D%2C%22Traversal%22%3A%22Shallow%22%2C%22Paging%22%3A%7B%22__type%22%3A%22SeekToConditionPageView%3A%23Exchange%22%2C%22BasePoint%22%3A%22Beginning%22%2C%22Condition%22%3A%7B%22__type%22%3A%22RestrictionType%3A%23Exchange%22%2C%22Item%22%3A%7B%22__type%22%3A%22IsEqualTo%3A%23Exchange%22%2C%22Item%22%3A%7B%22__type%22%3A%22PropertyUri%3A%23Exchange%22%2C%22FieldURI%22%3A%22InstanceKey%22%7D%2C%22FieldURIOrConstant%22%3A%7B%22__type%22%3A%22FieldURIOrConstantType%3A%23Exchange%22%2C%22Item%22%3A%7B%22__type%22%3A%22Constant%3A%23Exchange%22%2C%22Value%22%3A%22AQAAAAAAAQwBAAAAB7m%2FNwAAAAA%3D%22%7D%7D%7D%7D%2C%22MaxEntriesReturned%22%3A200%7D%2C%22ViewFilter%22%3A%22All%22%2C%22IsWarmUpSearch%22%3Afalse%2C%22FocusedViewFilter%22%3A-1%2C%22Grouping%22%3Anull%2C%22ShapeName%22%3A%22MailListItem%22%2C%22SortOrder%22%3A%5B%7B%22__type%22%3A%22SortResults%3A%23Exchange%22%2C%22Order%22%3A%22Descending%22%2C%22Path%22%3A%7B%22__type%22%3A%22PropertyUri%3A%23Exchange%22%2C%22FieldURI%22%3A%22ReceivedOrRenewTime%22%7D%7D%2C%7B%22__type%22%3A%22SortResults%3A%23Exchange%22%2C%22Order%22%3A%22Descending%22%2C%22Path%22%3A%7B%22__type%22%3A%22PropertyUri%3A%23Exchange%22%2C%22FieldURI%22%3A%22DateTimeReceived%22%7D%7D%5D%7D%7D'}
    response2 = requests.post(url=url2, headers=header2, verify=False)
    data2 = outputToList(response2)
    #将两次data的数据合并，都是list类型的数据
    data=data1+data2
    email_dict=MergeData(data)
    ListToExcel(email_dict)

最终效果：