MaterialCost.py 1.9 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869
  1. # Author renyupeng
  2. # coding=utf-8
  3. # @Time : 2021/9/27 10:50 上午
  4. # @Site :
  5. # @File : MaterialCost.py
  6. # @Software: PyCharm
  7. # @contact: renyupeng@c-top.com.cn
  8. # @Tel 1501435553
  9. import logging
  10. from math import ceil
  11. from Apietl.utlis.Utils import Utils
  12. from Apietl.conn.TidbConn import TidbConn
  13. class AdDailyReport:
  14. def __init__(self):
  15. self.conn = TidbConn.get_connection()
  16. self.parm = Utils.get_args()
  17. def daily_report(self):
  18. try:
  19. sql = """select account_id
  20. from `jeecg-boot`.ctop_user_allocation
  21. where account_status = 0
  22. and media_id = 2
  23. and project_id in (
  24. 50,
  25. 67,
  26. 123,
  27. 2400915,
  28. 7920890,
  29. 12330885,
  30. 14310886,
  31. 14490902,
  32. 15120886,
  33. 15750885
  34. )"""
  35. account_list = TidbConn.quary(self.conn, sql)
  36. for account_id in account_list:
  37. sql = """
  38. replace into application.material_cost
  39. select
  40. account_id,a.signature,video_name,photo_url,charge,frast_cost_time
  41. from
  42. (select account_id,signature,sum(charge) as charge,video_name,photo_url from application.kuaishou_material_video_report_daily_dw
  43. where signature is not null
  44. group by account_id,signature)a
  45. left join application.material_farst_cost_time b
  46. on a.signature=b.signature
  47. where account_id={account_id}
  48. """.format(account_id=account_id[0])
  49. print(sql)
  50. TidbConn.upsert(conn=self.conn, sql_buff=sql)
  51. self.conn.commit()
  52. TidbConn.conn_close(conn=self.conn)
  53. return 0
  54. except Exception as e:
  55. logging.error(e)
  56. return -1
  57. if __name__ == '__main__':
  58. status = AdDailyReport().daily_report()
  59. if status == 0:
  60. print("作业执行成功")
  61. exit(0)
  62. else:
  63. print("作业执行失败")
  64. exit(-1)