# -*- codeing = utf-8 -*- # @Time : 2022/10/8 10:58 # @Author : Clown # @File : 饿了么(分时双转数据)zzx.py # @Software : PyCharm import requests import csv import os import sys from datetime import datetime,timedelta import json import pymysql import random import time 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 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'] 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=0)).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=0)).strftime ('%Y%m%d')) # 20220623 if 1==1: save_path = 'F:/cppc/cppc_数据表/3每日爬虫数据/饿了么分时双转数据/' title = ["门店id", "时段", "曝光人数", "进店人数", "进店转化率%", "下单人数", "下单次数", "下单转化率%", '分时收入',"店名", "数据日期"] csv_name_last = str(beginTime_int) + '饿了么分时双转数据(浆小白).csv' f_last = open (save_path+csv_name_last, mode='w', newline='', encoding="utf-8-sig") writer_last = csv.writer (f_last) # 这一步是创建一个csv的写入 writer_last.writerow (title) # csv_name_today = str (endTime_int) + '饿了么分时双转数据(即时).csv' # f_today = open (save_path + '即时数据/' + csv_name_today, mode='w', newline='', encoding="utf-8-sig") # writer_today = csv.writer (f_today) # 这一步是创建一个csv的写入 # writer_today.writerow (title) url = 'https://lsycm.alibaba.com/api/naposOverview/queryOverviewRealTimeHourData' for shop in elm_shops: time.sleep (random.uniform (0.5, 1.5)) shop_id = shop['shop_id'] shop_name = shop['shop_name'] params = {"shopId":int(shop_id), "endDate":str(endTime_int), "compareOption":"YESTERDAY"} headers = { 'user-agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/90.0.4430.85 Safari/537.36', 'content-type': 'application/json', 'token': f'{ksid}={shop_id}'} resp = requests.post(url,json=params,headers=headers).json() try: data = resp['data'] for h in range(0,24): last_day_exp_pv = data['expUvDist']['compareValue'][str (h)] #曝光人数 last_day_clk_pv = data['clkUvDist']['compareValue'][str (h)] #进店人数 last_day_clk_exp_rate = data['clkExpRateDist']['compareValue'][str (h)] #进店转化率 last_day_valid_order_user_cnt = data['validOrderUserCntDist']['compareValue'][str (h)] #下单人数 last_day_valid_order_cnt = data['validOrderDist']['compareValue'][str (h)] #下单次数 last_day_ord_clk_rate = data['ordClkRateDist']['compareValue'][str (h)] #下单转化率 incomeDist = data['incomeDist']['compareValue'][str (h)] #收入 last_day_row = [shop_id, h, last_day_exp_pv, last_day_clk_pv, last_day_clk_exp_rate, last_day_valid_order_user_cnt, last_day_valid_order_cnt, last_day_ord_clk_rate, incomeDist, shop_name, beginTime] writer_last.writerow (last_day_row) print (script_name, shop_id, shop_name, 'inputing') except: # print (resp) last_day_row = [shop_id, -1, -1, -1, -1, -1, -1, -1, -1, shop_name, beginTime] writer_last.writerow (last_day_row) print (script_name, shop_id, shop_name, 'error') except: print(script_name,'有误')