qc-dgp/dgp/get_cost_history.py

## Subject of obligation

#!/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
import token_list  as tl



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-11-12',         ## 补数据，需求的时间段
            "end_date": '2020-11-16'
        },
        "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()
    sql1 = '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);'
    #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(sql1,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_qq_cost(token_list_q):
    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'))
        try : 
            for b in a['data']['list']:
                b['account_id']=x[0]
                z = tuple(b.values())
                t= t+((z),)
        except Exception as e:
            print(a)
            continue
                
    mysql_insert_daily_qq(t)
    print(len(t),x[0])
        

def get_wx_cost(token_list_v):
    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'])
        try : 
            for d in c['data']['list']:
                d['account_id']=y[0]
                x = tuple(d.values())
                t= t+((x),)
        except Exception as e:
            print(c)
            continue
            
    mysql_insert_daily_vx(t)
    print(len(t),y[0]) 
    ## print(t) 
        
        
def get_cost():
    '''
    token_list_q = [
    ['18353744','','2d1c965ff2a7608aec1ad7baa6455ce0','b09e877adba47f9827e3d087ecb3d18d','乾城文海','趣程15期','董平','文鼎','仕途天骄'],
    ['18353743','','7c27bff5464266c81929e024bb120b36','1366f89a0e55de466ed625841037c431','乾城文海','趣程15期','董平','文鼎','仕途天骄']
    ]
    token_list_v =[
    ['14490987','wxd2e3b7b0c2f0ad9c','bad09cccc78b32d60ed68973bd4e62ad','71b38d5b9cf43cf0af4cd19a33331c3a','风吟书楼','清勇10月','蒋瑜','掌中云','风无痕深爱无言']
    ]  
    '''

    token_list_v = tl.token_list_vx
    token_list_q = tl.token_list_qq

    get_wx_cost(token_list_v)
    get_qq_cost(token_list_q)
    

    
    
if __name__ == '__main__':
    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()

"""