# Author:Clown # -*- codeing = 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 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'] try: sleep(random.uniform(1, 2)) url = "https://waimaieapp.meituan.com/ad/v3/statistics/cpc/today/info?acctId=%s"%str(acctId)+"&wmPoiId=%s" %str(shop_id)+"&token=%s&platform=0"%str(token) resp =requests.get(url) data = resp.json() data_json = data['data']['yesterday']#昨日数据 for list in data_json: clickCount = list['clickCount']#点击次数 cost = list['cost']#时段竞价花费 avgPrice = list['avgPrice']#单次点击花费 showCount = list['showCount']#曝光次数 clickRate = list['clickRate']#点击率% time = list['time']#时段 list_data = [shop_id,time,clickCount,cost,avgPrice,showCount,clickRate,shop_name,beginTime] writer.writerow(list_data) # 写入样本数据 print(script_name,shop_id,shop_name,'input') 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') 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)#密钥 print(token) for i in id_data: row += 1 cell = table.cell_value(row, 4) # 第row行第5列 try: n = int(i) sleep(random.uniform(1, 2)) url = "https://waimaieapp.meituan.com/ad/v3/statistics/cpc/today/info?acctId=93908152&wmPoiId=%s" %str(n)+"&token=%s&platform=0"%str(token) resp =requests.get(url) data = resp.json() data_json = data['data']['yesterday']#昨日数据 for list in data_json: clickCount = list['clickCount']#点击次数 cost = list['cost']#时段竞价花费 avgPrice = list['avgPrice']#单次点击花费 showCount = list['showCount']#曝光次数 clickRate = list['clickRate']#点击率% time = list['time']#时段 list_data = [[n,time,clickCount,cost,avgPrice,showCount,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, "第%s行"%str(row+1),'inputing') except: list_data = [[n, "error", "error", "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,"第%s行"%str(row+1),'error') '''