python_files/cppc_python脚本/每日数据爬取v1/饿了么(分时竞价数据)20240116.py

# -*- codeing = utf-8 -*-
# @Time : 2021/4/14 13:30
# @Author : Clown
# @File : 美团曝光(新).py
# @Software : PyCharm

from time import sleep
import requests
import csv
import random
import xlrd
import os
import sys
from datetime import datetime,timedelta
import json
import pymysql

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=-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

        title = ["门店id", "时段", "点击次数", "时段竞价花费", "单次点击花费", "曝光次数", "点击率%", "店名", "数据日期"]
        with open ("F:/cppc/cppc_数据表/3每日爬虫数据/饿了么分时竞价数据/%s饿了么分时竞价数据.csv" % str (beginTime_int), 'a', newline='',
                   encoding="utf-8-sig") as t:  # encoding= "utf-8"
            writer = csv.writer (t)  # 这一步是创建一个csv的写入
            writer.writerow (title)  # 写入标签
            for shop in elm_shops:
                shop_id = shop['shop_id']
                shop_name = shop['shop_name']
                # print(shop_name)
                sleep(random.uniform(0.5, 1.5))
                try:

                  url1 = 'https://httpizza.ele.me/vas.gateway/one_route/call_one_bp?method=findUserTotalReport&service=oneReportService'
                  params ={"service":"oneReportService",
                           "method":"findUserTotalReport",
                           "params":[{"loginId":shop_id,"bizCode":"eleCpc"},{"reportEndDate":endTime,"reportStartDate":beginTime,"showDetail":True}],
                           "ksid":ksid}
                  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'}
                  resp =requests.post(url1,json=params,headers=headers)#post类型的网页数据
                  data = resp.json()
                  data_json = data['result']#分时数据

                  for list in data_json['list']:
                      timeSign = list['time']#时段
                      totalCount = list['click']#点击次数
                      totalCost = list['cost']#时段竞价花费
                      averageCost = list['ppc']#单次点击花费
                      exposureAmount = list['impression']#曝光次数
                      clickRate = list['ctr']#点击率
                      list_data= [shop_id,timeSign,totalCount,totalCost,averageCost,exposureAmount,clickRate,shop_name,beginTime]

                      writer.writerow(list_data)  # 写入样本数据
                  print(script_name,shop_id, shop_name,'inputing')
                except:
                  list_data = [shop_id, "error", "error", "error", "error", "error", "error", shop_name,beginTime]
                  writer.writerow(list_data)  # 写入样本数据
                  print(script_name,shop_id,shop_name,'error')

    except Exception as e:
        print(e)


'''
#需要修改时间注意！注意！

workbook = xlrd.open_workbook(r'F:/cppc/cppc_数据表/门店信息表.xlsx')
sheet_list = ["浆小白"]
for sheet in sheet_list:
    table = workbook.sheet_by_name(sheet)#袁阿姨 浆小白
    id_data = table.col_values(1,10)#第2列第11行
    row = 9#行号
    a = str( table.cell_value(1,1))#开始时间
    b = str( table.cell_value(2,1))#结束时间
    ksid = table.cell_value(0,1)#'MDBMNDMTA1Mjc0Mzc5NjQzNTAxTmRSZWZYaTJQ'
    pin = table.cell_value(3,1)#第4行 第2列
    day = int(table.cell_value(4,1))#第5行 第2列
    for i in id_data:
      row += 1
      cell = table.cell_value(row, 2)  # 第row行第3列
      sleep(random.uniform(0.5, 1.5))
      try:
          n = int(i)
          url1 = 'https://app-api.shop.ele.me/alchemy/invoke/?method=wager.getUVSummary'
          params ={'id':'3CF8E83EF1FE4D299B2E44E83328B50D|1619496050955','metas':{'appName':'bs-river-bid','appVersion':'4.4.0','ksid':ksid,'biz_type':'seiya'},'service':'wager','method':'getUVSummary','params':{'restaurantId':n,'type':'','beginTime':a,'endTime':b},'ncp':'2.0.0'}
          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'}
          resp =requests.post(url1,json=params,headers=headers)#post类型的网页数据
          data = resp.json()
          data_json = data['result']#分时数据
          for list in data_json['details']:
              timeSign = list['timeSign']#时段
              totalCount = list['totalCount']#点击次数
              totalCost = list['totalCost']#时段竞价花费
              averageCost = list['averageCost']#单次点击花费
              exposureAmount = list['exposureAmount']#曝光次数
              clickRate = list['clickRate']#点击率
              list_data= [[n,timeSign,totalCount,totalCost,averageCost,exposureAmount,clickRate,cell,a]]
              title = ["门店id","时段","点击次数","时段竞价花费","单次点击花费","曝光次数","点击率%","店名","数据日期"]
              with open("F:/cppc/cppc_数据表/3每日爬虫数据/饿了么分时竞价数据/%s饿了么分时竞价数据.csv"%str(day), 'a', newline='', encoding="utf-8-sig") as t:  # encoding= "utf-8"
                  writer = csv.writer(t)  # 这一步是创建一个csv的写入
                  writer.writerow(title)  # 写入标签
                  writer.writerows(list_data)  # 写入样本数据
          print(sheet,n, "第%s行"%str(row+1),'inputing')
      except:
          list_data = [[n, "error", "error", "error", "error", "error", "error", cell,a]]
          title = ["门店id", "时段", "点击次数", "时段竞价花费", "单次点击花费", "曝光次数", "点击率%", "店名","数据日期"]
          with open("F:/cppc/cppc_数据表/3每日爬虫数据/饿了么分时竞价数据/%s饿了么分时竞价数据.csv" %str(day), 'a', newline='',
                    encoding="utf-8-sig") as t:  # encoding= "utf-8"
              writer = csv.writer(t)  # 这一步是创建一个csv的写入
              writer.writerow(title)  # 写入标签
              writer.writerows(list_data)  # 写入样本数据
          print(sheet,n,"第%s行"%str(row+1),'error')
'''