qc-dgp/dgp/get_data_hourly.py

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
Created on Thu Jun  4 15:06:05 2020

@author: chencong
"""
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
from concurrent.futures import ThreadPoolExecutor
from datetime import datetime




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 get_daily_vx():
	token_list_v = tl.token_list_vx
	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 = tl.token_list_qq
	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_order(data):
	db = pymysql.connect('rm-bp1c9cj79872tx3aaro.mysql.rds.aliyuncs.com', 'superc', 'Cc719199895', 'quchen_text')
	cursor = db.cursor()
	time1 = time.time()
	# sql = 'insert ignore into quchen_text.order (amount,channel,channel_id,date,from_novel,order_id,order_time,platform,reg_time,stage,user_id) values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);'
	sql = 'replace into quchen_text.order(amount,channel,channel_id,date,from_novel,order_id,order_time,platform,reg_time,stage,user_id) values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);'
	try:
		cursor.executemany(sql, data)
		db.commit()
		cost_time = round((time.time() - time1) / 60, 1)
		print('insert_order access', len(data), 'cost_minutes:', cost_time)
	except:
		db.rollback()
		print('insert_order defeat')


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 = '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.execute(sql1, str(time.strftime("%Y-%m-%d", time.localtime())))
	# 	db.commit()
	# 	print('clear_daily_vx access')
	# except:
	# 	print('clear_daily_vx defeat')
	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 = 'delete from daily_qq where date = %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.execute(sql1, str(time.strftime("%Y-%m-%d", time.localtime())))
	# 	db.commit()
	# 	print('clear_daily_qq access')
	# except:
	# 	print('clear_daily_qq defeat')
	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')
	if sys.argv.__len__() ==2:
		yestoday=(datetime.today()-timedelta(days=1)).strftime('%Y-%m-%d')
		my_time=yestoday
		print("跑昨天["+yestoday+"]数据")
	start_cost_job()