qc-dgp/dgp/get_ad_hourly.py

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
Created on Mon Jul  6 10:23:11 2020

@author: chencong
"""

import requests
import time
import json 
import random
import pymysql
from datetime import datetime
import token_list as tl
import importlib
from concurrent.futures import ThreadPoolExecutor
importlib.reload(tl)

token_list_q = tl.token_list_qq
token_list_v = tl.token_list_vx

def hourly_reports_get(access_token,account_id,date,level,fields) :
    interface = 'hourly_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": date,
                "end_date": date
            },
            "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 ignore into hourly_vx (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,date) 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_hourly_vx access',len(data),'cost_minutes:',cost_time)
    except:
        db.rollback()
        print('insert_hourly_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 ignore into hourly_qq (view_count,valid_click_count,ctr,cpc,cost,web_order_count,web_order_rate,web_order_cost,follow_count,account_id,date) values (%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_hourly_qq access',len(data),'cost_minutes:',cost_time)
    except:
        db.rollback()
        print('insert_hourly_qq defeat')

def get_qq_data(x,start_time,start_date_unixtime,q_li):
    a = hourly_reports_get(x[2], x[0], start_time, "REPORT_LEVEL_ADVERTISER", ('hour', 'view_count', 'valid_click_count', 'ctr', 'cpc', 'cost', 'web_order_count', 'web_order_rate','web_order_cost', 'follow_count'))
    if 'data' in a.keys():
        for b in a['data']['list']:
            if b['hour'] == int((time.time() - ((time.time() + 8 * 3600) // 86400 * 86400 - 8 * 3600)) // 3600) - 1:
                b['account_id'] = x[0]
                b['date'] = time.strftime("%Y-%m-%d %H", time.localtime(start_date_unixtime + int(b['hour']) * 3600))
                del b['hour']
                b = tuple(b.values())
                q_li.append(b)
                # print(q_li)

def get_vx_data(y,start_time,start_date_unixtime,v_li):
    c = hourly_reports_get(y[2], y[0], start_time, "REPORT_LEVEL_ADVERTISER_WECHAT", ('hour', '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']:
            if d['hour'] == int((time.time() - ((time.time() + 8 * 3600) // 86400 * 86400 - 8 * 3600)) // 3600) - 1:
                d['account_id'] = y[0]
                d['date'] = time.strftime("%Y-%m-%d %H", time.localtime(start_date_unixtime + int(d['hour']) * 3600))
                del d['hour']
                d = tuple(d.values())
                v_li.append(d)


def start_all_job(): 
    start_date_unixtime=int((time.time()+8*3600)//86400*86400-8*3600)
    start_time = time.strftime("%Y-%m-%d",time.localtime(start_date_unixtime))
    print(time.strftime("%Y-%m-%d %H",time.localtime(time.time()//3600*3600)))

    executor = ThreadPoolExecutor(max_workers=30)
    start=time.time()
    q_li=[]
    for x in token_list_q :
        executor.submit(get_qq_data,x,start_time,start_date_unixtime,q_li)
    executor.shutdown()
    mysql_insert_daily_qq(tuple(q_li))
    end=time.time()
    print(end-start)

    executor = ThreadPoolExecutor(max_workers=30)
    v_li=[]
    for y in token_list_v :
        executor.submit(get_vx_data, y, start_time, start_date_unixtime, v_li)
    executor.shutdown()
    mysql_insert_daily_vx(tuple(v_li))
    end2=time.time()
    print(end2-end)


if __name__ == '__main__':
    print(datetime.today())
    start_all_job()