DataCenterGame/app/etl/dw/dw_ad_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,a.ad_id,b.ad_name,b.type,'',a.cost,view_count,click_count,follow_count,order_count,order_amount,
            title,description,e.channel,pitcher,book,platform,stage,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}'  """


    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)

    db.dm.executeMany("replace into dw_ad_day values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)",data)



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



if __name__ == '__main__':
    day()