AppKuaishouMaterialProjectRelation.py 1.8 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051
  1. # Author renyupeng
  2. # coding=utf-8
  3. # @Time : 2022/8/4 2:35 下午
  4. # @Site :
  5. # @File : AppKuaishouMaterialProjectRelation.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 AppKuaishouMaterialProjectRelation:
  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=2"""
  17. account_list = TidbConn.quary(self.conn, sql)
  18. for account_id in account_list:
  19. try:
  20. sql = """
  21. replace into application.kuaishou_project_material_relation
  22. select project_id, signature, date_format(stat_date,'%Y%m%d') as create_time from(
  23. select project_id, signature,stat_date,row_number() over (partition by signature order by stat_date desc) as rn
  24. from (select account_id, signature,stat_date
  25. from media_api.kuaishou_video_list where account_id ={account_id}
  26. and stat_date>=date_format(date_sub(now(),interval 3 day),'%Y-%m-%d')
  27. and signature is not null
  28. group by stat_date, 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.account_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. AppKuaishouMaterialProjectRelation().handler()