爬虫哈希破解密码

import json
import hashlib
import time

import requests
from collections import OrderedDict
import arrow
import pandas as pd
import pandas.io.formats.excel
from xlsxwriter.utility import xl_rowcol_to_cell

pandas.io.formats.excel.header_style = None

now = arrow.now()
yesterday = now.shift(days=-1).format("YYYY-MM-DD")
#yesterday = '2019-06-30'


headers_zh = {
    'Cookie': '_xxxx6.1557052539.1557127982.1559294121.3; __utxzZGxxx',
    'User-Agent': 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_14_4) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/74.0.3729.169 Safari/537.36',
    'Host': 'xgopxxxxxxxom',
    'Referer': 'http://xxxxp'
}

headers_hb_login = {
    'User-Agent': 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_14_4) AppleWebKit/53xxxx4.0.3729.169 Safari/537.36',
    'Host': 'b.laxxxxxom',
    'Origin': 'https://b.lxxxxxxm',
    'Content-Type': 'application/json;charset=UTF-8',
    'Cookie': 'JSESSIOxxxxxxb0-ad23-15a25b26571d'
    }

data_list = []


def get_data(userId, mediaId, mobile):
    hb_info = OrderedDict()
    hb_info['日期'] = yesterday
    hb_info['xxxx'] = userId
    # 这里
    url = f'http://xxxxxxd={userId}&stTms={yesterday}&endTms={yesterday}&dataType=USER&groupUnit=BY_TOTAL&'

    res = requests.get(url, headers=headers_zh)
    zh_data = res.json()['total']

    for k, v in zh_data.items():
        if k not in ('install', 'submitt', 'pv', 'uv'):
            if k == 'cost' or k == 'clickPrice':
                hb_info[k] = round(v / 100, 2)
            else:
                hb_info[k] = v


    hash = hashlib.sha256()
    if userId in [10461,10460,10457]:
        hash.update('yhxxxx6'.encode('utf-8'))
    else:
        hash.update('qxxxxx'.encode('utf-8'))
    sha256pwd = hash.hexdigest()

    data = {"mobile": mobile, "password": str(sha256pwd)}  # 这里

    gettoken_url = 'https:xxxxxgin'
    res_token = requests.post(gettoken_url, data=json.dumps(data), headers=headers_hb_login)
    token = res_token.json()['data']['token']

    headers_hb_info = {
        'User-Agent': 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_14_4) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/74.0.3729.169 Safari/537.36',
        'Referer': 'httxxxxxxxnkx/',
        'Host': 'b.lattebank.com',
        'Accept': 'application/json, text/plain, */*',
        'Cookie': f'JSESSIxxxx29FD; token={token}; ddfe1axxxxxx4b4678f68d39026',
        'X-TOKEN': token
    }

    hb_data_url = f'hxxxxxxxxging?mediaId={mediaId}&searchFields%5BchannelApp%5D=hb&searchFields%5BstartDate%5D={yesterday}&searchFields%5BendDate%5D={yesterday}&page=1&count=40&zeroSwitch='

    res_info = requests.get(hb_data_url, headers=headers_hb_info)
    res = res_info.json()

    hb_data = res['data']['data']['partnerTotal']
    hb_info['激活'] = hb_data['activatedCount']
    hb_info['注册'] = hb_data['registeredCount']
    hb_info['申完'] = hb_data['appliedCount']
    hb_info['授信'] = hb_data['passedCount']
    try:
        hb_info['点击cvr'] = hb_info['激活'] / hb_info['cost']
    except ZeroDivisionError as e:
        hb_info['点击cvr'] = ''
    try:
        hb_info['注册率'] = hb_info['注册'] / hb_info['激活']
    except ZeroDivisionError as e:
        hb_info['注册率'] = ''
    try:
        hb_info['申完率'] = hb_info['申完'] / hb_info['注册']
    except ZeroDivisionError as e:
        hb_info['申完率'] = ''
    try:
        hb_info['授信率'] = hb_info['授信'] / hb_info['申完']
    except ZeroDivisionError as e:
        hb_info['授信率'] = ''
    try:
        hb_info['千次曝光成本'] = round(hb_info['cost'] / (hb_info['impression'] / 1000), 2)
    except ZeroDivisionError as e:
        hb_info['千次曝光成本率'] = ''
    try:
        hb_info['激活成本'] = round(hb_info['cost'] / hb_info['激活'], 2)
    except ZeroDivisionError as e:
        hb_info['激活成本'] = ''
    try:
        hb_info['注册成本'] = round(hb_info['cost'] / hb_info['注册'], 2)
    except ZeroDivisionError as e:
        hb_info['注册成本'] = ''
    try:
        hb_info['申完成本'] = round(hb_info['cost'] / hb_info['申完'], 2)
    except ZeroDivisionError as e:
        hb_info['申完成本'] = ''
    try:
        hb_info['账面授信成本'] = round(hb_info['cost'] / hb_info['授信'], 2)
    except ZeroDivisionError as e:
        hb_info['账面授信成本'] = ''

    data_list.append(hb_info)


