script/mpad.py

# 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 '<WECHAT_AD_PUBLISHER_ADUNIT_GENERAL %r>' % 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 '<wechat_ad_publisher_adpos_general %r>' % 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/