def post(self, *args, **kwargs):
data = json.loads(self.request.body.decode("utf-8"))
db_obj = {}
db_obj['host'] = data['db_host']
db_obj['port'] = int(data['db_port'])
db_obj['user'] = data['db_user']
db_obj['passwd'] = data['db_pwd']
db_obj['db'] = data['db_instance']
if len(data['db_pwd']) == 32:
db_obj['passwd'] = decrypt(data['db_pwd'])
try:
if data['db_type'] == 'mysql':
mysql_conn = MysqlBase(**db_obj)
res = mysql_conn.test()
elif data['db_type'] == 'oracle':
oracle_conn = OracleBase(**db_obj)
res = oracle_conn.test()
except Exception as e:
return self.write(dict(code=-1, msg='%s' % e))
return self.write(dict(code=0, msg='连接成功'))
def get_serverObjList(ip_list=[]):
"""
:return: [{"ip": "39.104.83.140", "port": "22", "username": "******", "password": "******"}]
"""
serverObjList = []
CUSTOM_DB_INFO['db'] = 'codo_cmdb'
mysql_conn = MysqlBase(**CUSTOM_DB_INFO)
if len(ip_list) > 0:
sql = '''
select a.ip,a.`port`,b.system_user,b.`password` from asset_server a,admin_users b
where a.admin_user = b.admin_user and a.ip in ("{}")
'''.format('","'.join(ip_list))
else:
sql = '''
select a.ip,a.`port`,b.system_user,b.`password` from asset_server a,admin_users b
where a.admin_user = b.admin_user
'''
server_info = mysql_conn.query(sql)
for ip, port, username, password in server_info:
data = {}
data['ip'] = ip
data['port'] = port
data['username'] = username
data['password'] = decrypt(password)
serverObjList.append(data)
return serverObjList
def toexcel(all_customized_list, asset_date, AssetSql_date):
for j in all_customized_list: # 执行时间表
for q_id, q_header, q_dbname_id, q_totype, q_sqlstr in AssetSql_date: # sql语句表
if q_id == int(j[1]): # 脚本id相等
for s_id, s_db_host, s_db_port, s_db_user, s_db_pwd, s_db_type, s_db_instance in asset_date: # 数据库源
if str(s_id) == q_dbname_id: # 数据源id相等
CUSTOM_DB_INFO = dict(
host=s_db_host,
port=s_db_port,
user=s_db_user,
passwd=decrypt(s_db_pwd),
db=s_db_instance # 库名
)
try:
if s_db_type == "mysql":
mysql_conn = MysqlBase(**CUSTOM_DB_INFO)
db_data = mysql_conn.query(q_sqlstr)
if s_db_type == "oracle":
oracle_conn = OracleBase(**CUSTOM_DB_INFO)
db_data = oracle_conn.query(q_sqlstr)
except:
traceback.print_exc()
db_data_list = []
for g in db_data:
db_data_list.append(list(g))
# 解析excel表头
# q_header.split('|')
temp_copy = copy.deepcopy(q_header)
temp_copy2 = temp_copy.split('|')
ins_log.read_log(
'info', "11111111111111111111111111111111111111")
ins_log.read_log('info', temp_copy2)
ins_log.read_log(
'info', "11111111111111111111111111111111111111")
# 保存文件
Base_DIR = os.path.dirname(
os.path.dirname(os.path.abspath(__file__)))
upload_path = '{}/static/report/'.format(Base_DIR)
upload_path = upload_path + j[3]
# 创建的目录
if not os.path.exists(upload_path):
os.mkdir(upload_path)
file_path = upload_path + '/' + j[0] + str(
datetime.datetime.now().strftime(
'%Y%m%d-%H:%M:%S')) + ".xls"
writer = pd.ExcelWriter(file_path, engine='xlsxwriter')
df = pd.DataFrame(db_data_list,
index=range(1,
len(db_data_list) + 1),
columns=temp_copy2)
df.to_excel(writer,
encoding='utf-8',
sheet_name='Sheet')
writer.save()
def getDBList():
CUSTOM_DB_INFO['db'] = 'codo_cmdb'
mysql_conn = MysqlBase(**CUSTOM_DB_INFO)
# 获取数据库源 连接地址
select_db = 'select id,db_type, db_host, db_port, db_user, db_pwd, db_instance from asset_db'
db_info = mysql_conn.query(select_db)
return db_info
def getdatebase(ipstr):
tempdata = []
flag = []
print(ipstr[0][0], ipstr[0][1])
toname = ipstr[0][0]
toip = ipstr[0][1]
if toip not in DB_list:
CUSTOM_DB_INFO['db'] = 'codo_cmdb'
mysql_conn = MysqlBase(**CUSTOM_DB_INFO)
# 获取数据库源 连接地址
select_db = 'select db_type, db_host, db_port, db_user, db_pwd from asset_db'
db_info = mysql_conn.query(select_db)
if len(db_info) > 0:
for db_type, db_host, db_port, db_user, db_pwd in db_info:
DB_list[db_host] = [db_port, db_user, db_pwd]
# for toname, toip in ipstr:
# 解密
# 连接数据库
# print(DB_list)
try:
# uri = '{0}/{1}@{2}:{3}/{4}'.format(DB_list[toip][1], decrypt(DB_list[toip][2]), toip, DB_list[toip][0], toname)
# conn = cx_Oracle.connect(uri)
# cur = conn.cursor()
# 获取表空间数据
# sqlstr = ""
# curlist = cur.execute(sqlstr)
# datadict = curlist.fetchall()
datalist = [("system", 3.39, 98.06), ("rm_stat", 260.45, 87.63),
("PASS_20170201", 0.04, 99.67)]
for name, sumsize, uselv in datalist:
if float(sumsize) < 50 and float(uselv) > 99:
flag.append(0)
else:
flag.append(1)
if float(uselv) > 90:
name = name + ':' + str(uselv) + "%"
tempdata.append(name)
# 关闭光标
# cur.close()
# 关闭数据库连接
# conn.close()
except:
flag.append(0)
return flag, tempdata
def main():
"""
检查用户是否配置了同步codo-task MySQL信息,
如果检测到配置，初始化MySQL，同步数据
拿不到ORM直接使用原生SQL语句操作
:return:
"""
host = CODO_TASK_DB_INFO.get('host')
port = CODO_TASK_DB_INFO.get('port')
user = CODO_TASK_DB_INFO.get('user')
passwd = CODO_TASK_DB_INFO.get('passwd')
db = CODO_TASK_DB_INFO.get('db')
if not host or not port or not user or not passwd or not db:
print('[Error]: Not fount CODO_TASK_DB_INFO, auto pass...')
return False
try:
mb = MysqlBase(**CODO_TASK_DB_INFO)
obj = SyncTagTree(mb)
obj.server_sync_task()
except Exception as e:
msg = '[Error]: 请确认下CODO_TASK 数据库配置信息是否正确'
ins_log.read_log('error', e)
return msg
def getConn(db='codo_task'):
db_conf = {
'host': DEFAULT_DB_DBHOST,
'port': DEFAULT_DB_DBPORT,
'user': DEFAULT_DB_DBUSER,
'passwd': DEFAULT_DB_DBPWD,
'db': db
}
mysql_conn = MysqlBase(**db_conf)
return mysql_conn
def mysqlConnTest(data_dict):
############# 监测数据库连接情况，后续放着定时任务里面# ############
try:
# 解密密码
db_pwd = decrypt(data_dict['db_pwd'])
db_conf = {
'host': data_dict['db_host'],
'port': int(data_dict['db_port']),
'user': data_dict['db_user'],
'passwd': db_pwd,
'db': READONLY_DB_DBNAME
}
mysql_conn = MysqlBase(**db_conf)
if mysql_conn.test():
data_dict['state'] = 'Running'
else:
data_dict['state'] = 'failed'
del mysql_conn
except:
data_dict['state'] = 'failed'
def intoSql(obj):
try:
sql = '''
INSERT INTO `codo_cmdb`.`asset_server_performance`(`ip`, `cpu_sy`, `mem_total`, `mem_free`, `mem_used`,
`mem_buff_cache`, `tcp_established`, `tcp_time_wait`, `iowait`, `create_time`)
VALUES ('{ip}', '{cpu_sy}', {mem_total}, {mem_free}, {mem_used}, {mem_buff_cache}, {tcp_established},
{tcp_time_wait}, '{iowait}', '{create_time}');
'''.format(ip=obj.get('ip', ''),
cpu_sy=obj.get('cpu_sy', ''),
mem_total=obj.get('mem_total', ''),
mem_free=obj.get('mem_free', ''),
mem_used=obj.get('mem_used', ''),
mem_buff_cache=obj.get('mem_buff_cache', ''),
tcp_established=obj.get('tcp_established', 0),
tcp_time_wait=obj.get('tcp_time_wait', 0),
iowait=obj.get('iowait', ''),
create_time=datetime.datetime.now())
CUSTOM_DB_INFO['db'] = 'codo_cmdb'
mysql_conn = MysqlBase(**CUSTOM_DB_INFO)
mysql_conn.change(sql)
except Exception as e:
traceback.print_exc(e)
traceback.print_exc(sql)
def run():
DB_INFO = getDBList()
for db in DB_INFO:
try:
# 解密密码
db_pwd = decrypt(db[5])
db_conf = {
'host': db[2],
'port': int(db[3]),
'user': db[4],
'passwd': db_pwd,
'db': db[6]
}
if not db[6]:
if db[1] == 'mysql':
db_conf['db'] = 'mysql'
if db[1] == 'oracle':
db_conf['db'] = 'orcl'
if db[1] == 'mysql':
db_conn = MysqlBase(**db_conf)
if db[1] == 'oracle':
db_conn = OracleBase(**db_conf)
if db_conn.test():
state = 'Running'
else:
state = 'failed'
CUSTOM_DB_INFO['db'] = 'codo_cmdb'
mysql_conn = MysqlBase(**CUSTOM_DB_INFO)
up_sql = '''update codo_cmdb.asset_db set state = '%s' where id = %s''' % (
state, db[0])
mysql_conn.change(up_sql)
except:
traceback.print_exc()
return 'failed'
print(state)
return 'ok'
def getdatebase(datebase_name, select_str):
CUSTOM_DB_INFO['db'] = datebase_name
mysql_conn = MysqlBase(**CUSTOM_DB_INFO)
db_info = mysql_conn.query(select_str)
return db_info
def do_sql(redis_conn, key, new_data):
db_info = []
query_info = {}
res = {}
ty = 0
if 'zongdui' in str(key, 'utf8'):
ty = 0
try:
with DBContext('r') as session:
query_info = session.query(CustomQuery).filter(
CustomQuery.id == new_data['id']).first()
if query_info.type == 'sql':
dblinkId = query_info.dblinkId
CUSTOM_DB_INFO['db'] = 'codo_cmdb'
mysql_conn = MysqlBase(**CUSTOM_DB_INFO)
# 获取数据库源 连接地址
select_db = 'select db_type, db_host, db_port, db_user, db_pwd, db_instance from asset_db where id = {}'.format(
dblinkId)
db_info = mysql_conn.query(select_db)
elif query_info.type == 'urls':
data_list = []
for url in json.loads(query_info.urls):
url = 'http://' + url
status_code, resp_time = getHttpCode(url)
_d = {}
_d['get_time'] = str(datetime.datetime.now()).split('.')[0]
_d['url'] = url
_d['httpcode'] = status_code
_d['resp_time'] = resp_time
colalarms = json.loads(query_info.colalarms)
# 判断指标值 （排序后，同大取最大，同少取最少）
subColList = colalarms[0]['subColList']
subColList = sorted(subColList,
key=lambda x: TypeObj[x['alarmType']],
reverse=True)
for alarmObj in subColList:
sign = alarmObj['sign']
alarmVal = alarmObj['alarmVal']
if sign == '>' and float(resp_time) > float(alarmVal):
_d['target'] = alarmObj['alarmType']
break
if sign == '>=' and float(resp_time) >= float(
alarmVal):
_d['target'] = alarmObj['alarmType']
break
if sign == '<' and float(resp_time) < float(alarmVal):
_d['target'] = alarmObj['alarmType']
if sign == '<=' and float(resp_time) <= float(
alarmVal):
_d['target'] = alarmObj['alarmType']
if sign == '=' and float(resp_time) == float(alarmVal):
_d['target'] = alarmObj['alarmType']
data_list.append(_d)
data_list.sort(key=lambda x: TypeObj[x['target']],
reverse=True)
countObj = dict(Counter([i['target'] for i in data_list]))
res = dict(code=0,
msg='获取成功',
errormsg='',
data=data_list,
count=countObj)
# 将结果存入redis，设置过期时间.
saveRedis(redis_conn, key, new_data, res)
except:
traceback.print_exc()
if len(db_info) > 0:
res = getData(query_info, db_info)
# 将结果存入redis，设置过期时间.
saveRedis(redis_conn, key, new_data, res)
if 'zhidui' in str(key, 'utf8'):
ty = 1
zdlink = new_data['zdlink']
qid = new_data.get('qid', 0)
if int(qid) > 0:
res = getSubData(zdlink, qid)
# 将结果存入redis，设置过期时间.
saveRedis(redis_conn, key, new_data, res)
# 记录历史 只记录最高指标的一条数据
if res and ty >= 0:
saveLog(new_data, res, ty)
def change_datebase(datebase_name, select_str):
CUSTOM_DB_INFO['db'] = datebase_name
mysql_conn = MysqlBase(**CUSTOM_DB_INFO)
db_info = mysql_conn.change(select_str)
return db_info
def get(self):
qid = self.get_argument('qid', default=None, strip=True)
dict_list = []
errormsg = ''
try:
# 配置信息
query_info = {}
mysql_conn = getConn('codo_task')
sql = '''
select id,title,dblinkId,`database`,`user`,`password`,`sql`,colnames,timesTy,timesTyVal,colalarms,
`status`,create_time,update_time,description,seq,groupID from custom_query where id = %s
''' % qid
resp = mysql_conn.query(sql)
for id, title, dblinkId, database, user, password, sql, colnames, timesTy, timesTyVal, colalarms, status, \
create_time, update_time, description, seq, groupID in resp:
query_info['id'] = id
query_info['title'] = title
query_info['dblinkId'] = dblinkId
query_info['database'] = database
query_info['user'] = user
query_info['password'] = password
query_info['sql'] = sql
query_info['colnames'] = colnames
query_info['timesTy'] = timesTy
query_info['timesTyVal'] = timesTyVal
query_info['colalarms'] = colalarms
query_info['status'] = status
query_info['create_time'] = create_time
query_info['update_time'] = update_time
query_info['description'] = description
query_info['seq'] = seq
query_info['groupID'] = groupID
dblinkId = query_info['dblinkId']
# 获取数据库源 连接地址
select_db = '''
select db_type, db_host, db_port, db_user, db_pwd, db_instance
from codo_cmdb.asset_db where id = {}
'''.format(dblinkId)
db_info = mysql_conn.query(select_db)
except:
errormsg = '获取数据库源连接信息失败'
return self.write(dict(code=-1, msg='获取失败', errormsg=errormsg, data=[]))
if len(db_info) > 0:
db = db_info[0]
db_obj = {}
db_obj['host'] = db[1]
db_obj['port'] = int(db[2])
db_obj['user'] = db[3]
db_obj['passwd'] = decrypt(db[4])
if query_info['database']:
db_obj['db'] = query_info['database']
else:
db_obj['db'] = db[5]
sql = query_info['sql']
if query_info['user']:
db_obj['user'] = query_info['user']
if query_info['password']:
db_obj['passwd'] = decrypt(query_info['password'])
sql = re.sub('update|drop', '', sql, 0, re.I)
# ins_log.read_log('info', db_obj)
res = []
try:
if db[0] == 'mysql':
mysql_conn = MysqlBase(**db_obj)
res = mysql_conn.query(sql)
if db[0] == 'oracle':
oracle_conn = OracleBase(**db_obj)
res = oracle_conn.query(sql)
except Exception as e:
errormsg = '%s 数据库: 查询失败, %s' % (db_obj['host'], e)
return self.write(dict(code=-1, msg='获取失败', errormsg=errormsg, data=[]))
if res:
try:
colnames = json.loads(query_info['colnames'])
colalarms = json.loads(query_info['colalarms'])
# 增加状态列
if len(colalarms) > 0:
colnames.append({'col': "target", 'name': "指标"})
dict_key = []
for i in colnames:
dict_key.append(i['col'])
for i in res:
_d = dict(zip(dict_key, i))
for selColObj in colalarms:
# 判断指标值 (同少取最少，同大取最大)
selCol = selColObj['selCol']
if selCol in _d:
dbval = _d[selCol]
if not dbval:
dbval = 0
subColList = selColObj['subColList']
subColList = sorted(subColList, key=lambda x: TypeObj[x['alarmType']], reverse=True)
# ins_log.read_log('info', subColList)
for alarmObj in subColList:
sign = alarmObj['sign']
alarmVal = alarmObj['alarmVal']
if sign == '>' and float(dbval) > float(alarmVal):
_d['target'] = alarmObj['alarmType']
if sign == '<' and float(dbval) < float(alarmVal):
_d['target'] = alarmObj['alarmType']
if sign == '>=' and float(dbval) >= float(alarmVal):
_d['target'] = alarmObj['alarmType']
break
if sign == '<=' and float(dbval) <= float(alarmVal):
_d['target'] = alarmObj['alarmType']
if sign == '=' and float(dbval) == float(alarmVal):
_d['target'] = alarmObj['alarmType']
if 'target' not in _d:
_d['target'] = '未知'
# ins_log.read_log('info', _d)
dict_list.append(_d)
if len(colalarms) > 0:
dict_list.sort(key=lambda x: TypeObj[x['target']], reverse=True)
countObj = dict(Counter([i['target'] for i in dict_list]))
else:
countObj = {}
except Exception as e:
traceback.print_exc()
dict_list = []
countObj = {}
errormsg = '字段格式错误'
return self.write(dict(code=-2, msg='获取失败', errormsg=errormsg, data=[]))
# 转换 时间类型字段
for _d in dict_list:
for k, v in _d.items():
if isinstance(v, datetime.datetime):
_d[k] = v.strftime("%Y-%m-%d %H:%M:%S")
return self.write(dict(code=0, msg='获取成功', errormsg=errormsg, data=dict_list, count=countObj))
return self.write(dict(code=-1, msg='获取失败', errormsg=errormsg, data=[], count={}))
def renlian():
print("人脸识别率统计：开始")
temp_sql_str5 = "select sum(decode(rlsbzt,'1',1,0)) as cgl , count(*) as zl ,sum(decode(rlsbzt,'1',1,0))/count(*) from net_mhs_face_result where cjsj>=to_date('startdate','yyyymmdd') and cjsj<to_date('enddate','yyyymmdd')"
today = str(datetime.now().strftime('%Y%m')) + "01" # 当前月份
now = datetime.now()
date = now + dateutil.relativedelta.relativedelta(months=-1) # 上个月时间
befor_day = str(date.strftime('%Y%m')) + "01" #上个月份
temp_sql_str5 = str(temp_sql_str5).replace('startdate', befor_day)
sql_str5 = str(temp_sql_str5).replace('enddate', today)
print("9999999999999999999999")
print(sql_str5)
print(today)
print(befor_day)
print(sql_str5)
print("9999999999999999999999")
sql_str = "select id,db_host,db_port,db_user,db_pwd,db_type,db_instance from asset_db"
asset_date = getdatebase("codo_cmdb", sql_str) # 数据库源
for s_id, s_db_host, s_db_port, s_db_user, s_db_pwd, s_db_type, s_db_instance in asset_date: # 数据库源
CUSTOM_DB_INFO = dict(
host=s_db_host,
port=s_db_port,
user=s_db_user,
passwd=decrypt(s_db_pwd),
db=s_db_instance # 库名
)
print("11111111111111111111111111111111111111")
print(CUSTOM_DB_INFO)
print("11111111111111111111111111111111111111")
try:
if s_db_type == "mysql":
mysql_conn = MysqlBase(**CUSTOM_DB_INFO)
db_data = mysql_conn.query(sql_str5)
if s_db_type == "oracle":
oracle_conn = OracleBase(**CUSTOM_DB_INFO)
db_data = oracle_conn.query(sql_str5)
except:
traceback.print_exc()
#duanxin_date = getdatebase("codo_task", sql_str5)
# 获取当前月份
set_day = "'" + datetime.now().strftime('%Y-%m') + "'"
sql_str = "select id,renlian_defen,renlian_remarks from scorelist where today = " + set_day
meter_date = getdatebase("codo_task", sql_str)
if len(meter_date):
# if toSeconds > 0:
defenlist = []
get_id, get_defen, get_remarks = meter_date[0]
defen_sum = int(get_defen)
remarkslist = eval(get_remarks)
remarks = {}
remarks["gettime"] = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
remarks["getlongtime"] = 0
remarkslist.append(remarks)
for i in remarkslist:
defenlist.append(int(i["getlongtime"])) # 获取时长列表，取最长的时长
if max(defenlist) < 2 * 60: # 监测用户网上支付成功交易的平均操作时长，对于用户平均操作时长小于2分钟的，得5分
defen_sum = 5
toremark = '"' + str(remarkslist) + '"'
defen_str = '"' + str(defen_sum) + '"'
sql_str3 = "update scorelist set duanxin_defen = " + defen_str + "," + " duanxin_remarks=" + toremark + " where id=" + str(
get_id)
# print(sql_str3)
get_num = change_datebase("codo_task", sql_str3)
else:
# 新增一条数据
remarkslist = []
remarks = {} # {"gettime":"","getlongtime":""}
remarks["gettime"] = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
remarks["getlongtime"] = 0
remarkslist.append(remarks)
toremark = '"' + str(remarkslist) + '"'
set_creattime = '"' + datetime.now().strftime(
'%Y-%m-%d %H:%M:%S') + '"'
defen_sum = 10
defen_str = '"' + str(defen_sum) + '"'
temp_str = " , " + defen_str + "," + toremark
sql_str2 = "insert into scorelist(today,yunxing_defen,yunxing_remarks,create_time) " \
+ " values(" + set_day + temp_str + set_creattime + ")"
get_num = change_datebase("codo_task", sql_str2)
def get(self, *args, **kwargs):
# nickname = self.get_current_nickname()
key = self.get_argument('key', default=None, strip=True)
value = self.get_argument('value', default=None, strip=True)
page_size = self.get_argument('page', default=1, strip=True)
limit = self.get_argument('limit', default="999", strip=True)
limit_start = (int(page_size) - 1) * int(limit)
sys_list = []
mysql_conn = MysqlBase(**CODO_PROBLEM_DB_INFO)
with DBContext('r') as session:
if key == 'sys_name' and value:
count = session.query(Sys).filter(
Sys.sys_name.like('%{}%'.format(value))).count()
all_sys = session.query(Sys).filter(
Sys.sys_name.like('%{}%'.format(value))).order_by(
Sys.id).offset(limit_start).limit(int(limit))
elif key and key != 'sys_name' and value:
count = session.query(Sys).filter_by(**{key: value}).count()
all_sys = session.query(Sys).order_by(
Sys.id).filter_by(**{
key: value
}).order_by(Sys.id).offset(limit_start).limit(int(limit))
elif limit == '999':
count = session.query(Sys).count()
all_sys = session.query(Sys).order_by(Sys.id).all()
else:
count = session.query(Sys).count()
all_sys = session.query(Sys).order_by(
Sys.id).offset(limit_start).limit(int(limit))
for msg in all_sys:
data_dict = model_to_dict(msg)
data_dict['online_time'] = str(data_dict['online_time'])
# 上传附件列表
if data_dict[
'uploadList'] and data_dict['uploadList'] != 'null':
data_dict['uploadList'] = json.loads(
str(data_dict['uploadList']))
# data_dict['uploadList']['url'] = getPreviewUrl(self.request, data_dict['uploadList']['url'])
for i in data_dict['uploadList']:
i['url'] = getPreviewUrl(self.request,
i['response']['url'])
else:
data_dict['uploadList'] = []
# 组件列表
soft_list = session.query(SysSoft).filter(
SysSoft.sys_id == data_dict['id']).all()
data_dict['soft_list'] = []
for soft in soft_list:
soft_dict = model_to_dict(soft)
soft_dict['soft_type_name'] = [
int(soft_dict['soft_type']), soft_dict['soft_name']
]
soft_dict['soft_type'] = soft_type_dict.get(
soft_dict['soft_type'], soft_dict['soft_type'])
data_dict['soft_list'].append(soft_dict)
# 负责人列表
sys_manager_list = session.query(SysManager).filter(
SysManager.sys_id == data_dict['id']).all()
data_dict['sys_manager_list'] = []
for sys_mg in sys_manager_list:
sys_mg_dict = model_to_dict(sys_mg)
data_dict['sys_manager_list'].append(sys_mg_dict)
# url列表
data_dict['sys_url_list'] = []
sys_url_list = session.query(SysUrl).filter(
SysUrl.sys_id == data_dict['id']).all()
for sys_url in sys_url_list:
sys_url_dict = model_to_dict(sys_url)
data_dict['sys_url_list'].append(sys_url_dict)
# 文档列表
select_docx_sql = 'select f_name, f_url from pb_docxs where sysID = {}'.format(
data_dict['id'])
sys_docx_list = mysql_conn.query(select_docx_sql)
# ins_log.read_log('info', sys_docx_list)
data_dict['sys_docx_list'] = []
for l in sys_docx_list:
_d = {
'name': l[0],
'url': getPreviewUrl(self.request, l[1])
}
data_dict['sys_docx_list'].append(_d)
sys_list.append(data_dict)
if len(sys_list) > 0:
self.write(dict(code=0, msg='获取成功', count=count, data=sys_list))
else:
self.write(dict(code=-1, msg='没有查询到数据', count=count,
data=sys_list))
def getData(query_info, db_info):
dict_list = []
errormsg = ''
db = db_info[0]
db_obj = {}
db_obj['host'] = db[1]
db_obj['port'] = int(db[2])
db_obj['user'] = db[3]
db_obj['passwd'] = decrypt(db[4])
if query_info.database:
db_obj['db'] = query_info.database
else:
db_obj['db'] = db[5]
sql = query_info.sql
if query_info.user:
db_obj['user'] = query_info.user
if query_info.password:
db_obj['passwd'] = decrypt(query_info.password)
sql = re.sub('update|drop', '', sql, 0, re.I)
# ins_log.read_log('info', db_obj)
res = []
try:
if db[0] == 'mysql':
mysql_conn = MysqlBase(**db_obj)
res = mysql_conn.query(sql)
if db[0] == 'oracle':
oracle_conn = OracleBase(**db_obj)
res = oracle_conn.query(sql)
except Exception as e:
errormsg = '%s 数据库: 查询失败, %s' % (db_obj['host'], e)
return dict(code=-1, msg='获取失败', errormsg=errormsg, data=[])
if res:
try:
colnames = json.loads(query_info.colnames)
colalarms = json.loads(query_info.colalarms)
# 增加状态列
if len(colalarms) > 0:
colnames.append({'col': "target", 'name': "指标"})
dict_key = []
for i in colnames:
dict_key.append(i['col'])
for i in res:
_d = dict(zip(dict_key, i))
for selColObj in colalarms:
# 判断指标值 (同少取最少，同大取最大)
selCol = selColObj['selCol']
if selCol in _d:
dbval = _d[selCol]
if not dbval:
dbval = 0
subColList = selColObj['subColList']
subColList = sorted(
subColList,
key=lambda x: TypeObj[x['alarmType']],
reverse=True)
# ins_log.read_log('info', subColList)
for alarmObj in subColList:
sign = alarmObj['sign']
alarmVal = alarmObj['alarmVal']
if sign == '>' and float(dbval) > float(alarmVal):
_d['target'] = alarmObj['alarmType']
break
if sign == '<' and float(dbval) < float(alarmVal):
_d['target'] = alarmObj['alarmType']
if sign == '>=' and float(dbval) >= float(
alarmVal):
_d['target'] = alarmObj['alarmType']
break
if sign == '<=' and float(dbval) <= float(
alarmVal):
_d['target'] = alarmObj['alarmType']
if sign == '=' and float(dbval) == float(alarmVal):
_d['target'] = alarmObj['alarmType']
if 'target' not in _d:
_d['target'] = '未知'
# ins_log.read_log('info', _d)
dict_list.append(_d)
if len(colalarms) > 0:
dict_list.sort(key=lambda x: TypeObj[x['target']],
reverse=True)
countObj = dict(Counter([i['target'] for i in dict_list]))
else:
countObj = {}
except Exception as e:
traceback.print_exc()
errormsg = '字段格式错误'
return dict(code=-2, msg='获取失败', errormsg=errormsg, data=[])
# 转换 时间类型字段
for _d in dict_list:
for k, v in _d.items():
if isinstance(v, datetime.datetime):
_d[k] = v.strftime("%Y-%m-%d %H:%M:%S")
return dict(code=0,
msg='获取成功',
errormsg=errormsg,
data=dict_list,
count=countObj)
else:
return dict(code=-1, msg='获取失败', errormsg='没有数据库源', data=[], count={})