AppBytedanceMaterialProjectRalation.py 1.9 KB

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253
  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 advertiser_id from media_api.bytedance_file_video_get
  17. where create_time>=date_format(date_sub(now(),interval 3 day),'%Y-%m-%d')
  18. group by advertiser_id"""
  19. account_list = TidbConn.quary(self.conn, sql)
  20. for account_id in account_list:
  21. try:
  22. sql = """
  23. replace into application.bytedance_project_material_relation
  24. select project_id, signature, date_format(create_time,'%Y%m%d') from(
  25. select project_id, signature,create_time,row_number() over (partition by signature order by create_time desc) as rn
  26. from (select advertiser_id, signature,create_time
  27. from media_api.bytedance_file_video_get where advertiser_id ={account_id}
  28. and create_time>=date_format(date_sub(now(),interval 3 day),'%Y-%m-%d')
  29. and signature is not null
  30. group by advertiser_id, signature) a
  31. left join
  32. (select account_id, project_id from `jeecg-boot`.ctop_user_allocation where media_id != 2) b
  33. on a.advertiser_id = b.account_id
  34. group by project_id, signature)t
  35. where rn <=1
  36. """.format(account_id=account_id[0])
  37. print(sql)
  38. TidbConn.upsert(conn=self.conn, sql_buff=sql)
  39. self.conn.commit()
  40. except Exception as e:
  41. logging.error(e)
  42. return -1
  43. except Exception as e:
  44. logging.error(e)
  45. return -1
  46. if __name__ == '__main__':
  47. AppBytedanceMaterialProjectRalation().handler()