AppBytedanceMaterialProjectRalation.py 1.9 KB

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859
  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
  25. from (select advertiser_id, signature
  26. from media_api.bytedance_file_video_get where advertiser_id ={account_id}
  27. and create_time>=date_format(date_sub(now(),interval 3 day),'%Y-%m-%d')
  28. and signature is not null
  29. group by advertiser_id, signature) a
  30. left join
  31. (select account_id, project_id from `jeecg-boot`.ctop_user_allocation where media_id != 2) b
  32. on a.advertiser_id = b.account_id
  33. group by project_id, signature
  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. status = AppBytedanceMaterialProjectRalation().handler()
  46. if status == 0:
  47. print("作业执行成功")
  48. exit(0)
  49. else:
  50. print("作业执行失败")
  51. exit(-1)