# Author renyupeng # coding=utf-8 # @Time : 2021/9/27 10:50 上午 # @Site : # @File : MaterialCost.py # @Software: PyCharm # @contact: renyupeng@c-top.com.cn # @Tel 1501435553 import logging from math import ceil from Apietl.utlis.Utils import Utils from Apietl.conn.TidbConn import TidbConn class AdDailyReport: def __init__(self): self.conn = TidbConn.get_connection() self.parm = Utils.get_args() def daily_report(self): try: sql = """select account_id from `jeecg-boot`.ctop_user_allocation where account_status = 0 and media_id = 2 and project_id in ( 50, 67, 123, 2400915, 7920890, 12330885, 14310886, 14490902, 15120886, 15750885 )""" account_list = TidbConn.quary(self.conn, sql) for account_id in account_list: sql = """ replace into application.material_cost select account_id,a.signature,video_name,photo_url,charge,frast_cost_time from (select account_id,signature,sum(charge) as charge,video_name,photo_url from application.kuaishou_material_video_report_daily_dw where signature is not null group by account_id,signature)a left join application.material_farst_cost_time b on a.signature=b.signature where account_id={account_id} """.format(account_id=account_id[0]) print(sql) TidbConn.upsert(conn=self.conn, sql_buff=sql) self.conn.commit() TidbConn.conn_close(conn=self.conn) return 0 except Exception as e: logging.error(e) return -1 if __name__ == '__main__': status = AdDailyReport().daily_report() if status == 0: print("作业执行成功") exit(0) else: print("作业执行失败") exit(-1)