| 1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253 | # Author renyupeng# coding=utf-8# @Time    : 2022/8/4 2:35 下午# @Site    : # @File    : AppBytedanceMaterialProjectRalation.py# @Software: PyCharm# @contact: renyupeng@c-top.com.cn# @Tel 1501435553import loggingfrom Apietl.conn.TidbConn import TidbConnclass AppBytedanceMaterialProjectRalation:    def __init__(self):        self.conn = TidbConn.get_connection()    def handler(self):        try:            sql = """select advertiser_id from media_api.bytedance_file_video_get             where create_time>=date_format(date_sub(now(),interval 3 day),'%Y-%m-%d')            group by advertiser_id"""            account_list = TidbConn.quary(self.conn, sql)            for account_id in account_list:                try:                    sql = """replace into application.bytedance_project_material_relationselect 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 rnfrom (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_idgroup 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 -1if __name__ == '__main__':    AppBytedanceMaterialProjectRalation().handler()
 |