DataCenterGame/app/etl/dw/dw_image_cost_day.py

import logging
from model.DataBaseUtils import MysqlUtils,CkUtils
from model.DateUtils import DateUtils
from model.DingTalkUtils import DingTalkUtils
logging.getLogger().setLevel(logging.WARNING)
import pandas as pd
db = MysqlUtils()
ck = CkUtils()
du=DateUtils()


def run(dt):
    sql=f"""SELECT a.dt,b.type,sum(a.cost),sum(view_count),sum(click_count),sum(follow_count),sum(order_count),sum(order_amount),
            title,description,book,platform,stage,e.channel,pitcher,ifnull(image_id,'')
            from 
            ad_cost_day a 
            left join ad_info b on a.ad_id=b.ad_id
            left join adcreative_info c on b.adcreative_id=c.adcreative_id
            left join channel_by_account_daily e on b.account_id=e.account_id and a.dt=e.dt
            left join channel_info_daily f on e.channel=f.channel and e.dt=f.dt
            where a.dt='{dt}'  
            group by a.dt,b.type,title,description,book,platform,stage,image_id,e.channel,pitcher  
            
            """

    # print(sql)
    data = db.quchen_text.get_data_list(sql)

    # print(data)

    # 图片链接拼接

    li = []
    for i in data:
        # print(i)
        li.extend(i[-1].split(','))
    # print(li)

    sql3 = f"select image_id,preview_url from image_info where  image_id in ({str(set(li))[1:-1]})"

    image_di = {}
    image_data = db.quchen_text.getData(sql3)
    for x in image_data:
        image_di[x[0]] = x[1]

    # print(image_di)

    for i in data:
        y = ''
        for j in i[-1].split(','):
            if image_di.get(j):
                y = y + ',' + image_di.get(j)
        i.append(y[1:])

    # print(data)
    # print(data)
    db.dm.execute(f'delete from dw_image_cost_day where dt="{dt}"')
    db.dm.executeMany("replace into dw_image_cost_day values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)",data)



def hourly():
    try:
        run(du.getNow())
    except:
        DingTalkUtils.send("广告数据清洗失败")



if __name__ == '__main__':
    # run('2021-04-09')

    for i in du.getDateLists('2020-04-20','2021-04-08'):
        print(i)
        run(i)