#!/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 import token_list as ts from apscheduler.schedulers.blocking import BlockingScheduler """ header1 = ['account_id','date','view_count','valid_click_count','ctr','cpc','cost','web_order_count','web_order_rate','web_order_cost','follow_count','order_amount','cheout_fd','order_roi','cheout_fd_reward'] header2 = ['account_id','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'] def append_csv(path,data,header): header = header with open (path,'a+',newline='',encoding='utf-8') as f : dictWriter = csv.DictWriter(f,fieldnames=header) dictWriter.writerow(data) def write_header(path,header): header = header with open (path,'w',newline='',encoding='utf-8') as f: dictWriter = csv.DictWriter(f, fieldnames=header) dictWriter.writeheader() ad_qq = 'ad_qq_'+str(time.strftime("%Y-%m-%d", time.localtime()))+'.csv' ad_vx = 'ad_vx_'+str(time.strftime("%Y-%m-%d", time.localtime()))+'.csv' write_header(ad_qq,header1) write_header(ad_vx,header2) """ 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": '2020-09-01', "end_date": '2020-09-03' }, "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() sql2 = 'insert ignore 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','Cc719199895','quchen_text') cursor = db.cursor() time1 = time.time() sql2 = 'insert ignore 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 get_cost(): token_list_q = [ ['17654125','','0de3e48bfcf08048d499320afb34b129','a57bff7432889e78b5ac8c80c3a06f5d','流云书楼','趣程15期','唐怡婷','文鼎','丁二狗的天梯人生'], ['17654126','','94aa877d937fe4a334db98fad6cdd39d','d66044d43cabe9aa555b1384b297ce8c','流云书楼','趣程15期','唐怡婷','文鼎','丁二狗的天梯人生'], ['17654127','','5ddf8193d5bc9bb3c9e877c85318cc31','2c55d4636ecf9b2ebb81a7432ffc6581','流云书楼','趣程15期','唐怡婷','文鼎','丁二狗的天梯人生'], ['17654128','','f28fbdf11e03023bf9543b4f3c12144c','5a34f508fc1842e6b32c2e726fdc515f','流云书楼','趣程15期','唐怡婷','文鼎','丁二狗的天梯人生'], ['17654129','','6be64129e531941596f45ea8eb5191a3','3dee118bf703bda68583a818ba300f60','流云书楼','趣程15期','唐怡婷','文鼎','丁二狗的天梯人生'], ['17654130','','677e17643d3a25fee022dced4b1f6531','7930c900a8d10eef8eeafe8661a23567','流云书楼','趣程15期','唐怡婷','文鼎','丁二狗的天梯人生'], ['17654131','','6b8e4e67a4f1719349de8c7660af3ac2','a949d39b59dc2900269c7127d092648f','流云书楼','趣程15期','唐怡婷','文鼎','丁二狗的天梯人生'], ['17654133','','60cce74d20a94e7885a0338288b0e752','613447e6af3cd6feaea6de7a1ba73dcb','流云书楼','趣程15期','唐怡婷','文鼎','丁二狗的天梯人生'], ['17654135','','002a427ab5ce1be10b21e4addee51557','59989d70361ff97c1232c0b1c6e2d975','流云书楼','趣程15期','唐怡婷','文鼎','丁二狗的天梯人生'] ] token_list_v =[ ['17590000','wx1ca3d7b3e6a550d3','f3c280dd415dfa3bd913ee32be2bc234','95ef1c34e17d6aa480fd4dffffde9ada','晨潇文轩','刘勇9月','蒋瑜','掌中云','战龙无双'], ['17589896','wx26fd7f6e4010af01','14ebe41c578f3be8c9989210fe1aa7ac','4eb61b2f4edc024d29af18caa3a8d564','惜寒文轩','清勇','','',''], ['17589964','wx30b90b1671010fb4','4567a63fee668da58d07e5a90aa05c64','068695a073ddf763cabaeaabb4dade48','秋珊文典','刘勇9月','黄海军','文鼎','丁二狗的天梯人生'], ['17590030','wx99d65ca787bc6e80','a10f6a9407fef98e856b7a7f281febc9','cc6cd0790c78129705fe59d7c4bf52c1','凝梦文海','刘勇9月','杜广林','文鼎','丁二狗的天梯人生'], ['17590045','wxb0b557705f6f4c95','d9aa49ef61b572c669c67ac4a7aacd6e','867fe22448311c166693b6274e18dc06','墨年书楼','刘勇9月','杜广林','掌中云','战龙无双'] ] t=() 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')) for b in a['data']['list']: b['account_id']=x[0] z = tuple(b.values()) t= t+((z),) mysql_insert_daily_qq(t) t= () 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')) #print(y[0],c['data']['list']) for d in c['data']['list']: d['account_id']=y[0] x = tuple(d.values()) t= t+((x),) mysql_insert_daily_vx(t) print(len(t),y[0],t) get_cost() """ start_job_time = '2020-07-23 17:12:00' if __name__ == '__main__': scheduler = BlockingScheduler() scheduler.add_job(get_cost, 'interval',minutes=1,start_date=start_job_time) scheduler.start() """