def set_rule(self): """设置待匹配的字段,规则""" for i in self.rule: sql_init = "INSERT INTO WS_RULE VALUES('%d','%s','%s','%s')" % (self.rule.index(i), i[0], i[0], i[1]) log().debug(sql_init) db_oracle(sql_init)
def search_stop(self): """ 融合后查询数据库,生成字典 :return: """ self.toals = {} for i in self.code: if self.id_code.get(i, False): self.code_element = i WS_OUTSIDE = self._creat_sql( ", id from WS_OUTSIDE where id='%s'" % self.id_code[i]) out = db_oracle(WS_OUTSIDE) text = " %s %s WS_OUTSIDE is %s " % (self.status, i, out) log().debug(text) self.outside = { self.keys[i]: out[0][i] for i in range(0, len(self.keys)) } log().debug("%s WS_OUTSIDE %s" % (i, self.outside)) WS_MERGEDMEDIA = self._creat_sql( ", c2code from WS_MERGEDMEDIA where c2code='%s'" % i) mer = db_oracle(WS_MERGEDMEDIA) self.stop = { self.keys[i]: mer[0][i] for i in range(0, len(self.keys)) } log().debug("%s WS_MERGEDMEDIA %s" % (i, self.stop)) self._data() self.toals[i] = self.toal log().info(self.toals)
def activity(self, s): """更改ws_process表,触发融合操作""" for i in self.code: sql="update WS_PROCESS set PROCESSRESULT='%s' where code='%s'" % (s,i) db_oracle(sql) log().debug([i, db_oracle("select PROCESSRESULT from WS_PROCESS where code='%s' " % i)]) self.status = "stop"
def init(self): """初始化环境""" strcode = str(self.id_list).replace("[", "").replace("]", "") clean_side= "delete WS_OUTSIDE where id in ({0})".format(strcode) db_oracle(clean_side) clean_tail = "delete WS_PROCESSDETAIL where id in ({0})".format(strcode) db_oracle(clean_tail) log().info("clean WS_PROCESSDETAIL and WS_OUTSIDE sucess")
def k(): for x in code: x = x[0] gropu = ["企业", "家庭", "政府"] y = [random.choice(gropu) for i in range(0, random.randrange(1, 4, 1))] g = (",".join(y)) sql2 = "UPDATE CATEGORYMAP SET EPGGROUP='%s' where code='%s'" % (g, x) print(sql2) db_oracle(sql2, url)
def search_c3(self): """查询C3数据""" strcode = str(self.code).replace("[", "").replace("]", "") sql_id = "select ID FROM WS_PROCESSDETAIL where SOURCE='3' and C2CODE in ({0})".format(strcode) resid = db_oracle(sql_id) id_list=[i[0] for i in resid] strid_list = str(id_list).replace("[", "").replace("]", "") sql_outside="SELECT count(*) from WS_OUTSIDE where ID in ({0})".format(strid_list) res_side=db_oracle(sql=sql_outside) self.id_list=id_list self.side=res_side[0][0] log().info("%s WS_PROCESSDETAIL ID have %s, WS_OUTSIDE ID have %s " % (self.status ,self.id_list, self.side))
def check_c2(self): """ 环境校验 :return: """ strcode = str(self.code).replace("[", "").replace("]", "") sql = "select count(*) from WS_MERGEDMEDIA where c2code in ({0})".format(strcode) num_MERGEDMEDIA_ = db_oracle(sql)[0][0] if num_MERGEDMEDIA_ != len(self.code): raise AssertionError("not found %s in WS_MERGEDMEDIA " % self.code) sql = "select count(*) from WS_PROCESS where code in ({0})".format(strcode) num_WS_PROCESS_ = db_oracle(sql)[0][0] if num_WS_PROCESS_ != len(self.code): raise AssertionError("not found %s in WS_PROCESS " % self.code)
def get_rules(self): """获取待匹配字段,以及规则""" sql = "SELECT PROPERTY, RULE from WS_RULE" getrule = db_oracle(sql) log().debug("get rule is %s send is %s" % (getrule, self.rule)) if set(getrule) == set(self.rule): self.rule = {x: y for x, y in getrule if x in self.keys} self.keys = [i for i in self.rule.keys()] log().debug(self.rule) elif set(getrule) != set(self.rule): log().error("ws_rule getrul != rule test end") raise AssertionError("ws_rule getrul != rule test end")
def initialize(self): """ 初始化 1、清空日志 2、初始化媒资数据 3、初始化匹配规则表 """ #1、获取最初的融合的id LS().idpcs_get_id("start") #2、初始化c2数据 for i in self.code: sql = "update WS_MERGEDMEDIA set YEAR='{0}', DIRECTORS='{1}', " \ "CASTS='{2}', TITLE='{3}', RATING='{4}',GENRES='{5}', WRITERS='{6}', LANGUAGES='{7}' where c2code='%s'".format( *self.value) % i db_oracle(sql) log().debug("初始化 WS_MERGEDMEDIA表,sql: %s" % sql) #3、初始化ws_rule sql = "DELETE from WS_RULE" db_oracle(sql) log().debug("clean ws_rule end :sql='%s'" % sql)
def search_start(self): """融合前查询媒资表""" result={} for i in self.code: WS_MERGEDMEDIA = self._creat_sql(", c2code from WS_MERGEDMEDIA where c2code='%s'" % i) log().debug(WS_MERGEDMEDIA) mer = db_oracle(WS_MERGEDMEDIA) result[i] = {} result[i] = {self.keys[i]: mer[0][i] for i in range(0, len(self.keys)) if mer} if not result[i]: raise AssertionError("code %s not data in table WS_MERGEDMEDIA" % i) log().debug("%s---start--- WS_MERGEDMEDIA %s" % (i, result)) exec("self.start=result" )
def getc3_wait(self,timeout=10): """等待爬取完毕,每条等待时间为 timeout*3""" strcode = str(self.code).replace("[", "").replace("]", "") number = 0 sql = "select count(*) FROM WS_PROCESSDETAIL where SOURCE='3' and C2CODE in ({0})".format(strcode) while True: rescount = db_oracle(sql)[0][0] log().debug("get over is %s" % rescount) if rescount >= len(self.code): break elif number == timeout*3*len(self.code): raise AssertionError("超时未获取C3数据 测试结束") number += 1 time.sleep(1)
def merge_wait(self , timeout=20): strcode = str(self.code).replace("[", "").replace("]", "") number=0 sql = "SELECT count(*) from WS_PROCESS where PROCESSRESULT='2' and CODE in ({0})".format(strcode) log().debug(sql) while True: rescount=db_oracle(sql)[0][0] log().debug("merge over is %s" % rescount) if rescount == len(self.code): break elif number == timeout: raise AssertionError("timeout = %s 超时未融合完毕 测试结束" % timeout) number += 1 time.sleep(1)
def get_id(self): """获取中间表id""" if self.status == "stop": log().debug("开获取融合ID") memgr_id = LS.idpcs_get_id("end") if memgr_id != len(self.code): raise AssertionError("融合的C3数据条数为 %s,少于总媒资数据条数 %s" % (memgr_id, len(self.code))) log().debug("id_code : %s" % memgr_id) self.id_code = {} for i in memgr_id: sql = "select c2code, id from WS_PROCESSDETAIL where id='%s'" % ( i) r = db_oracle(sql) self.id_code[r[0][0]] = r[0][1]
def get_id(self): """获取中间表id""" if self.status == "stop": log().debug("开获取融合ID") now_id=self._getidfromlog() memgr_id=len(now_id)-len(self.befor) if memgr_id == len(self.code): list_id=now_id[0-len(self.code):] else: raise AssertionError("融合的C3数据条数为 %s,少于总媒资数据条数 %s" % (memgr_id, len(self.code))) log().debug("id_code : %s" % list_id) self.id_code={} for i in list_id: sql="select c2code, id from WS_PROCESSDETAIL where id='%s'" %(i) r=db_oracle(sql) self.id_code[r[0][0]]=r[0][1]
# gropu=["企业","家庭","军队","政府"] # # import requests # # url=r"http://10.50.108.20:8092/idp/search?q=a&userid=123456&sdonly=0&group=政府" # url=r"http://10.50.108.20:8092/idp/search?q=a&userid=123456&sdonly=0&group=家庭" # url=r"http://10.50.108.20:8092/idp/search?q=a&userid=123456&sdonly=0&group=企业" # url=r"http://10.50.108.20:8092/idp/search?q=a&userid=123456&sdonly=0&group=酒店" # result=requests.get(url) # print(result.text) from lib.funcslib import db_oracle url = r"credb/[email protected]:1521/ORCL" sql = "SELECT code from CATEGORYMAP" code = db_oracle(sql, url) print(code) import os import random sqlactive = "update ws_mergedmedia set syncflagt = '2' where substr(status,1,1)='0'" def k(): for x in code: x = x[0] gropu = ["企业", "家庭", "政府"] y = [random.choice(gropu) for i in range(0, random.randrange(1, 4, 1))] g = (",".join(y)) sql2 = "UPDATE CATEGORYMAP SET EPGGROUP='%s' where code='%s'" % (g, x) print(sql2)