ai_strategy_request_handler.py 14 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319
  1. import datetime
  2. import json
  3. import traceback
  4. import uuid
  5. import pandas as pd
  6. import pymysql
  7. import requests
  8. import simplejson
  9. import tornado.web
  10. from db_config import *
  11. class AiStrategyRequestParse(tornado.web.RequestHandler):
  12. def initialize(self, logger):
  13. self.logger = logger
  14. def post(self):
  15. data = self.request.body
  16. data = str(data, 'utf8')
  17. data = json.loads(data, encoding='utf8')
  18. self.logger.info("*************************************** NEW REQUEST ***************************************")
  19. self.logger.info("raw data from request is %s" % data)
  20. # 1、新增广告组操作
  21. try:
  22. if data["operation_type"] == 1:
  23. inst = ParseAddCampaignOrAddGroupRequest(data,self.logger)
  24. inst.get_advertiser_strategy_info()
  25. inst.write_intelligence_strategy_table()
  26. if inst.campaign_id == "" or inst.campaign_id is None:
  27. if inst.add_campaign() == -1:
  28. raise Exception('广告计划创建失败')
  29. inst.assemble_group_and_creative_params()
  30. create_group_and_creative_res = inst.res_data
  31. inst.update_intelligence_strategy_table()
  32. self.logger.info("create group and creative request info: %s" % create_group_and_creative_res)
  33. request = requests.post(create_group_and_creative_url,
  34. headers=headers,
  35. data=json.JSONEncoder().encode(create_group_and_creative_res)
  36. )
  37. create_group_and_creative_response_data = json.loads(request.text)
  38. self.logger.info("请求返回:%s" % create_group_and_creative_response_data)
  39. self.write(json.dumps({'account_id': inst.account_id,
  40. 'ai_strategy_id': inst.ai_strategy_uuid,
  41. 'message': create_group_and_creative_response_data}))
  42. self.flush()
  43. else:
  44. self.write(json.dumps({"message": "operation_type 不支持"}))
  45. self.flush()
  46. except Exception:
  47. self.logger.error(traceback.format_exc())
  48. self.write(json.dumps(traceback.format_exc()))
  49. self.flush()
  50. # 该类解析的是 手动请求的新增广告计划或者新增广告组的数据
  51. class ParseAddCampaignOrAddGroupRequest(object):
  52. def __init__(self, request_data, logger):
  53. self.ai_strategy_uuid = str(uuid.uuid4())
  54. self.request_data = request_data
  55. self.logger = logger
  56. self.video = self.request_data['video']
  57. self.account_id = self.request_data['account_id']
  58. self.campaign_info = self.request_data['campaign_info']
  59. self.campaign_id = self.request_data['campaign_info'].get('campaign_id', None)
  60. self.group_info = self.request_data.get('group_info', None)
  61. self.creative_info = self.request_data.get('creative_info',None)
  62. self.operation_type = self.request_data['operation_type']
  63. self.advertiser_strategy_id = None
  64. self.advertiser_strategy = {}
  65. self.res_data = {}
  66. def get_advertiser_strategy_info(self):
  67. """
  68. 获取指定账户下客户的策略信息
  69. 为后续的写入库表,校验一致性做准备
  70. :return:
  71. """
  72. sql = """
  73. select *
  74. from ctop_ai_kuaishou_advertiser_strategy
  75. where account_id = %d
  76. and status = 1
  77. limit 1
  78. """ % self.account_id
  79. advertiser_strategy_df = pd.read_sql(sql, engine)
  80. self.advertiser_strategy_id = advertiser_strategy_df['id'].values[0]
  81. self.advertiser_strategy = advertiser_strategy_df.T.to_dict()[0]
  82. def write_intelligence_strategy_table(self):
  83. """
  84. 请求信息写入 ctop_ai_kuaishou_intelligence_strategy 表中
  85. :return:
  86. """
  87. intelligence_strategy_dict = \
  88. {
  89. 'ai_strategy_uuid': self.ai_strategy_uuid,
  90. 'advertiser_strategy_id': self.advertiser_strategy_id,
  91. 'account_id': self.account_id,
  92. 'ai_strategy_receive_content': str(self.request_data),
  93. 'ai_strategy_remark': self.request_data['ai_strategy_remark'],
  94. 'create_time': datetime.datetime.now()
  95. }
  96. df = pd.DataFrame.from_dict(intelligence_strategy_dict, orient='index').T
  97. df.to_sql(name="ctop_ai_kuaishou_intelligence_strategy", con=engine, if_exists='append', index=False)
  98. def update_intelligence_strategy_table(self):
  99. """
  100. 拼装之后的json, 更新到 ai_strategy_request_content
  101. :return:
  102. """
  103. db_con = pymysql.connect(host=host,
  104. port=port,
  105. user=user,
  106. password=passwd,
  107. database=db,
  108. charset=charset)
  109. cursor = db_con.cursor()
  110. cursor.execute("""UPDATE ctop_ai_kuaishou_intelligence_strategy
  111. SET ai_strategy_request_content= %s
  112. WHERE ai_strategy_uuid = %s""", (str(self.res_data), self.ai_strategy_uuid))
  113. db_con.commit()
  114. db_con.close()
  115. # 如果手动请求的数据结构中,"campaign" 中的 campaign_id 为空则调用该新增计划方法
  116. def add_campaign(self):
  117. """
  118. 新增广告计划, 创建成功之后写入计划层级操作表中
  119. :return:
  120. """
  121. create_campaign_req_data = \
  122. {'account_id': self.account_id,
  123. 'campaign_name': self.campaign_info['campaign_name'],
  124. 'type': self.advertiser_strategy['campaign_type']}
  125. request = requests.post(url=create_campaign_url,
  126. headers=headers,
  127. data=json.JSONEncoder().encode(create_campaign_req_data))
  128. res_data = json.loads(request.text)
  129. # res_data = {
  130. # "code": 0,
  131. # "data": {
  132. # "account_id": 23212,
  133. # "campaign_id": 30956133,
  134. # "campaign_create_time": "2021-01-13 13:50:16"
  135. # },
  136. # "message": "SUCCESS"}
  137. if res_data['code'] == 0:
  138. self.campaign_id = res_data['data']['campaign_id']
  139. campaign_info_to_db = {
  140. 'campaign_uuid': str(uuid.uuid4()),
  141. 'account_id': self.account_id,
  142. 'ai_strategy_uuid': self.ai_strategy_uuid,
  143. 'campaign_name': self.campaign_info['campaign_name'],
  144. 'campaign_id': self.campaign_id,
  145. 'campaign_type': self.advertiser_strategy['campaign_type'],
  146. 'operation_type': self.operation_type,
  147. 'campaign_create_time': res_data['data']['campaign_create_time'],
  148. 'create_time': datetime.datetime.now()
  149. }
  150. df = pd.DataFrame.from_dict(campaign_info_to_db, orient='index').T
  151. df.to_sql(name="ctop_ai_kuaishou_campaign_level_operation_record",
  152. con=engine,
  153. if_exists='append',
  154. index=False)
  155. else:
  156. self.logger.info("广告计划创建失败:%s, ai_strategy_uuid is %s" %
  157. (res_data['message'], self.ai_strategy_uuid))
  158. return -1
  159. def get_group_basic_params(self):
  160. pass
  161. def get_creative_basic_params(self):
  162. pass
  163. def assemble_group_and_creative_params(self):
  164. """
  165. 拼接组和创意层级参数
  166. 写入数据库
  167. :return:
  168. """
  169. # 1、组层级基本信息
  170. drop_cols = ['id', 'account_id', 'status', 'campaign_type',
  171. 'site_id', 'click_track_url', 'impression_url', 'ad_photo_played_t3s_url',
  172. 'actionbar_click_url',
  173. 'create_time', 'effective_time', 'expiry_time']
  174. group_cols_to_list = ['app_store', 'scene_id', 'region', 'district_ids', 'ages_range', 'device_brand',
  175. 'device_price', 'business_interest', 'fans_star', 'interest_video', 'app_interest',
  176. 'app_ids', 'population', 'exclude_population', 'paid_audience', 'behavior_interest']
  177. basic_group_info = self.advertiser_strategy.copy()
  178. for col in drop_cols:
  179. del basic_group_info[col]
  180. # 3、从请求的信息中更新组层级信息, 如组的名称、测试定向、测试出价等, update客户策略信息
  181. if (self.group_info is not None) and (len(self.group_info) > 0):
  182. for key, value in self.group_info.items():
  183. basic_group_info.update({key: value})
  184. # 发送的请求,这几个字段需要转为 [] 数组
  185. group_info_to_res_data = basic_group_info.copy()
  186. for col in group_cols_to_list:
  187. group_info_to_res_data[col] = eval(group_info_to_res_data[col]) \
  188. if group_info_to_res_data[col] is not None else None
  189. # 写入组层级操作记录数据表基本信息
  190. group_info_to_db = basic_group_info.copy()
  191. group_info_to_db['ai_strategy_uuid'] = self.ai_strategy_uuid
  192. group_info_to_db['account_id'] = self.account_id
  193. group_info_to_db['campaign_id'] = self.campaign_id
  194. group_info_to_db['operation_type'] = self.operation_type
  195. # 2、创意层级基本信息
  196. creative_cols = ['creative_name', 'action_bar_text', 'description', 'short_slogan',
  197. 'sticker_title', 'overlay_type', 'expose_tag', 'new_expose_tag', 'site_id',
  198. 'click_track_url', 'impression_url', 'ad_photo_played_t3s_url', 'actionbar_click_url'
  199. 'creative_category', 'creative_tag']
  200. basic_creative_info = {}
  201. for col in creative_cols:
  202. basic_creative_info[col] = self.advertiser_strategy.get(col)
  203. # 2.1从请求的信息中更新创意层级的信息,如广告语
  204. if (self.creative_info is not None) and (len(self.creative_info) > 0):
  205. for key, value in self.creative_info.items():
  206. basic_creative_info.update({key: value})
  207. # 2.2
  208. creative_info_to_res_data = basic_creative_info.copy()
  209. creative_cols_to_list = ['creative_tag']
  210. for col in creative_cols_to_list:
  211. creative_info_to_res_data[col] = eval(creative_info_to_res_data[col]) \
  212. if creative_info_to_res_data[col] is not None else None
  213. # 2.3 写入创意层级操作记录数据表基本信息
  214. creative_info_to_db = basic_creative_info.copy()
  215. creative_info_to_db['ai_strategy_uuid'] = self.ai_strategy_uuid
  216. creative_info_to_db['account_id'] = self.account_id
  217. creative_info_to_db['campaign_id'] = self.campaign_id
  218. creative_info_to_db['operation_type'] = self.operation_type
  219. # 拼接最终请求数据
  220. self.res_data['group_list'] = []
  221. group_cnt = 1
  222. for video in self.video:
  223. group_uuid = str(uuid.uuid4())
  224. group_name = basic_group_info['group_name'] + '-' + str(group_cnt)
  225. # 写入数据表中组信息
  226. single_group_info_to_db = group_info_to_db.copy()
  227. single_group_info_to_db['group_uuid'] = group_uuid
  228. single_group_info_to_db['group_name'] = group_name
  229. single_group_info_to_db['create_time'] = datetime.datetime.now()
  230. df = pd.DataFrame.from_dict(single_group_info_to_db, orient='index').T
  231. df.to_sql(name="ctop_ai_kuaishou_unit_level_operation_record",
  232. con=engine,
  233. if_exists='append',
  234. index=False)
  235. # 拼装返回json的组信息
  236. single_group_info_to_res_data = group_info_to_res_data.copy()
  237. single_group_info_to_res_data['group_uuid'] = group_uuid
  238. single_group_info_to_res_data['group_name'] = group_name
  239. single_group_info_to_res_data['creative_list'] = []
  240. group_cnt += 1
  241. # 拼装组下面创意的信息
  242. creative_cnt = 1
  243. for image_md5 in video['imageList'][: self.advertiser_strategy['image_cnt']]:
  244. creative_uuid = str(uuid.uuid4())
  245. creative_name = self.creative_info['creative_name'] + '_' + str(creative_cnt)
  246. single_creative_info_to_db = creative_info_to_db.copy()
  247. single_creative_info_to_db['creative_uuid'] = creative_uuid
  248. single_creative_info_to_db['creative_name'] = creative_name
  249. single_creative_info_to_db['photo_id'] = video['photo_id']
  250. single_creative_info_to_db['image_md5'] = image_md5
  251. single_creative_info_to_db['create_time'] = datetime.datetime.now()
  252. df = pd.DataFrame.from_dict(single_creative_info_to_db, orient='index').T
  253. df.to_sql(name="ctop_ai_kuaishou_creative_level_operation_record",
  254. con=engine,
  255. if_exists='append',
  256. index=False)
  257. single_creative_info_to_res_data = creative_info_to_res_data.copy()
  258. single_creative_info_to_res_data['creative_uuid'] = creative_uuid
  259. single_creative_info_to_res_data['creative_name'] = creative_name
  260. single_creative_info_to_res_data['photo_id'] = video['photo_id']
  261. single_creative_info_to_res_data['image_md5'] = image_md5
  262. single_group_info_to_res_data['creative_list'].append(single_creative_info_to_res_data)
  263. creative_cnt += 1
  264. # 组和创意信息添加到最终返回数据 group_list 中
  265. self.res_data['group_list'].append(single_group_info_to_res_data)
  266. self.res_data['account_id'] = self.account_id
  267. self.res_data['campaign_id'] = self.campaign_id
  268. # TODO 修改操作
  269. class ParseModifyRequest(object):
  270. def __init__(self):
  271. pass
  272. # TODO 关停操作
  273. class ParseShutDownRequest(object):
  274. def __init__(self):
  275. pass