AppKuaishouVideoList.py 8.8 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193
  1. # Author renyupeng
  2. # coding=utf-8
  3. # @Time : 2021/11/12 10:20 上午
  4. # @Site :
  5. # @File : AppKuaishouVideoList.py
  6. # @Software: PyCharm
  7. # @contact: renyupeng@c-top.com.cn
  8. # @Tel 1501435553
  9. import datetime
  10. import logging
  11. from math import ceil
  12. from Apietl.conn.TidbConn import TidbConn
  13. class AppKuaishouVideoList:
  14. def __init__(self):
  15. self.conn = TidbConn.get_connection()
  16. self.date_time = (datetime.date.today() + datetime.timedelta(-1)).strftime("%Y-%m-%d")
  17. def execSql(self):
  18. try:
  19. sql = """select account_id from media_api.kuaishou_video_list where date_format(stat_date, '%Y-%m-%d') >= date_sub('{date_time}', INTERVAL 13 DAY)
  20. group by account_id """.format(date_time=self.date_time)
  21. account_list = TidbConn.quary(self.conn, sql)
  22. for account_id in account_list:
  23. try:
  24. sql = """
  25. replace into application.app_kuaishou_video_list
  26. select account_id,
  27. status,
  28. photo_id,
  29. width,
  30. height,
  31. url,
  32. cover_url,
  33. material_type,
  34. channel_type,
  35. v.stat_date,
  36. case when v.signature is null then '' else v.signature end as signature,
  37. photo_name,
  38. case when tag_category_id is null then '' else tag_category_id end as tag_category_id,
  39. tag_category_name,
  40. case when parent_id is null then '' else parent_id end as parent_id,
  41. parent_name,
  42. case when tag_code is null then '' else tag_code end as tag_code,
  43. tag_name,
  44. clip_id,
  45. clip_name,
  46. shot_id,
  47. shot_name,
  48. plan_id,
  49. plan_name,
  50. leader_id,
  51. leader_name,
  52. material_innovate,
  53. hot.stat_date,
  54. effect.stat_date
  55. from (
  56. select `account_id`,
  57. `status`,
  58. `photo_id`,
  59. `width`,
  60. `height`,
  61. `url`,
  62. `cover_url`,
  63. `material_type`,
  64. `channel_type`,
  65. `stat_date`,
  66. `signature`,
  67. case when t.material_name is null then a.photo_name else t.material_name end as photo_name,
  68. clip_id,
  69. clip_name,
  70. shot_id,
  71. shot_name,
  72. plan_id,
  73. plan_name,
  74. leader_id,
  75. leader_name,
  76. material_innovate
  77. from (select `account_id`,
  78. `status`,
  79. `photo_id`,
  80. `width`,
  81. `height`,
  82. `url`,
  83. `cover_url`,
  84. `material_type`,
  85. `channel_type`,
  86. `stat_date`,
  87. `signature`,
  88. `photo_name`
  89. from media_api.kuaishou_video_list
  90. where date_format(stat_date, '%Y-%m-%d') >= date_sub('{date_time}', INTERVAL 13 DAY)
  91. and account_id ={account_id}
  92. ) a
  93. left join
  94. (select material_id,
  95. clip_id,
  96. clip_name,
  97. shot_id,
  98. shot_name,
  99. plan_id,
  100. plan_name,
  101. leader_id,
  102. leader_name,
  103. material_name,
  104. material_innovate
  105. from (select cma.material_id,
  106. clip_id,
  107. clip.realname as clip_name,
  108. shot_id,
  109. shot.realname as shot_name,
  110. plan_id,
  111. plan.realname as plan_name,
  112. cma.leader_id,
  113. leader.realname as leader_name,
  114. material_name,
  115. material_innovate
  116. from `jeecg-boot`.ctop_material_ascription cma
  117. left join
  118. `jeecg-boot`.ctop_material_info b
  119. on cma.material_id = b.id
  120. left join `jeecg-boot`.sys_user clip on clip.id = cma.clip_id
  121. left join `jeecg-boot`.sys_user shot on shot.id = cma.shot_id
  122. left join `jeecg-boot`.sys_user plan on plan.id = cma.plan_id
  123. left join `jeecg-boot`.sys_user leader on leader.id = cma.leader_id
  124. ) v) t
  125. on a.signature = t.material_id) v
  126. left join
  127. (select code,
  128. tag_category_id,
  129. tag_category_name,
  130. parent_id,
  131. parent_name,
  132. group_concat(tag_code order by tag_code separator '-') tag_code,
  133. group_concat(tag_name order by tag_code separator '-') tag_name
  134. from (select a.code, b.tag_category_id, b.tag_category_name, b.parent_id, b.parent_name, b.tag_code, b.tag_name
  135. from `jeecg-boot`.ctop_material_tag_info a
  136. left join
  137. (select tag_category_id,
  138. tag_category_name,
  139. case
  140. when parent_id is not null then parent_id
  141. when tag_category_id = 2 then null
  142. else tag_code end parent_id,
  143. case
  144. when parent_id = 60155 then '真人'
  145. when parent_id = 60156 then '制作'
  146. when parent_id = 60160 then '外拍场景'
  147. when parent_id = 60161 then '公司内景'
  148. when tag_category_id = 2 then null
  149. else '其他' end parent_name,
  150. tag_code,
  151. tag_name
  152. from `jeecg-boot`.ctop_tag_info) b
  153. on a.tag_code = b.tag_code) c
  154. group by code, tag_category_id, tag_category_name, parent_id, parent_name) m
  155. on v.signature = m.code
  156. left join (
  157. select signature,effect_date as stat_date from application.effect_material where media_id =2
  158. ) effect
  159. on effect.signature = v.signature
  160. left join (
  161. select signature,hot_date as stat_date from application.hot_material where media_id =2
  162. ) hot
  163. on hot.signature = v.signature
  164. group by account_id, photo_id, v.stat_date, v.signature, tag_category_id, parent_id, tag_code
  165. """.format(date_time=self.date_time, account_id=account_id[0])
  166. print(sql)
  167. TidbConn.upsert(conn=self.conn, sql_buff=sql)
  168. self.conn.commit()
  169. except Exception as e:
  170. logging.error(e)
  171. return -1
  172. TidbConn.conn_close(conn=self.conn)
  173. return 0
  174. except Exception as e:
  175. logging.error(e)
  176. return -1
  177. if __name__ == '__main__':
  178. status = AppKuaishouVideoList().execSql()
  179. if status == 0:
  180. print("作业执行成功")
  181. exit(0)
  182. else:
  183. print("作业执行失败")
  184. exit(-1)