data_map = {
    13301253530: {4820: 657, 9245: 1003, 9246: 1004},
    15611023808: {8629: 995, 9381: 1014, 9382: 1036},
    16602133886: {6676: 750, 6708: 839, 8504: 923, 9274: 1050, 9565: 1051, 9566: 1099},
    17343071317: {9539: 1044},
    18813040382: {10461:1138,10460:1171,10457:1172}
}

for mobile, idmap in data_map.items():
    for userId, mediaId in idmap.items():
        get_data(userId, mediaId, mobile)
        print(len(data_list))

df = pd.DataFrame(data_list)
df.rename(columns={
    'impression': '曝光',
    'click': '点击',
    'cost': '花费',
    'clickRate': '点击率',
    'clickPrice': '平均点击价格'
}, inplace=True)

df['账户'].replace({
    4820: 'xxxx',
    9245: 'xxxx',
    9246: 'xxxx',
    8629: 'xxxxx',
    9381: 'xxxxx',
    9382: 'xxxxx,
    6676: 'xxxxx',
    6708:xxxx,
    8504:xxxx
}, inplace=True)

writer = pd.ExcelWriter(yesterday +'xxxx告' + time.strftime("%Y%m%d%H%M") + '.xlsx', engine='xlsxwriter')
df.to_excel(writer, index=False, sheet_name='report')

number_rows = len(df.index)
workbook = writer.book
workbook.formats[0].set_font_name("微软雅黑")
worksheet = writer.sheets['report']

worksheet.set_zoom(90)

cell_format = workbook.add_format(
    {'font_name': '微软雅黑', 'font_size': 12, 'bold': True, 'bg_color': 'blue', 'font_color': 'white', 'align': 'center',
     'valign': 'vcenter', 'border': 1})
worksheet.set_row(0, None, cell_format)

money_fmt = workbook.add_format({'num_format': '￥#,##0.00', 'font_name': '微软雅黑', 'border': 1})
percent_fmt = workbook.add_format({'num_format': '0.00%', 'font_name': '微软雅黑', 'border': 1})

# Total formatting
total_fmt = workbook.add_format({'align': 'right', 'num_format': '￥#,##0.00',
                                 'bottom': 6, 'font_name': '微软雅黑', 'border': 1, 'bold': True})

# Total percent format
total_percent_fmt = workbook.add_format({'align': 'right', 'num_format': '0.00%',
                                         'bottom': 6, 'font_name': '微软雅黑', 'border': 1, 'bold': True})

# Add total rows
for column in range(2, 5):
    # Determine where we will place the formula
    cell_location = xl_rowcol_to_cell(number_rows + 1, column)
    # Get the range to use for the sum formula
    start_range = xl_rowcol_to_cell(1, column)
    end_range = xl_rowcol_to_cell(number_rows, column)
    # Construct and write the formula
    formula = "=SUM({:s}:{:s})".format(start_range, end_range)
    worksheet.write_formula(cell_location, formula, total_fmt)

