ai_strategy_request_handler.py 14 KB

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