# -*- 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)