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 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 post(self):
data = json.loads(self.request.body.decode("utf-8"))
searchValue = data.get('searchValue')
stime = data.get('stime')
etime = data.get('etime')
Base_DIR = os.path.dirname(
os.path.dirname(os.path.dirname(os.path.abspath(__file__))))
dir_path = '{}/static/report/'.format(Base_DIR)
filedir_path = '%s/%s/' % (dir_path, searchValue)
if not os.path.exists(filedir_path):
os.mkdir(filedir_path)
try:
if searchValue == 'kcjctj':
_obj = report_Obj[searchValue]
file_na = getFileNa(_obj["file_na"], stime, etime)
file_path = os.path.join(filedir_path, file_na)
columns = _obj["columns"]
sql = _obj["sql"].format(stime=stime, etime=etime)
oracle_conn = OracleBase(**ordb_obj)
res = oracle_conn.query(sql)
# res = (('4401', 904, 904, 0, 547, 56, 0),
# ('4402', 123, 123, 4, 324, 657, 0),
# ('4403', 677, 454, 0, 789, 567, 0),
# ('4404', 345, 998, 0, 566, 133, 0),
# ('4405', 677, 454, 0, 456, 134, 0),
# ('4406', 345, 795, 0, 678, 345, 0))
data = []
for d in res:
_l = []
if d[0] in flag:
_l.append('粤' + flag[d[0]][0])
_l.append(flag[d[0]][1])
else:
_l.append(d[0])
_l.append(d[0])
_l = _l + list(d[1:])
data.append(_l)
saveExce(columns, data, file_path)
except:
traceback.print_exc()
return self.write(dict(code=-1, msg='执行错误'))
return self.write(dict(code=0, msg='执行成功'))
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 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):
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 get(self, *args, **kwargs):
key = self.get_argument('key', default=None, strip=True)
value = self.get_argument('value', default=None, strip=True)
start_date = self.get_argument('start_date', default='', strip=True)
end_date = self.get_argument('end_date', default='', strip=True)
dict_list = []
query_info = []
count = 0
start_date_str = start_date + " 00:00:00"
end_date_str = end_date + " 23:59:59"
if key == 'dataAll':
with DBContext('r') as session:
resdata = {}
conditions = []
conditions.append(
CertDataUpLoadLog.create_time >= start_date_str)
conditions.append(
CertDataUpLoadLog.create_time <= end_date_str)
today_send = session.query(func.sum(
CertDataUpLoadLog.success)).filter(*conditions).scalar()
all_send = session.query(func.sum(
CertDataUpLoadLog.total)).scalar()
resdata['sumUpLoad'] = int(all_send) if all_send else 0
resdata['todayUpLoad'] = int(today_send) if today_send else 0
resdata['todayDownLoad'] = 0
try:
sql = '''
select sum(cnt) from (
select count(*) cnt from up_pro_accept_v4_unqbak
where recordtime >= to_date('{stime}', 'yyyy-mm-dd hh24:mi:ss')
and recordtime <= to_date('{etime}', 'yyyy-mm-dd hh24:mi:ss')
union all
select count(*) cnt from up_pro_process_v4_unqbak
where recordtime >= to_date('{stime}', 'yyyy-mm-dd hh24:mi:ss')
and recordtime <= to_date('{etime}', 'yyyy-mm-dd hh24:mi:ss')
union all
select count(*) cnt from up_pro_result_v4_unqbak
where recordtime >= to_date('{stime}', 'yyyy-mm-dd hh24:mi:ss')
and recordtime <= to_date('{etime}', 'yyyy-mm-dd hh24:mi:ss')
)
'''.format(stime=start_date_str, etime=end_date_str)
db_obj = {}
db_obj['host'] = '10.40.30.98'
db_obj['port'] = 1521
db_obj['user'] = ''
db_obj['passwd'] = ''
db_obj['db'] = 'cspt'
db_list = ['bj_zhpt', 'bj_sgjj', 'bj_ddzxc']
for d in db_list:
db_obj['user'] = d
db_obj['passwd'] = d
oracle_conn = OracleBase(**db_obj)
res = oracle_conn.query(sql)
resdata['todayDownLoad'] += int(res[0][0])
except Exception as e:
ins_log.read_log('error', e)
resdata['todayDownLoad'] = 0
dict_list.append(resdata)
if key == 'UpLoadLog':
with DBContext('r') as session:
conditions = []
conditions.append(
CertDataUpLoadLog.create_time >= start_date_str)
conditions.append(
CertDataUpLoadLog.create_time <= end_date_str)
#count = session.query(CertDataUpLoadLog).filter(*conditions).count()
query_info = session.query(CertDataUpLoadLog).filter(
*conditions).order_by(CertDataUpLoadLog.id.desc()).all()
if key == 'DownLoadLog':
try:
sql = '''
select recordtime, cd_batch, error_msg from up_pro_accept_v4_unqbak
where recordtime >= to_date('{stime}', 'yyyy-mm-dd hh24:mi:ss')
and recordtime <= to_date('{etime}', 'yyyy-mm-dd hh24:mi:ss')
union all
select recordtime, cd_batch, error_msg from up_pro_process_v4_unqbak
where recordtime >= to_date('{stime}', 'yyyy-mm-dd hh24:mi:ss')
and recordtime <= to_date('{etime}', 'yyyy-mm-dd hh24:mi:ss')
union all
select recordtime, cd_batch, error_msg from up_pro_result_v4_unqbak
where recordtime >= to_date('{stime}', 'yyyy-mm-dd hh24:mi:ss')
and recordtime <= to_date('{etime}', 'yyyy-mm-dd hh24:mi:ss')
order by 1 desc
'''.format(stime=start_date_str, etime=end_date_str)
db_obj = {}
db_obj['host'] = '10.40.30.98'
db_obj['port'] = 1521
db_obj['user'] = ''
db_obj['passwd'] = ''
db_obj['db'] = 'cspt'
db_list = ['bj_zhpt', 'bj_sgjj', 'bj_ddzxc']
for d in db_list:
db_obj['user'] = d
db_obj['passwd'] = d
oracle_conn = OracleBase(**db_obj)
res = oracle_conn.query(sql)
query_info.extend(res)
query_info = query_info[0:100]
except Exception as e:
ins_log.read_log('error', e)
if key == 'UpLoadError':
with DBContext('r') as session:
conditions = []
conditions.append(
CertDataUpLoadError.create_time >= start_date_str)
conditions.append(
CertDataUpLoadError.create_time <= end_date_str)
#count = session.query(CertDataUpLoadError).filter(*conditions).count()
query_info = session.query(CertDataUpLoadError).filter(
*conditions).order_by(
CertDataUpLoadError.id.desc()).offset(0).limit(
100).all()
if key == 'DownLoadError':
with DBContext('r') as session:
conditions = []
conditions.append(
CertDataDownLoadError.create_time >= start_date_str)
conditions.append(
CertDataDownLoadError.create_time <= end_date_str)
#count = session.query(CertDataDownLoadError).filter(*conditions).count()
query_info = session.query(CertDataDownLoadError).filter(
*conditions).order_by(
CertDataDownLoadError.id.desc()).offset(0).limit(
100).all()
for msg in query_info:
if key == 'UpLoadLog':
data_dict = {}
data_dict['total'] = msg.total
data_dict['success'] = msg.success
data_dict['topic'] = msg.topic
data_dict['failed'] = msg.failed
data_dict['extra'] = json.loads(msg.extra) if msg.extra else ''
data_dict['create_time'] = str(msg.create_time)
dict_list.append(data_dict)
if key == 'DownLoadLog':
data_dict = {}
data_dict['create_time'] = str(msg[0])
data_dict['cd_batch'] = msg[1]
data_dict['error_msg'] = msg[2]
dict_list.append(data_dict)
if key == 'UpLoadError':
data_dict = {}
data_dict['topic'] = msg.topic
data_dict['data'] = msg.data
data_dict['create_time'] = str(msg.create_time)
dict_list.append(data_dict)
if key == 'DownLoadError':
data_dict = {}
data_dict['topic'] = msg.topic
data_dict['data'] = msg.data
data_dict['create_time'] = str(msg.create_time)
dict_list.append(data_dict)
return self.write(dict(code=0, msg='获取成功', data=dict_list,
count=count))
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={})