python_files/cppc_python脚本/每日数据爬取v1/饿了么(同行门店榜单).py

# -*- codeing = utf-8 -*-
# @Time : 2023/3/7 11:03
# @Author : Clown
# @File : 饿了么(同行门店榜单).py
# @Software : PyCharm
from time import sleep
import requests
import random
import os
import sys
from datetime import datetime,timedelta
import json
import pymysql
import pandas as pd

def linkTomySql(host, passwd, db_name, port):
    '''连接至数据库返回【db】,v2新增local_infile=1 打开文件导入权限'''
    try:
        # 本地连接为：localhost  服务器连接为：124.222.188.59
        db = pymysql.connect (
            host=host, user="root",
            passwd=passwd,
            db=db_name,
            charset='utf8mb4',
            local_infile=1,
            port=port)
        # print ('\nconnect to mysql server 成功')
        # print ('---------------------------------------')

    except:
        print ("\ncould not connect to mysql server")
        db = "连接失败"
    return db

def read_key_value_pair(db, brand_name, wm_plate, owner):
    '''按条件读取，数据库中all_key_table表里的key_value_pair字段中的值，以键值对的形式输出
       db:数据库，
       brand_name：品牌名，
       wm_plate:外卖平台MEITUAN或ELEME，
       owner:账号权限all或one
    '''
    cursor = db.cursor ()
    sql = f'SELECT key_value_pair FROM all_key_table WHERE brand_name = "{brand_name}" AND wm_plate = "{wm_plate}" AND owner = "{owner}";'
    cursor.execute (sql)
    pair = json.loads (cursor.fetchall ()[0][0])
    return pair


def get_shops_info_to_list(db, brand_name, wm_plate, key_name):
    '''获取门店信息表【shops_info_to_list】中的信息，
    并返回表单shops_info_df【shop_id，shop_name，update_datetime，info_for_script】
    db：数据库信息
    brand_name：品牌
    wm_plate：外卖平台
    key_name：关键信息字段名，如无填‘’，如有填对应键值对的key
    '''
    cursor = db.cursor ()
    if key_name == '':
        sql = f'SELECT shop_id,shop_name,update_datetime FROM shops_info_for_script WHERE brand_name = "{brand_name}" AND wm_plate = "{wm_plate}";'
        cursor.execute (sql)
        shops_info = cursor.fetchall ()
        shops_info_df = []
        for shop_info in shops_info:
            shop_info_dict = {'shop_id': shop_info[0],
                              'shop_name': shop_info[1]}
            shops_info_df.append (shop_info_dict)
        return shops_info_df
    else:
        sql = f'SELECT shop_id,shop_name,update_datetime,info_for_script -> "$.{key_name}" FROM shops_info_for_script WHERE brand_name = "{brand_name}" AND wm_plate = "{wm_plate}";'
        cursor.execute (sql)
        shops_info = cursor.fetchall ()
        shops_info_df = []
        for shop_info in shops_info:
            shop_info_dict = {'shop_id': shop_info[0],
                              'shop_name': shop_info[1],
                              'update_datetime': shop_info[2],
                              f'{key_name}': shop_info[3]}
            shops_info_df.append (shop_info_dict)
        return shops_info_df

def rankDecs(dict_in):
    return dict_in['同行门店月售top10']


