# Author renyupeng # coding=utf-8 # @Time : 2022/8/4 2:35 下午 # @Site : # @File : AppBytedanceMaterialProjectRalation.py # @Software: PyCharm # @contact: renyupeng@c-top.com.cn # @Tel 1501435553 import logging from Apietl.conn.TidbConn import TidbConn class AppBytedanceMaterialProjectRalation: 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=1""" account_list = TidbConn.quary(self.conn, sql) for account_id in account_list: try: sql = """ replace into application.bytedance_project_material_relation select project_id, signature, date_format(create_time,'%Y%m%d') from( select project_id, signature,create_time,row_number() over (partition by signature order by create_time desc) as rn from (select advertiser_id, signature,create_time from media_api.bytedance_file_video_get where advertiser_id ={account_id} and create_time>=date_format(date_sub(now(),interval 3 day),'%Y-%m-%d') and signature is not null and source='AD_SITE' group by advertiser_id, signature) a left join (select account_id, project_id from `jeecg-boot`.ctop_user_allocation where media_id != 2) b on a.advertiser_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__': AppBytedanceMaterialProjectRalation().handler()