AppBytedanceFileVideoGetAll.py 7.1 KB

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