ai_strategy_request_handler.py 7.1 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202
  1. from typing import Optional, Awaitable
  2. import pandas as pd
  3. import numpy as np
  4. import datetime
  5. import pymysql
  6. from sqlalchemy import create_engine
  7. import simplejson
  8. import requests
  9. import tornado.web
  10. import pandas as pd
  11. import json
  12. import numpy as np
  13. import datetime
  14. from sqlalchemy import create_engine
  15. from db_config import *
  16. import traceback
  17. class AiStrategyRequestParse(tornado.web.RequestHandler):
  18. def initialize(self, logger):
  19. self.logger = logger
  20. def post(self):
  21. data = self.request.body
  22. data = str(data, 'utf8')
  23. self.logger.info("*************************************** NEW REQUEST ***************************************")
  24. self.logger.info("raw data from request is %s" % data)
  25. # 1、新增广告组
  26. # addClass 调用新增的操作类
  27. # 2、修改操作
  28. # modifyClass 调用修改的操作类
  29. # 3、关停操作
  30. # offClass 调用关停的操作类
  31. class ParseAddRequest(object):
  32. def __init__(self):
  33. pass
  34. class ParseModifyRequest(object):
  35. def __init__(self):
  36. pass
  37. class ParseShutDownRequest(object):
  38. def __init__(self):
  39. pass
  40. # -----------------------------------------------
  41. # 1、 获取需要创建广告组所需要的视频 --调用于蒙的视频接口
  42. def get_video(acc_id):
  43. # url = "http://192.168.1.8:8080/jeecg-boot/kuaishou/Ai/getNewVideoList"
  44. # request_data = {'account_id': acc_id}
  45. # request = requests.post(url, request_data)
  46. # res_data = simplejson.loads(request.text)
  47. video_dict = {
  48. "code": 0,
  49. "data": [
  50. {
  51. "video_url": "http://ucmov.a.yximgs.com/upic/2021/01/07/11/BMjAyMTAxMDcxMTI4MTlfMTI1MDY0NTMyOV80MTk5NzI5Njc0Ml8wXzM=_b_B210bce3effacab7744fa87897f2b4662.mp4?tag=1-1609990769-unknown-0-9ercfqrqp4-dd65af98d83fca04&clientCacheKey=3xigm8jk8fqbti6_b.mp4&tt=b&di=811c96e5&bp=13890",
  52. "photo_id": "5227271831775447110",
  53. "signature": "ed963b1333fe607d563d5cb0d916b65b",
  54. "stat_date": "2021-01-12 10:57:26",
  55. "channel_type": 0,
  56. "imageList": [
  57. "5ce93696dac9a326ff18505f49391297",
  58. "a368dd69d95539f26f5bee9d1580ccf8",
  59. "33b3e1a2cad48a2309d6ce351e6082fd",
  60. "56f6a8ec736dcdb17a2f052ebfcc255e",
  61. "addd5dc29c52ef863a40c6b688ce7951",
  62. "cb6e10ec5258145056aaafad647e1c36",
  63. "763344141ace6707c79f54bba3db5d1a",
  64. "86e061c34bf675690cb57943d8675130",
  65. "5d5258e7ff4af9dbb196e10ec4e878ce",
  66. "59ca244d88e7f23ef47744110e753bac",
  67. "260e0f08aebbf168b5fd80ee864b2e4c",
  68. "49a551a978d7dd182a04bd6af7e0c08d",
  69. "0c5c43f3b2c215e66d646fb9c03e110b",
  70. "44731b7d8b7e93ac12e8dcf3d34cc272",
  71. "2227f6cc89a5c0f242e04762b0444032"
  72. ]
  73. },
  74. {
  75. "video_url": "http://sjymov.a.yximgs.com/upic/2020/12/03/15/BMjAyMDEyMDMxNTEyNDZfMTI1MDY0NTMyOV80MDE1Mjg1MDE5OV8wXzM=_b_B7fd27f653e6af4e8cf0979041e31a38b.mp4?tag=1-1606982411-unknown-0-1ksr2io24f-3a57957a462092c7&clientCacheKey=3xmtw52hxui5j7k_b.mp4&tt=b&di=8bba1fd5&bp=13890",
  76. "photo_id": "5211227760959967759",
  77. "signature": "fc7717ac4f08316e81aa463c720226c2",
  78. "stat_date": "2021-01-12 10:57:21",
  79. "channel_type": 0,
  80. "imageList": [
  81. "11836a6a4042b254a64bd9a6ca99f9b5",
  82. "82349cfa1e26bd17397a303b85dd0fbb",
  83. "04b1dc6793b0d2a65d8f8b45ce37744d",
  84. "4165fa246defbced026558d62ef30ed5",
  85. "064af2a19b313c0c436f6c2e4d2384eb",
  86. "13997aaa0294bcf1ca0c27a43aecceb2",
  87. "fb138ea0251f0a1c243cb287f1f865d8",
  88. "83e9292549f9617ceb5cb468cd5cce0d",
  89. "4bf798acb18747baacec0762d76e2006",
  90. "74d7b71c4e7be44d12cd7af03568053b",
  91. "b74620eeaa6c19620530b7cb553e954d",
  92. "2a1c907e5e3cf0cf2e871e5df16cca8f",
  93. "981660adc052f807ece9155124290ae1",
  94. "1247852f30b51511b510ef3b2d7f0b70",
  95. "b13119ff3fb326adfdcd04bb87324d02"
  96. ]
  97. }
  98. ],
  99. "message": "SUCCESS"
  100. }
  101. if video_dict['code'] != 0:
  102. return
  103. else:
  104. return video_dict
  105. # 拼接请求的json
  106. # example: 整个项目近30天内,消耗top10的视频,创建组
  107. account_id = 9556344
  108. request_dict = {}
  109. # 1、读取 客户策略表中 status = 1 的记录 (ctop_ai_kuaishou_advertiser_strategy)
  110. get_advertiser_strategy_sql = """
  111. select *
  112. from ctop_ai_kuaishou_advertiser_strategy
  113. where account_id = %d
  114. and status = 1
  115. """ % account_id
  116. advertiser_strategy_df = pd.read_sql(get_advertiser_strategy_sql, engine)
  117. advertiser_strategy_drop_cols = ['id', 'status', 'create_time', 'effective_time', 'expiry_time']
  118. advertiser_strategy_df.drop(columns=advertiser_strategy_drop_cols, inplace=True)
  119. request_dict['advertiser_strategy_info'] = advertiser_strategy_df.T.to_dict()[0]
  120. # 2、拼接请求的json
  121. # 获取视频方式一
  122. days = 30
  123. num = 10
  124. sql = """
  125. select * from ctop_kuaishou_video_get
  126. where account_id = %d
  127. and signature in
  128. (
  129. select
  130. daily_material.signature
  131. from
  132. ctop_kuaishou_report_daily_material daily_material
  133. inner join
  134. ctop_user_allocation allocation
  135. on daily_material.account_id = allocation.account_id
  136. where allocation.project_id in (select project_id from ctop_user_allocation where account_id = %d)
  137. and to_days(now()) - to_days(daily_material.stat_date) <= %d
  138. and daily_material.signature is not null
  139. GROUP BY daily_material.signature
  140. order by sum(daily_material.charge) desc
  141. limit %d
  142. )
  143. """ % (account_id, account_id, days, num)
  144. # video_df = pd.read_sql(sql, engine)
  145. # print(video_df.shape)
  146. # 获取视频方式二
  147. # 离线分析手动提取 signature,然后创建广告组
  148. # signature_list = ['f17059ef15d5940a43bd38f9b13dc551', 'c0065cbd882d94c7c0a808db68f73666']
  149. # sql = """
  150. # select * from ctop_kuaishou_video_get
  151. # where account_id = %d
  152. # and signature in %s
  153. # """ % (account_id, tuple(signature_list))
  154. # video_df = pd.read_sql(sql, engine)
  155. # print(video_df)
  156. # 2.1 计划层级的参数
  157. campaign_name = "户14-上下滑&信息流-跑量视频-剪辑-低价-低价包邮-活动-年龄18岁以上-单出价-" + \
  158. datetime.datetime.now().strftime("%Y/%m/%d %H:%M:%S")
  159. # campaign_id 为空表示需要创建广告计划, 不为空则表示在该广告计划下新增广告组
  160. campaign_dict = {}
  161. campaign_id = None
  162. campaign_dict['campaign_name'] = campaign_name
  163. campaign_dict['campaign_id'] = campaign_id
  164. campaign_dict['campaign_type'] = 2
  165. campaign_dict['day_budget'] = None
  166. campaign_dict['day_budget_schedule'] = None
  167. campaign_dict['operation_type'] = "add"
  168. request_dict['campaign'] = campaign_dict
  169. # 2.2 组层级的参数
  170. if __name__ == '__main__':
  171. print("test")