ads_user_basic_behavior.py

# -*- coding: utf-8 -*-


'''
用户登录行为汇总,定时任务，每2个小时运行一次
'''

from common.file_uitil import get_file_path, get_file_name
from common.mysql_uitl import fetch_all, insert_batch

import logging
import sys

from common.file_uitil import get_file_path, get_file_name
from common.mysql_uitl import fetch_all, insert_batch


logging.basicConfig(format="%(asctime)s %(name)s:%(levelname)s:%(message)s", datefmt="%Y-%m-%d %H:%M:%S", level=logging.INFO)
file_name = sys.argv[0]


def ads_user_basic_behavior():
    user_basic_behavior_dict = query_ads_user_basic_behavior()
    save_ads_user_basic_behavior(user_basic_behavior_dict)


# 这里有点细节，没有严格去做。
def query_ads_user_basic_behavior():
    function_name = sys._getframe().f_code.co_name
    logging.info(f'{function_name} start')
    sql = '''
            select  env.user_id
                    ,u.real_name 
                    ,u.user_name
                    ,u.nickname
                    ,u.team_id
                    ,u.level_grade
                    ,env.top_env_name
                    ,env.recent_env_name
                    ,env.recent_time
                    ,coalesce(area.login_area, '') as login_area
                    ,coalesce(area.top_area, '') as top_area
                    ,coalesce(dur.live_total_dur, 0) as live_total_dur
                    ,coalesce(dur.course_total_dur, 0) as course_total_dur
                    ,coalesce(tel.phone_mode, '') as phone_mode
                    ,coalesce(tel.phone_name, '') as phone_name 
                    ,coalesce(tel.phone_brand, '') as phone_brand
            from    tamp_data_dws.dws_user_login_environment env
            left    join tamp_data_dws.dws_user_login_top_area area
            on      env.user_id = area.user_id 
            left    join  tamp_data_dws.dws_user_learn_total_dur dur
            on      env.user_id = dur.user_id 
            left    join   tamp_data_dws.dws_user_login_phone_mode tel
            on      env.user_id = tel.user_id
            inner   join tamp_analysis.user_info_view u
            on      env.user_id =u.user_id  
        '''
    user_basic_behavior_dict = fetch_all(sql, None)
    logging.info(f'{function_name} success')
    return user_basic_behavior_dict


def save_ads_user_basic_behavior(ret):
    function_name = sys._getframe().f_code.co_name
    if ret:
        logging.info(f'{function_name} start')
        row = 0
        fields = None
        place_holder = None
        values = list()
        for i in ret:
            row = row + 1
            fields = ','.join([f"`{k}`" for k in i.keys()])
            place_holder = ','.join(["%s" for _ in i.keys()])
            values.append(tuple(i.values()))
        sql = f'''replace into tamp_data_ads.ads_user_basic_behavior ( {fields} ) values ( {place_holder} )'''
        rs = insert_batch(sql, values)
        if rs == row or rs == 2 * row:  # 因为这里用的是replace
            logging.info(f'{function_name} success {row}')
        else:
            logging.error(f'{function_name} error 数据为:{row}行，插入成功为:{rs} 行 执行程序为：{file_name}')


if __name__ == '__main__':
    ads_user_basic_behavior()
    import datetime
    # begin = datetime.date(2021, 9, 7)
    # end = datetime.date(2021, 9, 7)
    # data_dt = begin
    # delta = datetime.timedelta(days=1)
    # while data_dt <= end:
    #     print(data_dt.strftime("%Y-%m-%d"))
    #     ads_user_login_behavior()
    #     data_dt += delta