ai_strategy_request_func.py 24 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503
  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 pymysql
  8. import requests
  9. from utils.BaseClass import *
  10. from utils.DataBaseConfig import *
  11. from utils.LogConfig import *
  12. from utils.UrlConfig import *
  13. log_handler = TimedRotatingFileHandler("logs/ai_strategy_request.log",
  14. when="midnight", backupCount=100)
  15. log_handler.setFormatter(log_formatter)
  16. logger = logging.getLogger('ai_strategy_request_logger')
  17. logger.addHandler(log_handler)
  18. logger.setLevel(logging.DEBUG)
  19. print('id of ai_strategy_request_logger %s' % id(logger))
  20. def ai_strategy_request_parse(data):
  21. """
  22. 发送创建组和创意的请求
  23. :param data:
  24. :return:
  25. """
  26. if data["operation_type"] == 1:
  27. inst = ParseAddCampaignOrAddGroupRequest(data)
  28. # 1 获取客户策略信息
  29. inst.get_advertiser_strategy_info()
  30. # 2 写入智能策略信息
  31. inst.write_intelligence_strategy_table()
  32. # 3 判断 campaign_id 是否为空,为空则发送创建计划的请求
  33. if inst.campaign_id == "" or inst.campaign_id is None:
  34. if inst.add_campaign() == -1:
  35. return {'code': -2, 'message': '广告计划创建失败!'}
  36. # 4 unit_type "4-自定义" or "7-程序化创意2.0" ,调用对应的拼装参数函数
  37. if data['group_info'].get('unit_type', 4) == 4:
  38. inst.assemble_group_and_creative_params()
  39. if data['group_info'].get('unit_type', 4) == 7:
  40. inst.assemble_group_and_programme_creative_params()
  41. # 5 发送的请求信息更新到数据库表
  42. inst.update_intelligence_strategy_table(request_content=inst.res_data, message=None)
  43. # 6 发送创建组和创意的请求
  44. request = requests.post(create_group_and_creative_url,
  45. headers=headers,
  46. data=json.JSONEncoder().encode(inst.res_data))
  47. response_data = json.loads(request.text)
  48. # 7 接受的返回信息更新到数据库表
  49. inst.update_intelligence_strategy_table(message=response_data)
  50. # 8 写入日志信息
  51. logger.info("策略uuid = %s 的请求返回信息:%s" % (inst.ai_strategy_uuid, response_data))
  52. return {'code': 0, 'message': response_data}
  53. else:
  54. return {'code': -1, 'message': '暂时不支持非新增的操作请求!'}
  55. # 该类解析的是 手动请求的新增广告计划或者新增广告组的数据
  56. class ParseAddCampaignOrAddGroupRequest(object):
  57. def __init__(self, request_data):
  58. self.ai_strategy_uuid = str(uuid.uuid4())
  59. self.request_data = request_data
  60. self.video = self.request_data['video']
  61. self.account_id = self.request_data['account_id']
  62. self.campaign_info = self.request_data['campaign_info']
  63. self.campaign_id = self.request_data['campaign_info'].get('campaign_id', None)
  64. self.group_info = self.request_data.get('group_info', None)
  65. self.creative_info = self.request_data.get('creative_info',None)
  66. self.operation_type = self.request_data['operation_type']
  67. self.advertiser_strategy_id = None
  68. self.advertiser_strategy = {}
  69. self.res_data = {}
  70. def get_advertiser_strategy_info(self):
  71. """
  72. 获取指定账户下客户的策略信息
  73. 为后续的写入库表,校验一致性做准备
  74. :return:
  75. """
  76. sql = """
  77. select *
  78. from ctop_ai_kuaishou_advertiser_strategy
  79. where account_id = %d
  80. and status = 1
  81. limit 1
  82. """ % self.account_id
  83. advertiser_strategy_df = pd.read_sql(sql, engine)
  84. self.advertiser_strategy_id = advertiser_strategy_df['id'].values[0]
  85. self.advertiser_strategy = advertiser_strategy_df.T.to_dict()[0]
  86. def write_intelligence_strategy_table(self):
  87. """
  88. 请求信息写入 ctop_ai_kuaishou_intelligence_strategy 表中
  89. :return:
  90. """
  91. intelligence_strategy_dict = \
  92. {
  93. 'ai_strategy_uuid': self.ai_strategy_uuid,
  94. 'advertiser_strategy_id': self.advertiser_strategy_id,
  95. 'account_id': self.account_id,
  96. 'ai_strategy_receive_content': str(self.request_data),
  97. 'ai_strategy_remark': self.request_data['ai_strategy_remark'],
  98. 'create_time': datetime.datetime.now()
  99. }
  100. df = pd.DataFrame.from_dict(intelligence_strategy_dict, orient='index').T
  101. df.to_sql(name="ctop_ai_kuaishou_intelligence_strategy", con=engine, if_exists='append', index=False)
  102. def update_intelligence_strategy_table(self, request_content=None, message=None):
  103. """
  104. 更新 intelligence_strategy_table
  105. :return:
  106. """
  107. db_con = pymysql.connect(host=host,
  108. port=port,
  109. user=user,
  110. password=passwd,
  111. database=db,
  112. charset=charset)
  113. cursor = db_con.cursor()
  114. if request_content:
  115. cursor.execute("""UPDATE ctop_ai_kuaishou_intelligence_strategy
  116. SET ai_strategy_request_content= %s
  117. WHERE ai_strategy_uuid = %s""", (str(request_content), self.ai_strategy_uuid))
  118. if message:
  119. cursor.execute("""UPDATE ctop_ai_kuaishou_intelligence_strategy
  120. SET message = %s
  121. WHERE ai_strategy_uuid = %s""", (str(message), self.ai_strategy_uuid))
  122. db_con.commit()
  123. db_con.close()
  124. def add_campaign(self):
  125. """
  126. 新增广告计划, 创建成功之后写入计划层级操作表中
  127. :return:
  128. """
  129. create_campaign_req_data = \
  130. {'account_id': self.account_id,
  131. 'campaign_name': self.campaign_info['campaign_name'],
  132. 'type': self.advertiser_strategy['campaign_type']}
  133. request = requests.post(url=create_campaign_url,
  134. headers=headers,
  135. data=json.JSONEncoder().encode(create_campaign_req_data))
  136. res_data = json.loads(request.text)
  137. # res_data = {
  138. # "code": 0,
  139. # "data": {
  140. # "account_id": 23212,
  141. # "campaign_id": 30956133,
  142. # "campaign_create_time": "2021-01-13 13:50:16"
  143. # },
  144. # "message": "SUCCESS"}
  145. campaign_info_to_db = {
  146. 'campaign_uuid': str(uuid.uuid4()),
  147. 'account_id': self.account_id,
  148. 'ai_strategy_uuid': self.ai_strategy_uuid,
  149. 'campaign_name': self.campaign_info['campaign_name'],
  150. 'campaign_type': self.advertiser_strategy['campaign_type'],
  151. 'operation_type': self.operation_type,
  152. 'status': 1,
  153. 'message': None,
  154. 'create_time': datetime.datetime.now()
  155. }
  156. res = 0
  157. if res_data['code'] == 0:
  158. self.campaign_id = res_data['data']['campaign_id']
  159. campaign_info_to_db['campaign_id'] = self.campaign_id
  160. campaign_info_to_db['campaign_create_time'] = res_data['data']['campaign_create_time']
  161. campaign_info_to_db['message'] = res_data['message']
  162. logger.info("广告计划创建成功,campaign_id = %s, ai_strategy_uuid = %s" %
  163. (self.campaign_id, self.ai_strategy_uuid))
  164. else:
  165. campaign_info_to_db['message'] = res_data['message']
  166. campaign_info_to_db['status'] = -1
  167. logger.error("广告计划创建失败:%s, ai_strategy_uuid = %s" % (res_data['message'], self.ai_strategy_uuid))
  168. res = -1
  169. # 写入计划层级的操作表
  170. df = pd.DataFrame.from_dict(campaign_info_to_db, orient='index').T
  171. df.to_sql(name="ctop_ai_kuaishou_campaign_level_operation_record",
  172. con=engine,
  173. if_exists='append',
  174. index=False)
  175. return res
  176. def get_group_params(self):
  177. group_params = self.advertiser_strategy.copy()
  178. # 1 从客户策略表中,剔除掉不是组层级的参数(客户基本信息、计划层级信息、创意层级信息)
  179. drop_cols = ['id', 'account_id', 'status', 'campaign_type', 'campaign_name',
  180. 'creative_name', 'action_bar_text', 'description', 'short_slogan',
  181. 'sticker_title', 'overlay_type', 'expose_tag', 'new_expose_tag', 'site_id',
  182. 'click_track_url', 'impression_url', 'ad_photo_played_t3s_url', 'actionbar_click_url',
  183. 'creative_category', 'creative_tag', 'image_cnt',
  184. 'create_time', 'effective_time', 'expiry_time']
  185. for col in drop_cols:
  186. del group_params[col]
  187. # 2 从请求的信息中更新组层级信息, 如组的名称、测试定向、测试出价等, update客户策略信息
  188. if (self.group_info is not None) and (len(self.group_info) > 0):
  189. for key, value in self.group_info.items():
  190. group_params.update({key: value})
  191. # 3 拼装json的组参数
  192. group_params_to_request = group_params.copy()
  193. # 3-1 拼接发送请求json时, 以下字段需要转为 list 类型
  194. cols_to_list = ['app_store', 'scene_id', 'region', 'district_ids', 'ages_range', 'device_brand',
  195. 'device_price', 'business_interest', 'fans_star', 'interest_video', 'app_interest',
  196. 'app_ids', 'population', 'exclude_population', 'paid_audience', 'behavior_interest']
  197. for col in cols_to_list:
  198. group_params_to_request[col] = eval(group_params_to_request[col]) \
  199. if group_params_to_request[col] is not None else None
  200. # 3-2 发送请求,需要将 group_name 改为 unit_name
  201. group_params_to_request['unit_name'] = group_params_to_request.pop('group_name')
  202. # 4 写入数据库的组参数
  203. group_params_to_db = group_params.copy()
  204. group_params_to_db['ai_strategy_uuid'] = self.ai_strategy_uuid
  205. group_params_to_db['account_id'] = self.account_id
  206. group_params_to_db['campaign_id'] = self.campaign_id
  207. group_params_to_db['operation_type'] = self.operation_type
  208. group_params_to_db['status'] = 1
  209. group_params_to_db['message'] = None
  210. return group_params_to_request, group_params_to_db
  211. def get_creative_params(self):
  212. # 1 从客户策略表中获取 创意层级基本信息
  213. creative_cols = ['creative_name', 'action_bar_text', 'description', 'short_slogan',
  214. 'sticker_title', 'overlay_type', 'expose_tag', 'new_expose_tag', 'site_id',
  215. 'click_track_url', 'impression_url', 'ad_photo_played_t3s_url', 'actionbar_click_url',
  216. 'creative_category', 'creative_tag']
  217. creative_params = {}
  218. for col in creative_cols:
  219. creative_params[col] = self.advertiser_strategy.get(col)
  220. # 2 从请求的信息中更新创意层级的信息,如广告语
  221. if (self.creative_info is not None) and (len(self.creative_info) > 0):
  222. for key, value in self.creative_info.items():
  223. creative_params.update({key: value})
  224. # 3 拼装json的 创意参数
  225. creative_params_to_request = creative_params.copy()
  226. # 3-1 拼接发送请求json时, 以下字段需要转为 list 类型
  227. cols_to_list = ['creative_tag']
  228. for col in cols_to_list:
  229. creative_params_to_request[col] = eval(creative_params_to_request[col]) \
  230. if creative_params_to_request[col] is not None else None
  231. # 4 写入数据库的创意参数
  232. creative_params_to_db = creative_params.copy()
  233. creative_params_to_db['ai_strategy_uuid'] = self.ai_strategy_uuid
  234. creative_params_to_db['account_id'] = self.account_id
  235. creative_params_to_db['campaign_id'] = self.campaign_id
  236. creative_params_to_db['operation_type'] = self.operation_type
  237. creative_params_to_db['status'] = 1
  238. creative_params_to_db['message'] = None
  239. return creative_params_to_request, creative_params_to_db
  240. def get_programme_creative_params(self):
  241. # 1 从客户策略表中获取 创意层级基本信息
  242. creative_cols = ['creative_name', 'action_bar_text', 'description', 'site_id',
  243. 'click_track_url', 'actionbar_click_url',
  244. 'creative_category', 'creative_tag']
  245. creative_params = {}
  246. for col in creative_cols:
  247. creative_params[col] = self.advertiser_strategy.get(col)
  248. # 2 从请求的信息中更新创意层级的信息,如广告语,创意名称等
  249. if (self.creative_info is not None) and (len(self.creative_info) > 0):
  250. for key, value in self.creative_info.items():
  251. creative_params.update({key: value})
  252. # 3 程序化创意部分字段发生改变,需要重命名
  253. # creative_name --> package_name(程序化创意名称)
  254. # action_bar_text --> action_bar(行动号召按钮)
  255. # description --> captions(作品广告语)
  256. # click_track_url --> click_url(点击监测链接)
  257. creative_params['package_name'] = creative_params.pop('creative_name')
  258. creative_params['action_bar'] = creative_params.pop('action_bar_text')
  259. creative_params['captions'] = creative_params.pop('description')
  260. creative_params['click_url'] = creative_params.pop('click_track_url')
  261. # 4 拼装json的 创意参数
  262. creative_params_to_request = creative_params.copy()
  263. # 4-1 拼接发送请求json时, 以下字段需要转为 list 类型
  264. cols_to_list = ['creative_tag']
  265. for col in cols_to_list:
  266. creative_params_to_request[col] = eval(creative_params_to_request[col]) \
  267. if creative_params_to_request[col] is not None else None
  268. # 5 写入数据库的创意参数
  269. creative_params_to_db = creative_params.copy()
  270. creative_params_to_db['ai_strategy_uuid'] = self.ai_strategy_uuid
  271. creative_params_to_db['account_id'] = self.account_id
  272. creative_params_to_db['campaign_id'] = self.campaign_id
  273. creative_params_to_db['operation_type'] = self.operation_type
  274. creative_params_to_db['status'] = 1
  275. creative_params_to_db['message'] = None
  276. return creative_params_to_request, creative_params_to_db
  277. def assemble_group_and_creative_params(self):
  278. """
  279. 拼接组和创意层级参数
  280. 写入数据库
  281. :return:
  282. """
  283. # 获取组和创意的基本参数
  284. group_params_to_request, group_params_to_db = self.get_group_params()
  285. creative_params_to_request, creative_params_to_db = self.get_creative_params()
  286. self.res_data['group_list'] = []
  287. group_cnt = 1
  288. for video in self.video:
  289. group_uuid = str(uuid.uuid4())
  290. group_name = group_params_to_db['group_name'] + '-' + str(group_cnt)
  291. # 写入数据表中组信息
  292. single_group_params_to_db = group_params_to_db.copy()
  293. single_group_params_to_db['group_uuid'] = group_uuid
  294. single_group_params_to_db['group_name'] = group_name
  295. single_group_params_to_db['create_time'] = datetime.datetime.now()
  296. df = pd.DataFrame.from_dict(single_group_params_to_db, orient='index').T
  297. df.to_sql(name="ctop_ai_kuaishou_unit_level_operation_record",
  298. con=engine,
  299. if_exists='append',
  300. index=False)
  301. # 拼装返回json的组信息
  302. single_group_params_to_request = group_params_to_request.copy()
  303. single_group_params_to_request['group_uuid'] = group_uuid
  304. single_group_params_to_request['unit_name'] = group_name
  305. single_group_params_to_request['creative_list'] = []
  306. group_cnt += 1
  307. # 拼装组下面创意的信息
  308. creative_cnt = 1
  309. for image_md5 in video['imageList'][: self.advertiser_strategy['image_cnt']]:
  310. creative_uuid = str(uuid.uuid4())
  311. creative_name = creative_params_to_db['creative_name'] + '_' + str(creative_cnt)
  312. single_creative_params_to_db = creative_params_to_db.copy()
  313. single_creative_params_to_db['creative_uuid'] = creative_uuid
  314. single_creative_params_to_db['creative_name'] = creative_name
  315. single_creative_params_to_db['photo_id'] = video['photo_id']
  316. single_creative_params_to_db['image_md5'] = image_md5
  317. single_creative_params_to_db['create_time'] = datetime.datetime.now()
  318. df = pd.DataFrame.from_dict(single_creative_params_to_db, orient='index').T
  319. df.to_sql(name="ctop_ai_kuaishou_creative_level_operation_record",
  320. con=engine,
  321. if_exists='append',
  322. index=False)
  323. single_creative_params_to_request = creative_params_to_request.copy()
  324. single_creative_params_to_request['creative_uuid'] = creative_uuid
  325. single_creative_params_to_request['creative_name'] = creative_name
  326. single_creative_params_to_request['photo_id'] = video['photo_id']
  327. single_creative_params_to_request['image_md5'] = image_md5
  328. # 单个创意信息加入到组里面的 creative_list 中
  329. single_group_params_to_request['creative_list'].append(single_creative_params_to_request)
  330. creative_cnt += 1
  331. # 组和创意信息添加到最终返回数据 group_list 中
  332. self.res_data['group_list'].append(single_group_params_to_request)
  333. self.res_data['account_id'] = self.account_id
  334. self.res_data['campaign_id'] = self.campaign_id
  335. def assemble_group_and_programme_creative_params(self):
  336. """
  337. 拼接组和程序化创意的参数
  338. 写入数据库
  339. :return:
  340. """
  341. # 获取组和创意的基本参数
  342. group_params_to_request, group_params_to_db = self.get_group_params()
  343. creative_params_to_request, creative_params_to_db = self.get_programme_creative_params()
  344. # 每5个视频一组,来拼接组层级 和 程序化创意 层级的参数
  345. self.res_data['group_list'] = []
  346. cnt = 1
  347. for i in range(0, len(self.video), 5):
  348. group_uuid = str(uuid.uuid4())
  349. group_name = group_params_to_db['group_name'] + '-' + str(cnt)
  350. # 写入数据表中组信息
  351. single_group_params_to_db = group_params_to_db.copy()
  352. single_group_params_to_db['group_uuid'] = group_uuid
  353. single_group_params_to_db['group_name'] = group_name
  354. single_group_params_to_db['create_time'] = datetime.datetime.now()
  355. df = pd.DataFrame.from_dict(single_group_params_to_db, orient='index').T
  356. df.to_sql(name="ctop_ai_kuaishou_unit_level_operation_record",
  357. con=engine,
  358. if_exists='append',
  359. index=False)
  360. # 拼装返回json的组信息
  361. single_group_params_to_request = group_params_to_request.copy()
  362. single_group_params_to_request['group_uuid'] = group_uuid
  363. single_group_params_to_request['unit_name'] = group_name
  364. single_group_params_to_request['creative_list'] = []
  365. single_group_params_to_request['programCreative'] = None
  366. # 拼装程序化创意的信息
  367. horizontal_photo_ids = []
  368. vertical_photo_ids = []
  369. cover_image_tokens = []
  370. if i+5 < len(self.video):
  371. for j in range(i, i+5, 1):
  372. if self.video[j]['material_type'] == 1:
  373. horizontal_photo_ids.append(self.video[j]['photo_id'])
  374. cover_image_tokens.append(self.video[j]['imageList'][0])
  375. if self.video[j]['material_type'] == 2:
  376. vertical_photo_ids.append(self.video[j]['photo_id'])
  377. cover_image_tokens.append(self.video[j]['imageList'][0])
  378. if len(cover_image_tokens) >= 5:
  379. cover_image_tokens = cover_image_tokens[:4]
  380. creative_uuid = str(uuid.uuid4())
  381. package_name = creative_params_to_db['package_name'] + '_' + str(cnt)
  382. single_creative_params_to_db = creative_params_to_db.copy()
  383. single_creative_params_to_db['creative_uuid'] = creative_uuid
  384. single_creative_params_to_db['package_name'] = package_name
  385. single_creative_params_to_db['horizontal_photo_ids'] = horizontal_photo_ids
  386. single_creative_params_to_db['vertical_photo_ids'] = vertical_photo_ids
  387. single_creative_params_to_db['cover_image_tokens'] = cover_image_tokens
  388. single_creative_params_to_db['create_time'] = datetime.datetime.now()
  389. df = pd.DataFrame.from_dict(single_creative_params_to_db, orient='index').T
  390. df.to_sql(name="ctop_ai_kuaishou_program_creative_level_operation_record",
  391. con=engine,
  392. if_exists='append',
  393. index=False)
  394. single_creative_params_to_request = creative_params_to_request.copy()
  395. single_creative_params_to_request['creative_uuid'] = creative_uuid
  396. single_creative_params_to_request['package_name'] = package_name
  397. single_creative_params_to_request['horizontal_photo_ids'] = horizontal_photo_ids
  398. single_creative_params_to_request['vertical_photo_ids'] = vertical_photo_ids
  399. single_creative_params_to_request['image_md5s'] = cover_image_tokens
  400. else:
  401. for j in range(i, len(self.video)):
  402. if self.video[j]['material_type'] == 1:
  403. horizontal_photo_ids.append(self.video[j]['photo_id'])
  404. cover_image_tokens.append(self.video[j]['imageList'][0])
  405. if self.video[j]['material_type'] == 2:
  406. vertical_photo_ids.append(self.video[j]['photo_id'])
  407. cover_image_tokens.append(self.video[j]['imageList'][0])
  408. if len(cover_image_tokens) >= 5:
  409. cover_image_tokens = cover_image_tokens[:4]
  410. creative_uuid = str(uuid.uuid4())
  411. package_name = creative_params_to_db['package_name'] + '_' + str(cnt)
  412. single_creative_params_to_db = creative_params_to_db.copy()
  413. single_creative_params_to_db['creative_uuid'] = creative_uuid
  414. single_creative_params_to_db['package_name'] = package_name
  415. single_creative_params_to_db['horizontal_photo_ids'] = horizontal_photo_ids
  416. single_creative_params_to_db['vertical_photo_ids'] = vertical_photo_ids
  417. single_creative_params_to_db['cover_image_tokens'] = cover_image_tokens
  418. single_creative_params_to_db['create_time'] = datetime.datetime.now()
  419. df = pd.DataFrame.from_dict(single_creative_params_to_db, orient='index').T
  420. df.to_sql(name="ctop_ai_kuaishou_program_creative_level_operation_record",
  421. con=engine,
  422. if_exists='append',
  423. index=False)
  424. single_creative_params_to_request = creative_params_to_request.copy()
  425. single_creative_params_to_request['creative_uuid'] = creative_uuid
  426. single_creative_params_to_request['package_name'] = package_name
  427. single_creative_params_to_request['horizontal_photo_ids'] = horizontal_photo_ids
  428. single_creative_params_to_request['vertical_photo_ids'] = vertical_photo_ids
  429. single_creative_params_to_request['image_md5s'] = cover_image_tokens
  430. single_group_params_to_request['programCreative'] = single_creative_params_to_request
  431. cnt += 1
  432. self.res_data['group_list'].append(single_group_params_to_request)
  433. # TODO 修改操作
  434. class ParseModifyRequest(object):
  435. def __init__(self):
  436. pass
  437. # TODO 关停操作
  438. class ParseShutDownRequest(object):
  439. def __init__(self):
  440. pass