| 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 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 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_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()
 
 
  |