if __name__ == '__main__':

    host = '124.222.188.59'
    passwd = '111...Clown'
    db_name = 'zuzu_data'
    port = 63306
    db = linkTomySql(host, passwd, db_name, port)
    brand_name = '浆小白'
    owner = 'all'
    keys_dict = {'elm':'',
                 'elm_shops':'',
                 'mt':'',
                 'mt_shops':''}
    keys_dict['elm'] = read_key_value_pair(db, brand_name, 'ELEME', owner)
    keys_dict['elm_shops'] = get_shops_info_to_list(db,brand_name,'ELEME','')
    keys_dict['mt'] = read_key_value_pair (db, brand_name, 'MEITUAN', owner)
    keys_dict['mt_shops'] = get_shops_info_to_list (db, brand_name, 'MEITUAN', '')

    try:
        ksid = keys_dict['elm'] ['data']['session']['ksid']
        elm_shops = keys_dict['elm_shops']
        # elm_shops = [{'shop_id':173858169,'shop_name':''},{'shop_id':507932892,'shop_name':''}]

        script_name = os.path.basename(sys.argv[0])

        beginTime = (datetime.today () + timedelta (days=-1)).strftime ('%Y-%m-%d')  # 2022-06-23
        endTime = (datetime.today () + timedelta (days=-1)).strftime ('%Y-%m-%d')  # 2022-06-23
        beginTime_int = int ((datetime.today () + timedelta (days=-1)).strftime ('%Y%m%d'))  # 20220623
        endTime_int = int ((datetime.today () + timedelta (days=-1)).strftime ('%Y%m%d'))  # 20220623

        downloadPath = f'F:/cppc/cppc_数据表/3每日爬虫数据/饿了么同行门店榜单/{beginTime_int}饿了么同行门店榜单.xlsx'
        df_out = []

        for shop in elm_shops:
            shop_id = shop['shop_id']
            shop_name = shop['shop_name']
            # print(ksid,shop_id)
            # # print(shop_name)
            sleep (random.uniform (0.5, 1.5))
            try:
                url = 'https://app-api.shop.ele.me/stats/invoke/?method=OrderStatsService.getIndustryShop'
                cookie = 'cna=sk9kGMYYSlkCAd9o1MY603L4; ubt_ssid=z4djmg7vis0sclouzk4dag3r05s2jj30_2021-03-04; perf_ssid=or3iygctea950e0ujr03rhb1lxtke9ms_2021-03-07; nrksid=MZZMNDMTA1MjU4MzA1NTA3NDAxT1Y1aXdxVzhQ; xlly_s=1; shopId='+str(shop_id)+'; ksid='+ksid+'; AEM_TAG_ID_CATCH_DATA_ES00000044={%22tagId%22:%22ES00000044%22%2C%22data%22:%22%E5%BF%AB%E9%A4%90%E4%BE%BF%E5%BD%93%22%2C%22userId%22:173858169%2C%22time%22:1678159125600}; AEM_TAG_ID_CATCH_DATA_ES00000069={%22tagId%22:%22ES00000069%22%2C%22data%22:%22%E8%80%81%E5%BA%97%22%2C%22userId%22:173858169%2C%22time%22:1678159125601}; AEM_TAG_ID_CATCH_DATA_ES00000035={%22tagId%22:%22ES00000035%22%2C%22data%22:%22%E8%80%81%E5%BA%97-%E4%B8%80%E8%88%AC%E5%93%81%E8%B4%A8%22%2C%22userId%22:173858169%2C%22time%22:1678159125601}; AEM_TAG_ID_CATCH_DATA_ES00000021={%22tagId%22:%22ES00000021%22%2C%22data%22:%22%E5%85%A8%E5%9B%BD%E8%BF%9E%E9%94%81%22%2C%22userId%22:173858169%2C%22time%22:1678159125601}; AEM_TAG_ID_CATCH_DATA_ES00000019={%22tagId%22:%22ES00000019%22%2C%22data%22:%22%E4%B8%8A%E6%B5%B7%22%2C%22userId%22:173858169%2C%22time%22:1678159125601}; tfstk=cptRBeO4vjcoG8ch0g308V_N3JOdaSGdG813vhyNM1b04jEbDsxkjhdxD46C__HA.; l=fBOyel_7jbqeT6mSBO5Ihurza779eIOb8oVzaNbMiIEGa6gCNFDj5OCsaGqeldtjgTCxnetrl5ZFzd3HkeUdaxDDBec6LmhInxv9xESPN; isg=BOHh1uRzIbZCUYn7wQVzgEex8K37jlWAM3q0MUO37-hVqgB8i9ilUBWoDN4sZu24'
                headers = {
                    'user-agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/110.0.0.0 Safari/537.36',
                    'content-type': 'application/json',
                    'Cookie':cookie}
                params = {"id":"89F28423BA7B4882900E677683EA56E7|1678159334618",
                          "metas":{"appName":"melody",
                                   "appVersion":"4.4.0",
                                   "ksid":ksid,
                                   "shopId":shop_id},
                          "service":"OrderStatsService",
                          "method":"getIndustryShop",
                          "params":{"shopId":shop_id},"ncp":"2.0.0"}
                resp = requests.post(url,headers=headers,json=params).json()
                recentOrderNum_list = []
                for shop_rank_info in resp['result']:
                    shop_dict = {'门店id':shop_id,
                                 '门店名称':shop_name,
                                 '商圈同行门店id':shop_rank_info['mShopId'],
                                 '商圈同行门店名称':shop_rank_info['mShopName'],
                                 '同行门店月售top10':shop_rank_info['recentOrderNum'],
                                 '同行门店评分':shop_rank_info['score']}
                    recentOrderNum_list.append(shop_dict)
                recentOrderNum_list.sort(key=rankDecs,reverse=True)
                df_out.extend(recentOrderNum_list[:10])
                code = '成功'
            except Exception as e:
                shop_dict = {'门店id': shop_id,
                             '门店名称': shop_name,
                             '商圈同行门店id': '无数据',
                             '商圈同行门店名称': '无数据',
                             '同行门店月售top10': '无数据',
                             '同行门店评分': '无数据'}
                df_out.append(shop_dict)
                code = e
            print(script_name,shop_id,shop_name,code)
        df_out = pd.DataFrame(df_out)
        df_out.to_excel(downloadPath)


    except Exception as e:
        print(e)