# Author renyupeng # coding=utf-8 # @Time : 2022/8/4 2:35 下午 # @Site : # @File : AppKuaishouMaterialProjectRelation.py # @Software: PyCharm # @contact: renyupeng@c-top.com.cn # @Tel 1501435553 import logging from Apietl.conn.TidbConn import TidbConn class AppKuaishouMaterialProjectRelation: def __init__(self): self.conn = TidbConn.get_connection() def handler(self): try: sql = """select account_id from media_api.kuaishou_video_list where stat_date>=date_format(date_sub(now(),interval 7 day),'%Y-%m-%d') group by account_id""" account_list = TidbConn.quary(self.conn, sql) for account_id in account_list: try: sql = """ replace into application.kuaishou_project_material_relation select project_id, signature from (select account_id, signature from media_api.kuaishou_video_list where account_id ={account_id} and stat_date>=date_format(date_sub(now(),interval 7 day),'%Y-%m-%d') and signature is not null group by account_id, signature) a left join (select account_id, project_id from `jeecg-boot`.ctop_user_allocation where media_id = 2) b on a.account_id = b.account_id group by project_id, signature """.format(account_id=account_id[0]) print(sql) TidbConn.upsert(conn=self.conn, sql_buff=sql) self.conn.commit() except Exception as e: logging.error(e) return -1 except Exception as e: logging.error(e) return -1 if __name__ == '__main__': status = AppKuaishouMaterialProjectRelation().handler() if status == 0: print("作业执行成功") exit(0) else: print("作业执行失败") exit(-1)