123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869 |
- # 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)
|