def addr_targs(key):
"""省份城市标签"""
bizcnf = get_cnf_val(key,sconf.BIZ)
#取业务配置
if not bizcnf:
return sconf.err_handle.biznum_not_config
ttl = bizcnf.get('ttl',600)
#取数据库配置
dbinfo = get_host_by_data(bizcnf['source'])
if not dbinfo:
return sconf.err_handle.db_not_config
dbinfo['dbname']=bizcnf['source'].split('.')[-1]
prefix = bizcnf['prefix']
rkey = "biz:targ.%s.%s"%(bizcnf['table'],prefix)
db = dbclass(dbinfo)
res,desc = db.connect()
if res == -1:
return sconf.err_handle.db_err
sql_item={}
sql_item['table'] = bizcnf['table']
sql_item['fields'] = bizcnf['fields']
sql_item['order'] = "sortid asc"
sql_item['where'] = " sortid >%s000 and sortid <%s999" % (prefix,prefix)
res,desc1 = db.query(sql_item)
sql_item['where'] = " sortid >%s000000 and sortid <%s999999" % (prefix,prefix)
res,desc2 = db.query(sql_item)
sql_item['where'] = " sortid >%s000000000 and sortid <%s999999999" % (prefix,prefix)
res,desc3 = db.query(sql_item)
if res == -1:
return sconf.err_handle.db_err
if desc:
obj = {}
for row in desc:
tid = str(row['sortid'])
if len(tid) ==3:
obj[row['sortid']]={"info":row}
elif len(tid) ==6:
pid = int(tid[:3])
if 'extn' not in obj[pid]:
obj[pid]['extn']={}
obj[pid]['extn'][row['sortid']]={"info":row}
elif len(tid)==9:
ppid = int(tid[:3])
pid = int(tid[:6])
if 'extn' not in obj[ppid]['extn'][pid]:
obj[ppid]['extn'][pid]['extn']={}
obj[ppid]['extn'][pid]['extn'][row['sortid']]={"info":row}
#try:
#rdb.set(rkey,gzip.compress(json.dumps(res).encode()))
cls_base.cache_set(rkey,json.dumps(obj),ttl)
#except:
# pass
return 0,obj
def get_dbhandle(dbname,flag=True): """获取数据库操作句柄 flag 只读标识 True选择数据库读库，False写库 """ dbinfo = sconf.get_db_info(dbname,flag) dbinfo['info']['dbname']=dbinfo['dbname'] db = dbclass(dbinfo['info']) res,desc = db.connect() if res == -1: return on_sql_error(desc) return db
def exist_corp(name,table):
"""检查公司数据是否已存在
"""
db = mysqlwrap.dbclass(config.CONFIG['mysqld'])
db.connect()
sql = "select id from data_%s where name='%s'" %(table,name)
res,desc = db.query(sql)
#print('DB: ',res,desc)
db.close()
if res == 0 and desc:
return True
return False
def prod_targs(key):
bizcnf = get_cnf_val(key,sconf.BIZ)
#取业务配置
if not bizcnf:
return sconf.err_handle.biznum_not_config
ttl = bizcnf.get('ttl',600)
#取数据库配置
dbinfo = get_host_by_data(bizcnf['source'])
if not dbinfo:
return sconf.err_handle.db_not_config
dbinfo['dbname']=bizcnf['source'].split('.')[-1]
rdb = rediswrap.get_redis('cache')
rkey = "biz:targ.%s"%bizcnf['table']
db = dbclass(dbinfo)
res,desc = db.connect()
if res == -1:
return sconf.err_handle.db_err
sql_item={}
sql_item['table'] = bizcnf['table']
sql_item['fields'] = bizcnf['fields']
sql_item['order'] = "tag_id asc"
res,desc = db.query(sql_item)
if res == -1:
return sconf.err_handle.db_err
if desc:
obj = {}
for row in desc:
tid = str(row['tag_id'])
if len(tid) ==3:
obj[row['tag_id']]={"info":row}
elif len(tid) ==6:
pid = int(tid[:3])
if 'extn' not in obj[pid]:
obj[pid]['extn']={}
obj[pid]['extn'][row['tag_id']]={"info":row}
elif len(tid)==9:
ppid = int(tid[:3])
pid = int(tid[:6])
if 'extn' not in obj[ppid]['extn'][pid]:
obj[ppid]['extn'][pid]['extn']={}
obj[ppid]['extn'][pid]['extn'][row['tag_id']]={"info":row}
#try:
#rdb.set(rkey,gzip.compress(json.dumps(res).encode()))
rdb.set(rkey,json.dumps(obj))
#except:
# pass
return 0,obj
def wrapper(kwargs):
if not dbinfo:
return err_handle.db_conf_err,
dbinfo['dbname']=biznum.split('.')[-1]
db = dbclass(dbinfo)
res,desc = db.connect()
if res == -1:
return on_sql_error(desc)
result = fn(db, **kwargs)
if result[0] == -1:
result = on_sql_error(result[1])
db.close()
return result
def wrapper(kwargs):
db = dbclass(dbinfo)
res,desc = db.connect()
if res == -1:
return on_sql_error(desc)
if kwargs['table'] in shard_table:
if "id" not in kwargs:
id = get_table_id(kwargs['table'],db)
else:
id = kwargs['id']
kwargs['table'] = get_table_name(id,kwargs['table'])
result = fn(db, **kwargs)
if result[0] == -1:
result = on_sql_error(result[1])
db.close()
return result
def reg_items_mysql(name,info):
"""items来源于mysql数据表，自动注册items
"""
k = info['source']
dbinfo = get_host_by_data(k)
if not dbinfo:
return [-1,"%s not find." % k]
dbinfo['dbname'] = k.split('.')[-1]
db = mysqlwrap.dbclass(dbinfo)
res,desc = db.connect()
if res ==-1:
return res,desc
idfield = info.get("id","id")
key_prefix = info.get("key_prefix","")
sql_item={}
sql_item['table']=info['table']
sql_item['fields']="%s,%s,%s" %(idfield,info['key'],info['name'])
where = info['where'] if 'where' in info and info['where'] else ""
sql_item['where'] = where
sql_item['limit'] = 1000
id = 0
item_total=0
while True:
sql_item['where'] = "%s and %s>%s" %(where,idfield,id) if where else "%s>%s" %(idfield,id)
res,desc = db.query(sql_item)
if res==-1 or res==0 and not desc:
break
itm=[]
for row in desc:
itm.append([row[info['name']],row[info['key']]])
print(itm[-1])
id = row[idfield]
rs,ds = stat_base.reg_items(name,itm,key_prefix)
#print(rs,ds)
if rs==0:
item_total+=len(itm)
stat_base.reg_items2redis(name)
return [0,item_total]
def prodlist(kw):
"""
test:
curl -l -H "Content-Type: application/json" -X POST -d '{"biznum":"tags.corplist","tag_id":[101]}' http://192.168.10.126:6000/biz_tags/prodlist
"""
tag_id = kw.get('tag_id',[])
biznum = kw.get('biznum','')
if not biznum:
return [-6,'parameter biznum not set.']
bizcnf = get_cnf_val(biznum,sconf.BIZ)
#取业务配置
if not bizcnf:
return sconf.err_handle.biznum_not_config
pageSize = kw.get('pageSize',bizcnf['search']['expression']['pageSize'])
page = kw.get('page',bizcnf['search']['expression']['page'])
#连接搜索引擎
host_info = get_host_by_data(bizcnf['search']['source'])
if not host_info :
return sconf.sphinx_index_not_found
sp = sphinx(host_info['host'],host_info['port'])
expression = bizcnf['search']['expression']
expression['index'] = bizcnf['search']['source'].split('.')[-1]
expression['pageSize']=pageSize
expression['page']=page
st = int(time.time())
for tid in tag_id:
tag_len = len(str(tid))
if tag_len == 3:
f = "r_tag_id"
elif tag_len == 6:
f = "c_tag_id"
elif tag_len == 9:
f = "_tag_id"
#exp = expression.copy()
expression['intType'][f]=str(tid)
sp.initQuery(expression)
rs = sp.RunQueries()
print("search_time:",time.time()-st)
result = []
st = int(time.time())
if rs:
#连接数据库
dbinfo = get_host_by_data(bizcnf['data']['source'])
dbinfo['dbname'] = bizcnf['data']['source'].split('.')[-1]
db = dbclass(dbinfo)
db.connect()
for row in rs:
sql_item = bizcnf['data'].copy()
ids = [str(k['id']) for k in row['matches']]
print("ids:",ids)
sql_item['where'] = sql_item['where'] % ",".join(ids)
res,desc = db.query(sql_item)
if res ==0 and desc:
result.append(desc)
else:
result.append([])
#print("search_time:",time.time()-st)
#pprint(result)
#print(sp._error)
return [0,result]
def gettags_base(kw):
"""标签缓存key: biz:prod:targ
"""
biznum = kw.get('biznum','')
sort = kw.get('sort','pro')
tag_id = int(kw.get('tag_id',0))
tag_id_len = 0 if len(str(tag_id)) < 3 else len(str(tag_id))
if not biznum:
return [-6,'parameter biznum not set.']
bizcnf = get_cnf_val(biznum,sconf.BIZ)
#取业务配置
if not bizcnf:
return sconf.err_handle.biznum_not_config
lv2rows = kw.get('lv2rows',bizcnf['lv2rows'])
lv3rows = kw.get('lv3rows',bizcnf['lv3rows'])
ttl = kw.get(bizcnf['ttl'],600)
isort = kw.get(bizcnf['sort'],'total_num')
#取数据库配置
dbinfo = get_host_by_data(bizcnf['source'])
if not dbinfo:
return sconf.err_handle.db_not_config
dbinfo['dbname']=bizcnf['source'].split('.')[-1]
rdb = rediswrap.get_redis('cache')
res = rdb.get("%s.%s%s%s%s" %(biznum,sort,tag_id,lv2rows,lv3rows))
if res:
return gzip.decompress(res)
db = dbclass(dbinfo)
res,desc = db.connect()
if res == -1:
return sconf.err_handle.db_err
sql_item={}
sql_item['table'] = bizcnf['table']
sql_item['fields'] = bizcnf['fields']
if tag_id_len ==3:
sql_item['where'] = "tag_id = %s or (tag_id>%s000 and tag_id <%s000) or (tag_id>%s000000 and tag_id<%s000000) " % (tag_id,tag_id,(tag_id+1),tag_id,(tag_id+1))
elif tag_id_len >=6:
sql_item['where'] = "tag_id = %s or (tag_id>%s000 and tag_id <%s000)" % (tag_id,tag_id,(tag_id+1))
sql_item['order'] = "tag_id asc"
pprint.pprint(kw)
pprint.pprint(sql_item)
res,desc = db.query(sql_item)
if res == -1:
return sconf.err_handle.db_err
if desc:
tag_id_len = 3 if tag_id_len==0 else tag_id_len
obj = {}
for row in desc:
tid = str(row['tag_id'])
if len(tid)-tag_id_len ==0:
obj[row['tag_id']]={"info":row}
elif len(tid) - tag_id_len ==3:
pid = int(tid[:tag_id_len])
if 'extn' not in obj[pid]:
obj[pid].update({'extn':{"info":row}})
obj[pid]['extn'].update({row['tag_id']:row})
elif len(tid) - tag_id_len==6:
ppid = int(tid[:tag_id_len])
pid = int(tid[:tag_id_len+3])
if 'extn' not in obj[ppid]['extn'][pid]:
obj[ppid]['extn'][pid].update({'extn':{"info":row}})
obj[ppid]['extn'][pid].update({row['tag_id']:row})
#pprint.pprint(obj)
return 0,obj
def sphinx2redis_ex(cnfkey):
"""通过配置文件，获取统计数据
"""
#取业务配置
bizcnf = get_cnf_val(cnfkey,sconf.BIZ)
if not bizcnf:
return sconf.err_handle.biznum_not_config
rdb = rediswrap.get_redis('cache')
dbinfo = get_host_by_data("base.mysql.biz72_product")
dbinfo['dbname']="biz72_product"
db = mysqlwrap.dbclass(dbinfo)
db.connect()
sql = "select tag_id from pro_tag where tag_id>100 and tag_id<200"
res,desc = db.query(sql)
print("rows:",len(desc))
source="base.sphinx.IDX_com_corp_dist"
host_info = get_host_by_data(source)
sp = sphinx(host_info['host'],host_info['port'])
prama = {
"querymod":"SPH_MATCH_EXTENDED2",
"pageSize":1,
"page":1,
"intType":{
},
"index":"IDX_com_corp_dist"
}
itm1 = {}
itm2 = {}
itm3 = {}
x=0
st = int(time.time())
for row in desc:
x+=1
if x%100==0:
print(x,time.time()-st)
tag_len = len(str(row['tag_id']))
if tag_len == 3:
f = "r_com"
elif tag_len == 6:
f = "c_com"
elif tag_len == 9:
f = "com"
prama['intType'][f]=str(row['tag_id'])
#prama['intType']["com"]='101105105'
sp.initQuery(prama)
rs = sp.RunQueries()
if rs and rs[0]['status']==0:
total_found = rs[0]['total_found']
print("tag_id",row['tag_id'],"total_found",total_found)
if total_found >0:
rdb.hset('stat2cach.tags_corp',row['tag_id'],total_found)
if tag_len == 3:
itm1[row['tag_id']]=total_found
elif tag_len == 6:
itm2[row['tag_id']]=total_found
elif tag_len == 9:
itm3[row['tag_id']]=total_found
else:
print(sp._error)
print("time:",time.time()-st)
print("itm1","len:",len(itm1),"sum:",sum(list(itm1.values())))
print("itm2","len:",len(itm2),"sum:",sum(list(itm2.values())))
print("itm3","len:",len(itm3),"sum:",sum(list(itm3.values())))