AppKuaishouMaterialProjectRelation.py 1.9 KB

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253
  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 media_api.kuaishou_video_list
  17. where stat_date>=date_format(date_sub(now(),interval 3 day),'%Y-%m-%d')
  18. group by account_id"""
  19. account_list = TidbConn.quary(self.conn, sql)
  20. for account_id in account_list:
  21. try:
  22. sql = """
  23. replace into application.kuaishou_project_material_relation
  24. select project_id, signature, date_format(stat_date,'%Y%m%d') as create_time from(
  25. select project_id, signature,stat_date,row_number() over (partition by signature order by stat_date desc) as rn
  26. from (select account_id, signature,stat_date
  27. from media_api.kuaishou_video_list where account_id ={account_id}
  28. and stat_date>=date_format(date_sub(now(),interval 3 day),'%Y-%m-%d')
  29. and signature is not null
  30. group by stat_date, 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.account_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. AppKuaishouMaterialProjectRelation().handler()