# Add total rows
for column in range(7, 11):
    # Determine where we will place the formula
    cell_location = xl_rowcol_to_cell(number_rows + 1, column)
    # Get the range to use for the sum formula
    start_range = xl_rowcol_to_cell(1, column)
    end_range = xl_rowcol_to_cell(number_rows, column)
    # Construct and write the formula
    formula = "=SUM({:s}:{:s})".format(start_range, end_range)
    worksheet.write_formula(cell_location, formula, total_fmt)

# Add a total label
worksheet.write_string(number_rows + 1, 0, "汇总", total_fmt)

percent_formula_same = "=D{0}/C{0}".format(number_rows + 2)
worksheet.write_formula(number_rows + 1, 5, percent_formula_same, total_percent_fmt)

percent_formula_ring = "=E{0}/D{0}".format(number_rows + 2)
worksheet.write_formula(number_rows + 1, 6, percent_formula_ring, money_fmt)

formula_mean7 = "=H{0}/E{0}".format(number_rows + 2)
worksheet.write_formula(number_rows + 1, 11, formula_mean7, total_percent_fmt)

formula_predays1 = "=I{0}/H{0}".format(number_rows + 2)
worksheet.write_formula(number_rows + 1, 12, formula_predays1, total_percent_fmt)

formula_predays2 = "=J{0}/I{0}".format(number_rows + 2)
worksheet.write_formula(number_rows + 1, 13, formula_predays2, total_percent_fmt)

formula_predays3 = "=K{0}/J{0}".format(number_rows + 2)
worksheet.write_formula(number_rows + 1, 14, formula_predays3, total_percent_fmt)

formula_predays4 = "=E{0}/(C{0}/1000)".format(number_rows + 2)
worksheet.write_formula(number_rows + 1, 15, formula_predays4, money_fmt)

formula_predays5 = "=E{0}/H{0}".format(number_rows + 2)
worksheet.write_formula(number_rows + 1, 16, formula_predays5, money_fmt)

formula_predays6 = "=E{0}/I{0}".format(number_rows + 2)
worksheet.write_formula(number_rows + 1, 17, formula_predays6, money_fmt)

formula_predays7 = "=E{0}/J{0}".format(number_rows + 2)
worksheet.write_formula(number_rows + 1, 18, formula_predays7, money_fmt)

formula_predays8 = "=E{0}/K{0}".format(number_rows + 2)
worksheet.write_formula(number_rows + 1, 19, formula_predays8, money_fmt)

all_border_fmt = workbook.add_format({'border': 1, 'font_name': '微软雅黑', 'align': 'right'})

worksheet.set_column('A:A', 12, all_border_fmt)
worksheet.set_column('B:B', 17, all_border_fmt)
worksheet.set_column('C:D', 10, all_border_fmt)
worksheet.set_column('E:E', 12, money_fmt)
worksheet.set_column('F:F', 12, percent_fmt)
worksheet.set_column('G:G', 12, money_fmt)
worksheet.set_column('H:K', 10, all_border_fmt)
worksheet.set_column('L:Q', 12, percent_fmt)
worksheet.set_column('P:T', 12, money_fmt)

worksheet.conditional_format(f'G2:G{number_rows+1}', {'type': 'data_bar', 'bar_solid': True, 'format': money_fmt})
worksheet.conditional_format(f'L2:L{number_rows+1}', {'type': 'data_bar', 'bar_solid': True, 'format': percent_fmt})
worksheet.conditional_format(f'M2:M{number_rows+1}', {'type': 'data_bar', 'bar_solid': True, 'format': percent_fmt})
worksheet.conditional_format(f'N2:N{number_rows+1}', {'type': 'data_bar', 'bar_solid': True, 'format': percent_fmt})
worksheet.conditional_format(f'O2:O{number_rows+1}', {'type': 'data_bar', 'bar_solid': True, 'format': percent_fmt})
worksheet.conditional_format(f'T2:T{number_rows+1}', {'type': 'data_bar', 'bar_solid': True, 'format': money_fmt})

writer.save()