# This is a sample Python script. # Press ⌃R to execute it or replace it with your code. # Press Double ⇧ to search everywhere for classes, files, tool windows, actions, and settings. import hashlib import json import os import subprocess import time import traceback import urllib import datetime import requests import pymysql from contextlib import contextmanager from sqlalchemy import create_engine, Numeric, Float from sqlalchemy.orm import sessionmaker from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Integer, String, DateTime, Text import ssl ssl._create_default_https_context = ssl._create_unverified_context HOST = 'rm-bp1nx318263k95yo3318.mysql.rds.aliyuncs.com' PORT = '3306' DATABASE = 'uservideo_bi' USERNAME = 'majin' PASSWORD = 'E5d2c960fdf3f5f0be5a27eea2f906ef' DB_URI = "mysql+pymysql://{username}:{password}@{host}:{port}/{db}?charset=utf8".format(username=USERNAME, password=PASSWORD, host=HOST, port=PORT, db=DATABASE) # HOST = 'rm-bp1k5853td1r25g3n690.mysql.rds.aliyuncs.com' # PORT = '3306' # DATABASE = 'mpad' # USERNAME = 'majin' # PASSWORD = 'e5d2c960fdf3f5f0be5a27eea2f906ef' # DB_URI = "mysql+pymysql://{username}:{password}@{host}:{port}/{db}?charset=utf8".format(username=USERNAME, # password=PASSWORD, # host=HOST, port=PORT, # db=DATABASE) Base = declarative_base() class WECHAT_AD_PUBLISHER_ADUNIT_GENERAL(Base): __tablename__ = 'wechat_ad_publisher_adunit_general' id = Column(Integer, primary_key=True) ad_unit_id = Column(String(1000)) ad_unit_name = Column(String(1000)) ad_slot = Column(String(1000)) click_count = Column(Integer, default=0) click_rate = Column(Float, default=0.0) date = Column(String(1000)) ecpm = Column(String(1000)) exposure_count = Column(Integer, default=0) exposure_rate = Column(Float, default=0.0) income = Column(Integer, default=0) req_succ_count = Column(Integer, default=0) app_type = Column(Integer, default=0) slot_str = Column(String(1000)) date_str = Column(String(1000)) def __init__(self): print(self.ad_unit_id) #print("app_type = "+ app_type +" adunit_id = "+self.ad_unit_id) def __repr__(self): return '' % self.ad_unit_id class WECHAT_AD_PUBLISHER_ADPOS_GENERAL(Base): __tablename__ = 'wechat_ad_publisher_adpos_general' id = Column(Integer, primary_key=True) ad_slot = Column(String(1000)) click_count = Column(Integer, default=0) click_rate = Column(Float, default=0.0) date = Column(String(1000)) ecpm = Column(String(1000)) exposure_count = Column(Integer, default=0) exposure_rate = Column(Float, default=0.0) income = Column(Integer, default=0) req_succ_count = Column(Integer, default=0) app_type = Column(Integer, default=0) slot_str = Column(String(1000)) date_str = Column(String(1000)) def __init__(self): print(self.ad_slot) #print("app_type = "+app_type+" ad_solt = "+self.ad_slot) def __repr__(self): return '' % self.ad_slot @contextmanager def session_maker(session=None, session_factory=None): try: if session_factory is None: engine = create_engine(DB_URI) session_factory = sessionmaker(bind=engine) if session is None: session = session_factory() yield session except: session.rollback() raise else: session.commit() # logger.debug('session.commit(){}'.format(session)) finally: session.close() # logger.debug('session.close(){}'.format(session)) def add_ad_data(data, app_type): # Use a breakpoint in the code line below to debug your script. print(f'Hi, {data}') # Press ⌘F8 to toggle the breakpoint. stat_item = data['stat_item'] try: with session_maker() as session: wechat_ad_publisher_adunit_general = WECHAT_AD_PUBLISHER_ADUNIT_GENERAL() wechat_ad_publisher_adunit_general.ad_unit_id = data['ad_unit_id'] wechat_ad_publisher_adunit_general.ad_unit_name = data['ad_unit_name'] wechat_ad_publisher_adunit_general.ad_slot = stat_item['ad_slot'] wechat_ad_publisher_adunit_general.click_count = stat_item['click_count'] wechat_ad_publisher_adunit_general.click_rate = stat_item['click_rate'] wechat_ad_publisher_adunit_general.date = stat_item['date'] wechat_ad_publisher_adunit_general.ecpm = stat_item['ecpm'] wechat_ad_publisher_adunit_general.exposure_count = stat_item['exposure_count'] wechat_ad_publisher_adunit_general.exposure_rate = stat_item['exposure_rate'] wechat_ad_publisher_adunit_general.income = stat_item['income'] wechat_ad_publisher_adunit_general.req_succ_count = stat_item['req_succ_count'] wechat_ad_publisher_adunit_general.slot_str = stat_item['slot_str'] wechat_ad_publisher_adunit_general.date_str = stat_item['date'].replace('-','') wechat_ad_publisher_adunit_general.app_type = app_type session.add(wechat_ad_publisher_adunit_general) print('OK') #print('app_type = '+app_type+' ad OK') except Exception as e: traceback.print_exc() print(traceback.format_exc()) #print("app_type = "+app_type+" ad trace = "traceback.format_exc()) def add_ad_adpos_data(stat_item, app_type): # Use a breakpoint in the code line below to debug your script. print(f'Hi, {stat_item}') # Press ⌘F8 to toggle the breakpoint. try: with session_maker() as session: wechat_ad_publisher_adpos_general = WECHAT_AD_PUBLISHER_ADPOS_GENERAL() wechat_ad_publisher_adpos_general.ad_slot = stat_item['ad_slot'] wechat_ad_publisher_adpos_general.click_count = stat_item['click_count'] wechat_ad_publisher_adpos_general.click_rate = stat_item['click_rate'] wechat_ad_publisher_adpos_general.date = stat_item['date'] wechat_ad_publisher_adpos_general.ecpm = stat_item['ecpm'] wechat_ad_publisher_adpos_general.exposure_count = stat_item['exposure_count'] wechat_ad_publisher_adpos_general.exposure_rate = stat_item['exposure_rate'] wechat_ad_publisher_adpos_general.income = stat_item['income'] wechat_ad_publisher_adpos_general.req_succ_count = stat_item['req_succ_count'] wechat_ad_publisher_adpos_general.slot_str = stat_item['slot_str'] wechat_ad_publisher_adpos_general.date_str = stat_item['date'].replace('-','') wechat_ad_publisher_adpos_general.app_type = app_type session.add(wechat_ad_publisher_adpos_general) print('OK') #print('app_type = '+app_type+' ad solt OK') except Exception as e: traceback.print_exc() print(traceback.format_exc()) #print("app_type = "+app_type+" solt trace"+traceback.format_exc()) def post_inform(url, content_text): url = url data = json.dumps(content_text) data = bytes(data, 'utf8') print(data) #print("app_type = "+app_type+" post data = "+data) headers = {"Content-Type": 'application/json'} req = urllib.request.Request(url=url, headers=headers, data=data) try: resp = urllib.request.urlopen(req).read() print(resp.decode('utf-8')) #print("app_type = "+app_type+" post resp = "+resp.decode('utf-8')) return resp.decode('utf-8') except Exception as e: print(e) #print("app_type = "+app_type+"post e = "+e) def get_inform(url): url = url headers = {"Content-Type": 'application/json'} req = urllib.request.Request(url=url, headers=headers) try: resp = urllib.request.urlopen(req).read() print(resp.decode('utf-8')) #print("app_type = "+app_type+" get resp = "+resp.decode('utf-8')) return resp.decode('utf-8') except Exception as e: print(e) #print("app_type = "+app_type+"get e = "+e) def get_mp_info(app_type): datestr = datetime.datetime.strftime(datetime.datetime.now() - datetime.timedelta(days=+1), '%Y-%m-%d') print(datestr) #print("app_type = "+ app_type +" date = " + datestr) time_str = time.strftime("%Y:%m:%d %H") print(time_str) #print("app_type="+app_type+" time = "+time_str) md5 = hashlib.md5('{}'.format(time_str).encode(encoding='UTF-8')).hexdigest() print(md5) #print("app_type ="+app_type+"md5 = "+md5) getliveaccesstoken_url = "https://longvideoapi.piaoquantv.com/longvideoapi/weixin/getWxAccessToken/{}".format(app_type) ret = get_inform(getliveaccesstoken_url) data = json.loads(ret).get('data',{}) print(data) #print("app_type = "+app_type+" getWxAccessToken date = "+data) with session_maker() as session: task = session.query(WECHAT_AD_PUBLISHER_ADUNIT_GENERAL).filter_by(date=datestr,app_type=app_type).first() if task is None: getweanalysisappiddailyvisittrend_url = 'https://api.weixin.qq.com/publisher/stat?action=publisher_adunit_general&access_token={}&page=1&page_size=100&start_date={}&end_date={}'.format( data, datestr, datestr) print(getweanalysisappiddailyvisittrend_url) #print("app_type = "+app_type+" publisher/stat adunit = "+getweanalysisappiddailyvisittrend_url) ret = get_inform(getweanalysisappiddailyvisittrend_url) list = json.loads(ret).get('list',[]) for item in list: add_ad_data(item, app_type) task = session.query(WECHAT_AD_PUBLISHER_ADPOS_GENERAL).filter_by(date=datestr, app_type=app_type).first() if task is None: getweanalysisappiddailyvisittrend_url = 'https://api.weixin.qq.com/publisher/stat?action=publisher_adpos_general&access_token={}&page=1&page_size=100&start_date={}&end_date={}'.format( data, datestr, datestr) print(getweanalysisappiddailyvisittrend_url) #print("app_type = "+app_type +" publisher/stat adops = "+getweanalysisappiddailyvisittrend_url) ret = get_inform(getweanalysisappiddailyvisittrend_url) list = json.loads(ret).get('list',[]) for item in list: add_ad_adpos_data(item, app_type) summary = json.loads(ret)['summary'] summary['ad_slot'] = 'SLOT_ID_WEAPP_ALL' summary['date'] = datestr summary['slot_str'] = 'summary' add_ad_adpos_data(summary, app_type) # Press the green button in the gutter to run the script. if __name__ == '__main__': get_mp_info(0) get_mp_info(3) get_mp_info(5) get_mp_info(4) get_mp_info(6) get_mp_info(19) get_mp_info(18) get_mp_info(21) get_mp_info(22) get_mp_info(17) get_mp_info(2) get_mp_info(23) get_mp_info(24) get_mp_info(25) #get_mp_info(11) #get_mp_info(26) # See PyCharm help at https://www.jetbrains.com/help/pycharm/