#!/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 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天的 """