123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193 |
- import datetime
- import logging
- import traceback
- import pandas as pd
- import pymysql
- from concurrent_log import ConcurrentTimedRotatingFileHandler
- from utils.BaseClass import *
- from utils.CommonFunction import get_lower_case_name
- from utils.DataBaseConfig import *
- log_formatter = logging.Formatter('%(asctime)s %(levelname)s %(message)s', '%m/%d/%Y %I:%M:%S %p')
- log_handler = ConcurrentTimedRotatingFileHandler("logs/advertiser_strategy_operate.log",
- when="midnight", backupCount=100, encoding='utf-8')
- log_handler.setFormatter(log_formatter)
- logger = logging.getLogger('advertiser_strategy_operate_logger')
- logger.addHandler(log_handler)
- logger.setLevel(logging.DEBUG)
- print('id of advertiser_strategy_operate_logger %s' % id(logger))
- class AddAdvertiserStrategy(BaseHandler):
- """
- 新增客户策略
- """
- def post(self):
- data = self.request.body
- logger.info("raw data from request is %s" % data)
- data = str(data, 'utf8')
- data = json.loads(data, encoding='utf8')
- # TODO customCrowd 是否不需要了
- # "111" 字符串类型 ,可以写入到整型的字段中
- # 整数类型 ,可以写入到字符串类型的字段中
- # 数组类型, 需要转化为字符串类型 然后写入字符串类型的字段中
- logger.info("*************************************** NEW REQUEST ***************************************")
- logger.info("raw data from request is %s" % data)
- drop_cols = ['customCrowd']
- for col in drop_cols:
- del data[col]
- # 1-1 判断 account_id 是否存在可用策略,如果存在则不能新增
- try:
- account_id = data.get('accountId')
- sql = """
- select * from ctop_ai_kuaishou_advertiser_strategy where account_id = %s and status = 1
- """ % account_id
- df = pd.read_sql(sql, engine)
- if not df.empty:
- self.write(json.dumps({"message": "该账户已经存在有效策略!", "code": -1}))
- self.flush()
- else:
- data['campaignType'] = data['marketingGoal']
- del data['marketingGoal']
- if not data['agesRange']:
- del data['agesRange']
- transfer_data = {}
- for key, value in data.items():
- transfer_key = get_lower_case_name(key)
- transfer_data[transfer_key] = str(value) if isinstance(value, list) else value
- df = pd.DataFrame.from_dict(transfer_data, orient='index').T
- df.to_sql(name="ctop_ai_kuaishou_advertiser_strategy", con=engine, if_exists='append', index=False)
- logger.info("策略添加成功!")
- self.write(json.dumps({"message": "策略添加成功!", "code":0}))
- self.flush()
- except:
- logger.error(traceback.format_exc())
- self.write(json.dumps({"message": traceback.format_exc(), "code": -2}))
- self.flush()
- class ModifyAdvertiserStrategy(tornado.web.RequestHandler):
- """
- 修改客户策略
- """
- def post(self):
- data = self.request.body
- data = str(data, 'utf8')
- data = json.loads(data, encoding='utf8')
- logger.info("*************************************** NEW REQUEST ***************************************")
- logger.info("raw data from request is %s" % data)
- try:
- transfer_data = {}
- for key, value in data.items():
- transfer_key = get_lower_case_name(key)
- transfer_data[transfer_key] = str(value) if isinstance(value, list) else value
- # 1-1 将已有的策略更新为无效,且更新该策略的失效时间
- db_con = pymysql.connect(host=host,
- port=port,
- user=user,
- password=passwd,
- database=db,
- charset=charset)
- cursor = db_con.cursor()
- cursor.execute("""UPDATE ctop_ai_kuaishou_advertiser_strategy
- SET status= %s, expiry_time=%s
- WHERE account_id = %s""", (0, datetime.datetime.now(), transfer_data['account_id']))
- db_con.commit()
- db_con.close()
-
- # 1-2 插入修改完成的策略
- df = pd.DataFrame.from_dict(transfer_data, orient='index').T
- df.to_sql(name="ctop_ai_kuaishou_advertiser_strategy", con=engine, if_exists='append', index=False)
- logger.info("策略修改成功!")
- self.write(json.dumps({"message": "策略修改成功!", "code": 0}))
- self.flush()
- except:
- logger.error(traceback.format_exc())
- self.write(json.dumps({"message": traceback.format_exc(), "code": -2}))
- self.flush()
- class QueryAdvertiserStrategy(tornado.web.RequestHandler):
- """
- 查询客户策略
- """
- def post(self):
- data = self.request.body
- data = str(data, 'utf8')
- data = json.loads(data, encoding='utf8')
- logger.info("*************************************** NEW REQUEST ***************************************")
- logger.info("raw data from request is %s" % data)
- account_id = data.get('accountId', 'null')
- # project_id = data.get('projectId', 'null')
- sql = """
- select * from ctop_ai_kuaishou_advertiser_strategy
- where (1 = (CASE WHEN '%s' = 'null' THEN 1 ELSE 0 END) OR account_id = %s)
- and status = 1
- """ % (account_id, account_id)
- df = pd.read_sql(sql, engine)
- if df.empty:
- logger.info("没有查询到策略!")
- self.write(json.dumps({"message": "没有查询到策略!", "code": -1}))
- self.flush()
- else:
- res = []
- for index, row in df.iterrows():
- res.append(row.to_dict())
- logger.info("策略查询完成!")
- self.write(json.dumps(res))
- self.flush()
- class DeleteAdvertiserStrategy(tornado.web.RequestHandler):
- """
- 删除客户策略,把已有的策略状态更新为 "0-停用", 并把失效时间更新为now()
- """
- def post(self):
- data = self.request.body
- data = str(data, 'utf8')
- data = json.loads(data, encoding='utf8')
- logger.info("*************************************** NEW REQUEST ***************************************")
- logger.info("raw data from request is %s" % data)
- try:
- db_con = pymysql.connect(host=host,
- port=port,
- user=user,
- password=passwd,
- database=db,
- charset=charset)
- cursor = db_con.cursor()
- cursor.execute("""UPDATE ctop_ai_kuaishou_advertiser_strategy
- SET status= %s, expiry_time=%s
- WHERE account_id = %s""",
- (0, datetime.datetime.now(), data['accountId']))
- db_con.commit()
- db_con.close()
- logger.info("策略删除(失效)成功!")
- self.write(json.dumps({"message": "策略删除成功!", "code": 0}))
- self.flush()
- except Exception:
- logger.error(traceback.format_exc())
- self.write(json.dumps({"message": traceback.format_exc(), "code": -2}))
- self.flush()
|