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()