advertiser_strategy_operate.py 7.7 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194
  1. import datetime
  2. import json
  3. import traceback
  4. from logging.handlers import TimedRotatingFileHandler
  5. from concurrent_log import ConcurrentTimedRotatingFileHandler
  6. import pymysql
  7. from utils.CommonFunction import get_lower_case_name
  8. import pandas as pd
  9. from utils.BaseClass import *
  10. from utils.DataBaseConfig import *
  11. import logging
  12. log_formatter = logging.Formatter('%(asctime)s %(levelname)s %(message)s', '%m/%d/%Y %I:%M:%S %p')
  13. log_handler = ConcurrentTimedRotatingFileHandler("logs/advertiser_strategy_operate.log",
  14. when="midnight", backupCount=100, encoding='utf-8')
  15. log_handler.setFormatter(log_formatter)
  16. logger = logging.getLogger('advertiser_strategy_operate_logger')
  17. logger.addHandler(log_handler)
  18. logger.setLevel(logging.DEBUG)
  19. print('id of advertiser_strategy_operate_logger %s' % id(logger))
  20. class AddAdvertiserStrategy(BaseHandler):
  21. """
  22. 新增客户策略
  23. """
  24. def post(self):
  25. data = self.request.body
  26. logger.info("raw data from request is %s" % data)
  27. data = str(data, 'utf8')
  28. data = json.loads(data, encoding='utf8')
  29. # TODO customCrowd 是否不需要了
  30. # "111" 字符串类型 ,可以写入到整型的字段中
  31. # 整数类型 ,可以写入到字符串类型的字段中
  32. # 数组类型, 需要转化为字符串类型 然后写入字符串类型的字段中
  33. logger.info("*************************************** NEW REQUEST ***************************************")
  34. logger.info("raw data from request is %s" % data)
  35. drop_cols = ['customCrowd']
  36. for col in drop_cols:
  37. del data[col]
  38. # 1-1 判断 account_id 是否存在可用策略,如果存在则不能新增
  39. try:
  40. account_id = data.get('accountId')
  41. sql = """
  42. select * from ctop_ai_kuaishou_advertiser_strategy where account_id = %s and status = 1
  43. """ % account_id
  44. df = pd.read_sql(sql, engine)
  45. if not df.empty:
  46. self.write(json.dumps({"message": "该账户已经存在有效策略!", "code": -1}))
  47. self.flush()
  48. else:
  49. data['campaignType'] = data['marketingGoal']
  50. del data['marketingGoal']
  51. if not data['agesRange']:
  52. del data['agesRange']
  53. transfer_data = {}
  54. for key, value in data.items():
  55. transfer_key = get_lower_case_name(key)
  56. transfer_data[transfer_key] = str(value) if isinstance(value, list) else value
  57. df = pd.DataFrame.from_dict(transfer_data, orient='index').T
  58. df.to_sql(name="ctop_ai_kuaishou_advertiser_strategy", con=engine, if_exists='append', index=False)
  59. logger.info("策略添加成功!")
  60. self.write(json.dumps({"message": "策略添加成功!", "code":0}))
  61. self.flush()
  62. except:
  63. logger.error(traceback.format_exc())
  64. self.write(json.dumps({"message": traceback.format_exc(), "code": -2}))
  65. self.flush()
  66. class ModifyAdvertiserStrategy(tornado.web.RequestHandler):
  67. """
  68. 修改客户策略
  69. """
  70. def post(self):
  71. data = self.request.body
  72. data = str(data, 'utf8')
  73. data = json.loads(data, encoding='utf8')
  74. logger.info("*************************************** NEW REQUEST ***************************************")
  75. logger.info("raw data from request is %s" % data)
  76. try:
  77. transfer_data = {}
  78. for key, value in data.items():
  79. transfer_key = get_lower_case_name(key)
  80. transfer_data[transfer_key] = str(value) if isinstance(value, list) else value
  81. # 1-1 将已有的策略更新为无效,且更新该策略的失效时间
  82. db_con = pymysql.connect(host=host,
  83. port=port,
  84. user=user,
  85. password=passwd,
  86. database=db,
  87. charset=charset)
  88. cursor = db_con.cursor()
  89. cursor.execute("""UPDATE ctop_ai_kuaishou_advertiser_strategy
  90. SET status= %s, expiry_time=%s
  91. WHERE account_id = %s""", (0, datetime.datetime.now(), transfer_data['account_id']))
  92. db_con.commit()
  93. db_con.close()
  94. # 1-2 插入修改完成的策略
  95. df = pd.DataFrame.from_dict(transfer_data, orient='index').T
  96. df.to_sql(name="ctop_ai_kuaishou_advertiser_strategy", con=engine, if_exists='append', index=False)
  97. logger.info("策略修改成功!")
  98. self.write(json.dumps({"message": "策略修改成功!", "code": 0}))
  99. self.flush()
  100. except:
  101. logger.error(traceback.format_exc())
  102. self.write(json.dumps({"message": traceback.format_exc(), "code": -2}))
  103. self.flush()
  104. class QueryAdvertiserStrategy(tornado.web.RequestHandler):
  105. """
  106. 查询客户策略
  107. """
  108. def post(self):
  109. data = self.request.body
  110. data = str(data, 'utf8')
  111. data = json.loads(data, encoding='utf8')
  112. logger.info("*************************************** NEW REQUEST ***************************************")
  113. logger.info("raw data from request is %s" % data)
  114. account_id = data.get('accountId', 'null')
  115. # project_id = data.get('projectId', 'null')
  116. sql = """
  117. select * from ctop_ai_kuaishou_advertiser_strategy
  118. where (1 = (CASE WHEN '%s' = 'null' THEN 1 ELSE 0 END) OR account_id = %s)
  119. and status = 1
  120. """ % (account_id, account_id)
  121. df = pd.read_sql(sql, engine)
  122. if df.empty:
  123. logger.info("没有查询到策略!")
  124. self.write(json.dumps({"message": "没有查询到策略!", "code": -1}))
  125. self.flush()
  126. else:
  127. res = []
  128. for index, row in df.iterrows():
  129. res.append(row.to_dict())
  130. logger.info("策略查询完成!")
  131. self.write(json.dumps(res))
  132. self.flush()
  133. class DeleteAdvertiserStrategy(tornado.web.RequestHandler):
  134. """
  135. 删除客户策略,把已有的策略状态更新为 "0-停用", 并把失效时间更新为now()
  136. """
  137. def post(self):
  138. data = self.request.body
  139. data = str(data, 'utf8')
  140. data = json.loads(data, encoding='utf8')
  141. logger.info("*************************************** NEW REQUEST ***************************************")
  142. logger.info("raw data from request is %s" % data)
  143. try:
  144. db_con = pymysql.connect(host=host,
  145. port=port,
  146. user=user,
  147. password=passwd,
  148. database=db,
  149. charset=charset)
  150. cursor = db_con.cursor()
  151. cursor.execute("""UPDATE ctop_ai_kuaishou_advertiser_strategy
  152. SET status= %s, expiry_time=%s
  153. WHERE account_id = %s""",
  154. (0, datetime.datetime.now(), data['accountId']))
  155. db_con.commit()
  156. db_con.close()
  157. logger.info("策略删除(失效)成功!")
  158. self.write(json.dumps({"message": "策略删除成功!", "code": 0}))
  159. self.flush()
  160. except Exception:
  161. logger.error(traceback.format_exc())
  162. self.write(json.dumps({"message": traceback.format_exc(), "code": -2}))
  163. self.flush()