#!/usr/bin/env python3 # -*- coding: utf-8 -*- """ Created on Fri May 15 16:09:12 2020 @author: chencong """ #!/usr/bin/env python3 # -*- coding: utf-8 -*- """ Created on Thu Apr 30 11:18:31 2020 @author: chencong """ import requests import urllib.parse import time import json import random import datetime import csv import pymysql from apscheduler.schedulers.blocking import BlockingScheduler import token_list as tl def get_daily_vx(): token_list_v = tl.token_list_vx t = () time1 = time.time() for y in token_list_v : 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()) t= t+((x),) cost_time =round((time.time()-time1)/60,1) print('get_daily_vx:',len(t),'cost_minutes:',cost_time) return t def get_daily_qq(): token_list_q = tl.token_list_qq t= () time1 = time.time() for x in token_list_q : 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')) if 'data' in a.keys(): for b in a['data']['list']: b['account_id']=x[0] x = tuple(b.values()) t= t+((x),) cost_time =round((time.time()-time1)/60,1) print('get_qq_order:',len(t),'cost_minutes:',cost_time) return t 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": str(datetime.date.today()-datetime.timedelta(days=1)), "end_date": str(datetime.date.today()-datetime.timedelta(days=1)) }, "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 mysql_insert_daily_vx(data): db = pymysql.connect('rm-bp1c9cj79872tx3aaro.mysql.rds.aliyuncs.com','superc','Cc719199895','quchen_text') cursor = db.cursor() time1 = time.time() sql1 = 'delete from daily_vx where date = %s' sql2 = 'insert 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.execute(sql1,str(datetime.date.today()-datetime.timedelta(days=1))) db.commit() print('clear_daily_vx access') except : print('clear_daily_vx defeat') 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','Cc719199895','quchen_text') cursor = db.cursor() time1 = time.time() sql1 = 'delete from daily_qq where date = %s' sql2 = 'insert 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.execute(sql1,str(datetime.date.today()-datetime.timedelta(days=1))) db.commit() print('clear_daily_qq access') except : print('clear_daily_qq defeat') 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 start_job(): mysql_insert_daily_vx(get_daily_vx()) mysql_insert_daily_qq(get_daily_qq()) start_job_time = '2020-09-24 06:00:00' if __name__ == '__main__': scheduler = BlockingScheduler() scheduler.add_job(start_job, 'interval',hours=12,start_date=start_job_time) scheduler.start()