advertiser_strategy_operate.py 7.6 KB

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