advertiser_strategy_operate.py 7.3 KB

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