python_files/cppc_python脚本/每日数据爬取v1/美团曝光(新).py

# Author:Clown
# -*- coding:utf-8 -*-
# @Time :2021/4/14 12:07
# @File: 美团曝光(新).py


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__':

    # start_time = time.time()
    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:

        token = keys_dict['mt']['data']['session']['token']
        acctId = keys_dict['mt']['data']['session']['acctId']
        mt_shops = keys_dict['mt_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

        page = [0,1,2]#页码
        page_dict = {'0': {'tabType': 3, 'firstCategoryId': -1},
                     '1': {'tabType': 1, 'firstCategoryId': 1},
                     '2': {'tabType': 2, 'firstCategoryId': 2}}

        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 mt_shops:

                shop_id = shop['shop_id']
                shop_name = shop['shop_name']
                sleep(random.uniform(1, 2))

                try:
                    # token = '0Vd9Veko2qHQdjYPNrBjCnFp3fZgbPjYaNFGZONVeo78*'
                    # wmPoiId = 9198088
                    # acctId = 93908152
                    # tabType = 3
                    for m in page:
                        tabType = page_dict[str (m)]['tabType']
                        firstCategoryId = page_dict[str (m)]['firstCategoryId']
                        url = f'https://waimaieapp.meituan.com/gw/bizdata/flow/single/origin?token={token}&wmPoiId={shop_id}&acctId={acctId}&appType=3&tabType={tabType}&durationType=1&beginDate=20221115&endDate=20221115&firstCategoryId={firstCategoryId}&ignoreSetRouterProxy=true'
                        resp = requests.get (url).json ()['data']
                        for date in resp['positions']:
                            exposeCnt = date['data']['exposeCnt']
                            visitCnt = date['data']['visitCnt']
                            clickRate = date['data']['clickRate']
                            position = date['position']
                            # print (position, exposeCnt, visitCnt, clickRate)
                    # url1 = "&wmPoiId=%s"% str(shop_id)+"&acctId=%s&appType=3&optimus_uuid=26d8a6f6-f850-4a54-b6df-9b7606096fda&optimus_risk_level=71&optimus_code=10&optimus_partner=19" % str(acctId)
                    # for m in page:
                    #     url2 = "https://waimaieapp.meituan.com/bizdata/flowV4/pc/dis?tabType=%s&durationType=1&beginDate=20210407&endDate=20210407&"%str(m)+"token=%s" %str(token)
                    #     url = url2+url1
                    #     resp =requests.get(url)
                    #     data = resp.json()
                    #     # print(data)
                    #     data_json = data['data']['exposureAndVisitCntList']
                    #     for list in data_json:
                    #         name = list['name']
                    #         exposeCnt = list['exposeCnt']
                    #         visitCnt = list['visitCnt']
                    #         clickRate = list['clickRate']
                            list_data = [m,shop_id,position,exposeCnt,visitCnt,clickRate,shop_name,beginTime]
                            writer.writerow(list_data)  # 写入样本数据
                    print (script_name, shop_id, shop_name, 'input')

                except:
                    list_data = [m,shop_id, "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')
table = workbook.sheet_by_name('美团')
id_data = table.col_values(3,10)#第4列第11行
row = 9#行号
day = int(table.cell_value(0, 1))#数字日期
day_data = table.cell_value(1, 1)#数据日期
token = table.cell_value(2, 1)#密钥
page = [0,1,2]#页码
for i in id_data:
  row += 1
  cell = table.cell_value(row, 4)  # 第row行第5列
  sleep(random.uniform(1, 2))
  try:
      n = int(i)
      url1 = "&wmPoiId=%s&acctId=93908152&appType=3&optimus_uuid=26d8a6f6-f850-4a54-b6df-9b7606096fda&optimus_risk_level=71&optimus_code=10&optimus_partner=19" % str(n)
      for m in page:
          url2 = "https://waimaieapp.meituan.com/bizdata/flowV4/pc/dis?tabType=%s&durationType=1&beginDate=20210407&endDate=20210407&"%str(m)+"token=%s" %str(token)
          url = url2+url1
          resp =requests.get(url)
          data = resp.json()
          data_json = data['data']['exposureAndVisitCntList']
          for list in data_json:
              name = list['name']
              exposeCnt = list['exposeCnt']
              visitCnt = list['visitCnt']
              clickRate = list['clickRate']
              list_data = [[m,n,name,exposeCnt,visitCnt,clickRate,cell,day_data]]
              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(n,m,row+1,'inputing')
  except:
      list_data = [[m,n, "error", "error", "error", "error", cell,day_data]]
      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(n,m,"第%s行"%str(row+1),'error')
'''