qc-dgp/dgp/tests/get_data_hourly.py

#!/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 token_list as tl
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', 'Cc719199895', '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', 'Cc719199895', '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', 'Cc719199895', '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 start_cost_job():
	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
	my_time = datetime.today().strftime('%Y-%m-%d')
	# my_time = '2020-11-24'
	if sys.argv.__len__() == 2:

		if int(sys.argv[1])==2:
			my_time=(datetime.today()-timedelta(days=1)).strftime('%Y-%m-%d')
			start_cost_job()
		if int(sys.argv[1])==3:
			run_new = True
			i = 1
			while True:
				my_time=(datetime.today()-timedelta(days=i)).strftime('%Y-%m-%d')
				print("run["+my_time+"]data")
				start_cost_job()
				if i == 15:
					break
				i+=1
	else:
		start_cost_job()

	"""调度逻辑
	1.每小时跑今天的
	2.凌晨跑昨天的
	3.昨天新增的账号跑前15天的
	"""