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_daily_vx():
	token_list_v =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()
	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 send_waring_email():
	msg_Sender = '2091961210@qq.cm'  # 发送方邮箱
	msg_code = 'ylltzynbshmufdeh'  # 发送方邮箱的授权码
	msg_Receiver = '2091961210@qq.cm'  # 收件人邮箱

	subject = "数据库未按时更新"  # 主题
	content = "数据库未按时更新"  # 正文
	msg = MIMEText(content, _charset="utf-8")
	msg['Subject'] = subject
	msg['From'] = msg_Sender
	msg['To'] = msg_Receiver
	try:
		s = smtplib.SMTP_SSL("smtp.qq.com", 465)  # 邮件服务器及端口号
		s.login(msg_Sender, msg_code)
		s.sendmail(msg_Sender, msg_Receiver, msg.as_string())
		print("发送成功")
	except Exception as e:
		print("发送失败", e)
	finally:
		s.quit()


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())+"===================")
	max_workers = 5
	my_time = datetime.today().strftime('%Y-%m-%d')
	# my_time = '2020-11-24'
	if sys.argv.__len__() == 2:
		if sys.argv[1]=='month':
			fday,lday=date_util.getLastMonthDay()
			i=0
			my_time = fday.strftime("'%Y-%m-%d'")
			while True:
				my_time=(fday+timedelta(days=i)).strftime("%Y-%m-%d")
				print(my_time)
				start_cost_job()
				if my_time==lday.strftime("%Y-%m-%d"):
					exit(0)
				else:
					i+=1

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

			# yestoday = (datetime.today() - timedelta(days=1)).strftime('%Y-%m-%d')
			# my_time = yestoday
			# print("跑昨天[" + yestoday + "]数据")

	start_cost_job()

	# run history data
	# i=1
	# while True:
	# 	my_time=(datetime.today()-timedelta(days=i)).strftime('%Y-%m-%d')
	# 	print("run["+my_time+"]data")
	# 	start_cost_job()
	# 	i+=1
	# 	if my_time=='2020-12-01':
	# 		break