#!/usr/bin/env python3 # -*- coding: utf-8 -*- """ Created on Thu Jun 4 15:06:05 2020 @author: chencong """ 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 import importlib importlib.reload(tl) from concurrent.futures import ThreadPoolExecutor 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": time.strftime("%Y-%m-%d", time.localtime()), "end_date": time.strftime("%Y-%m-%d", time.localtime()) }, "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=30) 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=30) 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 = '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.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 = '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);' 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(): st_unix_time = time.time() st_unix = int((st_unix_time + 8 * 3600) // 3600 * 3600 - 8 * 3600 - 3600) et_unix = int((st_unix_time + 8 * 3600) // 3600 * 3600 - 8 * 3600) # et_unix = et_unix - 1 st_dt = time.strftime("%Y-%m-%dT%H:%M:%S", time.localtime(st_unix)) + '+08:00' et_dt = time.strftime("%Y-%m-%dT%H:%M:%S", time.localtime(et_unix)) + '+08:00' print(st_dt, et_dt) mysql_insert_daily_vx(get_daily_vx()) mysql_insert_daily_qq(get_daily_qq()) if __name__ == '__main__': print(datetime.today()) start_cost_job()