#!/usr/bin/env python3 # -*- coding: utf-8 -*- """ Created on Thu Jun 4 15:06:05 2020 @author: chencong updated by ck on 20201127 """ import sys import requests import hashlib import time import json import pymysql import random import token_list as tl import smtplib from email.mime.text import MIMEText from datetime import datetime,timedelta,date from concurrent.futures import ThreadPoolExecutor from datetime import datetime from util import date_util def md5value(s): md5 = hashlib.md5() md5.update(s.encode("utf-8")) return md5.hexdigest() def daily_reports_get(access_token, account_id, level, fields): interface = 'daily_reports/get' url = 'https://api.e.qq.com/v1.1/' + interface common_parameters = { 'access_token': access_token, 'timestamp': int(time.time()), 'nonce': str(time.time()) + str(random.randint(0, 999999)), } parameters = { "account_id": account_id, "level": level, "date_range": { "start_date": my_time, "end_date": my_time }, "page": 1, "page_size": 1000, "fields": fields } parameters.update(common_parameters) for k in parameters: if type(parameters[k]) is not str: parameters[k] = json.dumps(parameters[k]) r = requests.get(url, params=parameters) return r.json() def get_q_data(x,li): a = daily_reports_get(x[2], x[0], "REPORT_LEVEL_ADVERTISER", ( 'date', 'view_count', 'valid_click_count', 'ctr', 'cpc', 'cost', 'web_order_count', 'web_order_rate', 'web_order_cost', 'follow_count', 'order_amount', 'order_roi', 'platform_page_view_count', 'web_commodity_page_view_count', 'from_follow_uv')) for b in a['data']['list']: b['account_id'] = x[0] y = tuple(b.values()) li.append(y) def get_v_data(y,li): c = daily_reports_get(y[2], y[0], "REPORT_LEVEL_ADVERTISER_WECHAT", ( 'date', 'cost', 'view_count', 'valid_click_count', 'ctr', 'official_account_follow_rate', 'order_amount', 'order_roi', 'order_count', 'order_rate', 'order_unit_price', 'web_order_cost', 'first_day_order_amount', 'first_day_order_count')) if 'data' in c.keys(): for d in c['data']['list']: d['account_id'] = y[0] x = tuple(d.values()) li.append(x) def mysql_select(sql): db = pymysql.connect('rm-bp1c9cj79872tx3aaro.mysql.rds.aliyuncs.com', 'superc', 'Qc_123456', 'quchen_text') cur=db.cursor() cur.execute(sql) data=cur.fetchall() return data def get_vx_list(): sql="select account_id,wechat_account_id,access_token,refresh_token,name," \ "ifnull(stage,''),ifnull(pitcher,''),ifnull(platform,''),ifnull(book,'') from advertiser_vx" a= mysql_select(sql) return a def get_qq_list(): sql = "select account_id,'',access_token,refresh_token,name," \ "ifnull(stage,''),ifnull(pitcher,''),ifnull(platform,''),ifnull(book,'') from advertiser_qq" a = mysql_select(sql) return a def get_vx_list_new(): sql="select account_id,wechat_account_id,access_token,refresh_token,name," \ "ifnull(stage,''),ifnull(pitcher,''),ifnull(platform,''),ifnull(book,'') from advertiser_vx " \ " where create_time>='{}'".format(date_util.get_n_day(-1)) a= mysql_select(sql) return a def get_qq_list_new(): sql = "select account_id,'',access_token,refresh_token,name," \ "ifnull(stage,''),ifnull(pitcher,''),ifnull(platform,''),ifnull(book,'') from advertiser_qq " \ " where create_time>='{}'".format(date_util.get_n_day(-1)) a = mysql_select(sql) return a def get_daily_vx(): token_list_v =get_vx_list_new() if run_new else get_vx_list() print("获取vx账号：",token_list_v.__len__()) time1 = time.time() executor = ThreadPoolExecutor(max_workers=max_workers) li=[] for y in token_list_v: executor.submit(get_v_data,y,li) executor.shutdown() print('get_daily_vx:', len(li), 'cost:', int(time.time()-time1)) return li def get_daily_qq(): token_list_q = get_qq_list_new() if run_new else get_qq_list() print("获取qq账号：",token_list_q.__len__()) time1 = time.time() li=[] executor = ThreadPoolExecutor(max_workers=max_workers) for x in token_list_q: executor.submit(get_q_data,x,li) executor.shutdown() print('get_qq_order:', len(li), 'cost:', int(time.time()-time1)) return tuple(li) def mysql_insert_daily_vx(data): db = pymysql.connect('rm-bp1c9cj79872tx3aaro.mysql.rds.aliyuncs.com', 'superc', 'Qc_123456', 'quchen_text') cursor = db.cursor() time1 = time.time() sql2 = 'replace into daily_vx (date,cost,view_count,valid_click_count,ctr,official_account_follow_rate,order_amount,order_roi,order_count,order_rate,order_unit_price,web_order_cost,first_day_order_amount,first_day_order_count,account_id) values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);' try: cursor.executemany(sql2, data) db.commit() cost_time = round((time.time() - time1) / 60, 1) print('insert_daily_vx access', len(data), 'cost_minutes:', cost_time) except: db.rollback() print('insert_daily_vx defeat') def mysql_insert_daily_qq(data): db = pymysql.connect('rm-bp1c9cj79872tx3aaro.mysql.rds.aliyuncs.com', 'superc', 'Qc_123456', 'quchen_text') cursor = db.cursor() time1 = time.time() sql2 = 'replace into daily_qq (date,view_count,valid_click_count,ctr,cpc,cost,web_order_count,web_order_rate,web_order_cost,follow_count,order_amount,order_roi,platform_page_view_count,web_commodity_page_view_count,from_follow_uv,account_id) values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);' try: cursor.executemany(sql2, data) db.commit() cost_time = round((time.time() - time1) / 60, 1) print('insert_daily_qq access', len(data), 'cost_minutes:', cost_time) except: db.rollback() print('insert_daily_qq defeat') def run(): print(f"run [{my_time}---------------]") mysql_insert_daily_vx(get_daily_vx()) mysql_insert_daily_qq(get_daily_qq()) if __name__ == '__main__': print("start_at ===================="+str(datetime.today())+"===================") run_new=False max_workers = 5 # 无参数默认跑今天数据 if len(sys.argv)==1: print("请输入日期") exit(0) elif len(sys.argv)==2: my_time=sys.argv[1] run() elif len(sys.argv)==3: for i in date_util.getDateLists(sys.argv[1],sys.argv[2]): my_time=i run() elif len(sys.argv)==4: run_new=True for i in date_util.getDateLists(sys.argv[1], sys.argv[2]): my_time=i run() else: print("输入参数过多") """调度逻辑 1.每小时跑今天的 2.凌晨跑昨天的 3.昨天新增的账号跑前15天的 """