AppBytedanceMaterialProjectRalation.py 1.8 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051
  1. # Author renyupeng
  2. # coding=utf-8
  3. # @Time : 2022/8/4 2:35 下午
  4. # @Site :
  5. # @File : AppBytedanceMaterialProjectRalation.py
  6. # @Software: PyCharm
  7. # @contact: renyupeng@c-top.com.cn
  8. # @Tel 1501435553
  9. import logging
  10. from Apietl.conn.TidbConn import TidbConn
  11. class AppBytedanceMaterialProjectRalation:
  12. def __init__(self):
  13. self.conn = TidbConn.get_connection()
  14. def handler(self):
  15. try:
  16. sql = """select account_id from `jeecg-boot`.ctop_user_allocation where account_status=0 and media_id=1"""
  17. account_list = TidbConn.quary(self.conn, sql)
  18. for account_id in account_list:
  19. try:
  20. sql = """
  21. replace into application.bytedance_project_material_relation
  22. select project_id, signature, date_format(create_time,'%Y%m%d') from(
  23. select project_id, signature,create_time,row_number() over (partition by signature order by create_time desc) as rn
  24. from (select advertiser_id, signature,create_time
  25. from media_api.bytedance_file_video_get where advertiser_id ={account_id}
  26. and create_time>=date_format(date_sub(now(),interval 3 day),'%Y-%m-%d')
  27. and signature is not null and source='AD_SITE'
  28. group by advertiser_id, signature) a
  29. left join
  30. (select account_id, project_id from `jeecg-boot`.ctop_user_allocation where media_id != 2) b
  31. on a.advertiser_id = b.account_id
  32. group by project_id, signature)t
  33. where rn <=1
  34. """.format(account_id=account_id[0])
  35. print(sql)
  36. TidbConn.upsert(conn=self.conn, sql_buff=sql)
  37. self.conn.commit()
  38. except Exception as e:
  39. logging.error(e)
  40. return -1
  41. except Exception as e:
  42. logging.error(e)
  43. return -1
  44. if __name__ == '__main__':
  45. AppBytedanceMaterialProjectRalation().handler()