advertiser_strategy_operate.py 7.5 KB

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