def get_trade_id(cls, adx_id, campaign): """ 获取行业Id :return: """ if adx_id not in cls.trade_mapping: conn = bunny_engine.connect() sql = text( "SELECT m.adproductId, t.adxAdproductId " "FROM b_base_adx_adproduct t " "LEFT JOIN b_base_adproduct_mapping m on t.id = m.adxAdproductId " "WHERE t.adxId = :adx_id").bindparams(adx_id=adx_id) result = conn.execute(sql).fetchall() if not result: current_app.logger.debug('[ DB ] category is empty') return {} cls.trade_mapping[adx_id] = {} for row in result: cls.trade_mapping[adx_id][ row['adproductId']] = row['adxAdproductId'] category_id = campaign.categoryId if category_id in cls.trade_mapping[adx_id]: return cls.trade_mapping[adx_id][category_id] else: current_app.logger.error( '[ DB ] Creative(%s) has no tradeId(%s), Campaign categoryId(%s) not in mapping', adx_id, creative.id, category_id) return False
def get_mail_list(): """ 获取邮件发送列表 :return: """ conn = bunny_engine.connect() sql = text("SELECT `id`, `to`, `cc`, `bcc`, `subject`, `body` FROM b_mail WHERE `status` = 1") return conn.execute(sql).fetchall()
def get_domain(cls, company_id): if company_id not in cls.domain_mapping: conn = bunny_engine.connect() sql = text("SELECT id, domain FROM b_company_domain WHERE companyId = :company_id") \ .columns(id=Integer, domain=String).bindparams(company_id=company_id) company_domain = conn.execute(sql).fetchone() cls.domain_mapping[company_id] = company_domain[ 'domain'] if company_domain else None return cls.domain_mapping[company_id]
def sended_mail(mail_id, status=0): """ 将邮件设定为已发送 :param mail_id: :return: """ conn = bunny_engine.connect() sql = text("UPDATE b_mail SET `status` = :status WHERE `id` = :id ").bindparams(status=status, id=mail_id) return conn.execute(sql)
def get_mail_list(): """ 获取邮件发送列表 :return: """ conn = bunny_engine.connect() sql = text( "SELECT `id`, `to`, `cc`, `bcc`, `subject`, `body` FROM b_mail WHERE `status` = 1" ) return conn.execute(sql).fetchall()
def creative_type(cls, adx_id): if adx_id not in cls.creative_type_mapping: conn = bunny_engine.connect() sql = text("SELECT creativeTypeId FROM b_relation_adxCreativeTemplate WHERE adxId = :adx_id")\ .bindparams(adx_id=adx_id) id_list = conn.execute(sql).fetchall() cls.creative_type_mapping[adx_id] = [ row['creativeTypeId'] for row in id_list ] if id_list else [] return cls.creative_type_mapping[adx_id]
def sended_mail(mail_id, status=0): """ 将邮件设定为已发送 :param mail_id: :return: """ conn = bunny_engine.connect() sql = text( "UPDATE b_mail SET `status` = :status WHERE `id` = :id ").bindparams( status=status, id=mail_id) return conn.execute(sql)