Python实现的淘宝直通车数据抓取（3）

商家那里给出的是一个有几千行的excel表，这里使用openpyxl来读取excel。

class read_write:
    def __init__(self,token,cookiestr):
        self.subway=subwayquery(token,cookiestr)
        self.threadpool=threadpool.ThreadPool(32)
        self.centeralignment=openpyxl.styles.Alignment(horizontal='center')
        self.percentage_format=openpyxl.styles.numbers.FORMAT_PERCENTAGE_00
        self.number00_format=openpyxl.styles.numbers.FORMAT_NUMBER_00
        self.number_format=openpyxl.styles.numbers.FORMAT_NUMBER
    def write_sheet(self,sheet,data,row,base):
        cell1=sheet.cell(row=row,column=base+1)
        cell1.value=float(data['impressionRate'])/10000
        cell1.number_format=self.percentage_format
        cell2=sheet.cell(row=row,column=base+2)
        cell2.value=int(data['impression'])
        cell2.number_format=self.number_format
        cell3=sheet.cell(row=row,column=base+3)
        cell3.value=int(data['click'])
        cell3.number_format=self.number_format
        cell4=sheet.cell(row=row,column=base+4)
        cell4.value=float(data['ctr'])/10000
        cell4.number_format=self.percentage_format
        cell5=sheet.cell(row=row,column=base+5)
        cell5.value=float(data['cvr'])/10000
        cell5.number_format=self.percentage_format
        cell6=sheet.cell(row=row,column=base+6)
        cell6.value=float(data['avgPrice'])/100
        cell6.number_format=self.number00_format
        cell7=sheet.cell(row=row,column=base+7)
        cell7.value=int(data['competition'])
        cell7.number_format=self.number_format
    def query_write(self,sheet,row):
        keyword=sheet.cell(row=row,column=1).value
        try:
            parse1=self.subway.query(keyword,'1')
            if(parse1!=False):
                self.write_sheet(sheet,parse1['result'][0],row,4)
                self.write_sheet(sheet,parse1['result'][1],row,11)
        except Exception as e:
#            print("%s,%d,%s,%s" % (keyword,row,e,parse1['result']))
            pass
        try:
            parse2=self.subway.query(keyword,'2')
            if(parse2!=False):
                self.write_sheet(sheet,parse2['result'][0],row,18)
                self.write_sheet(sheet,parse2['result'][1],row,25)
        except Exception as e:
#            print("%s,%d,%s,%s" % (keyword,row,e,parse2))
            pass
    def try_save(self,wb,filename):
        try:
            print('正在保存文件：'+filename)
            wb.save(filename)
        except  Exception as e:
            wb.save(filename+'_copy')
    def start(self):
        files=os.listdir(os.getcwd()+'/work')
        for filename in files:
            print('正在读取文件：'+filename)
            wb=load_workbook('work//'+filename)
            sheets=wb.sheetnames
            for sheetname in sheets:
                print('正在读取表:'+sheetname)
                sheet=wb[sheetname]
                sheet.merge_cells('E1:K1')
                sheet.merge_cells('L1:R1')
                sheet.merge_cells('S1:Y1')
                sheet.merge_cells('Z1:AF1')
                alignment=openpyxl.styles.Alignment(horizontal='center')
                sheet['E1'].value='淘宝站内'
                sheet['L1'].value='淘宝站外'
                sheet['S1'].value='计算机设备'
                sheet['Z1'].value='移动设备'
                sheet['E1'].alignment=sheet['Z1'].alignment=sheet['S1'].alignment=sheet['L1'].alignment=self.centeralignment
                sheet['E2'].value=sheet['L2'].value=sheet['S2'].value=sheet['Z2']='展现占比'
                sheet['F2'].value=sheet['M2'].value=sheet['T2'].value=sheet['AA2']='展现指数'
                sheet['G2'].value=sheet['N2'].value=sheet['U2'].value=sheet['AB2']='点击指数'
                sheet['H2'].value=sheet['O2'].value=sheet['V2'].value=sheet['AC2']='点击率'
                sheet['I2'].value=sheet['P2'].value=sheet['W2'].value=sheet['AD2']='点击转化率'
                sheet['J2'].value=sheet['Q2'].value=sheet['X2'].value=sheet['AE2']='市场均价'
                sheet['K2'].value=sheet['R2'].value=sheet['Y2'].value=sheet['AF2']='竞争度'
                max_row=sheet.max_row
                for i in range(0,(max_row+2997)//3000):
                    start=time.time()
                    arguments=list()
                    for row in range(3+i*3000,min(3002+i*3000,max_row)+1):
                        arguments.append(([sheet,row],()))
                    requests=threadpool.makeRequests(self.query_write,arguments)
                    [self.threadpool.putRequest(req) for req in requests]
                    self.threadpool.wait()
                    end=time.time()
                    print('第%d-%d行执行完毕，用时%.1f秒.'%(3+i*3000,min(3002+i*3000,max_row),(end-start)))
            self.try_save(wb,filename)

原理就是读取excel表中第一列的关键词，然后开启一个线程池，将抓取任务每3000个一次放到线程池中运行，最后保存结果
Python实现的淘宝直通车数据抓取（1）
Python实现的淘宝直通车数据抓取（2）
Python实现的淘宝直通车数据抓取（3）
Python实现的淘宝直通车数据抓取（4）