| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189 | import datetimeimport jsonimport tracebackfrom logging.handlers import TimedRotatingFileHandlerimport pandas as pdfrom utils.BaseClass import *from utils.CommonFunction import *from utils.DataBaseConfig import *from utils.LogConfig import *log_handler = TimedRotatingFileHandler("logs/advertiser_strategy_operate/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()
 |