# 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 `jeecg-boot`.ctop_user_allocation where account_status=0 and media_id=2""" 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, date_format(stat_date,'%Y%m%d') as create_time from( select project_id, signature,stat_date,row_number() over (partition by signature order by stat_date desc) as rn from (select account_id, signature,stat_date from media_api.kuaishou_video_list where account_id ={account_id} and stat_date>=date_format(date_sub(now(),interval 3 day),'%Y-%m-%d') and signature is not null group by stat_date, 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)t where rn <=1 """.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__': AppKuaishouMaterialProjectRelation().handler()