def __init__(self, to_addr=None, ssl=None):
self.to_addr = to_addr
un_init = util.init_conf()
self.ssl = ssl
self.email = ast.literal_eval(un_init['message'])
self.email_suffix_list = ast.literal_eval(util.init_conf().get(
'other', '')).get('email_suffix_list', []) # 获取可以注册邮箱后缀
def post(self, request, args=None):
un_init = util.init_conf()
limit = ast.literal_eval(un_init['other'])
sql = request.data['sql']
check = str(sql).strip().split(';\n')
user = query_order.objects.filter(
username=request.user).order_by('-id').first()
un_init = util.init_conf()
custom_com = ast.literal_eval(un_init['other'])
if user.query_per == 1:
if check[-1].strip().lower().startswith('s') != 1:
return Response('只支持查询功能或删除不必要的空白行！')
else:
address = json.loads(request.data['address'])
_c = DatabaseList.objects.filter(
connection_name=user.connection_name,
computer_room=user.computer_room).first()
with con_database.SQLgo(ip=_c.ip,
password=_c.password,
user=_c.username,
port=_c.port,
db=address['basename']) as f:
try:
if limit.get('limit').strip() == '':
CUSTOM_ERROR.error('未设置全局最大limit值，系统自动设置为1000')
query_sql = replace_limit(check[-1].strip(), 1000)
else:
query_sql = replace_limit(check[-1].strip(),
limit.get('limit'))
data_set = f.search(sql=query_sql)
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(e)
else:
for l in data_set['data']:
for k, v in l.items():
if isinstance(v, bytes):
for n in range(data_set['len']):
data_set['data'][n].update(
{k: 'blob字段为不可呈现类型'})
for i in custom_com['sensitive_list']:
if k == i:
for n in range(data_set['len']):
data_set['data'][n].update(
{k: '********'})
querypermissions.objects.create(work_id=user.work_id,
username=request.user,
statements=query_sql)
return HttpResponse(
simplejson.dumps(data_set,
cls=DateEncoder,
bigint_as_string=True))
else:
return Response('非法请求,账号无查询权限！')
def post(self, request, args=None):
un_init = util.init_conf()
limit = ast.literal_eval(un_init['other'])
sql = request.data['sql']
check = str(sql).strip().split(';\n')
user = query_order.objects.filter(username=request.user).order_by('-id').first()
un_init = util.init_conf()
custom_com = ast.literal_eval(un_init['other'])
if user.query_per == 1:
if check[-1].strip().lower().startswith('s') != 1:
return Response('只支持查询功能或删除不必要的空白行！')
else:
address = json.loads(request.data['address'])
_c = DatabaseList.objects.filter(
connection_name=user.connection_name,
computer_room=user.computer_room
).first()
with con_database.SQLgo(
ip=_c.ip,
password=_c.password,
user=_c.username,
port=_c.port,
db=address['basename']
) as f:
try:
if limit.get('limit').strip() == '':
CUSTOM_ERROR.error('未设置全局最大limit值，系统自动设置为1000')
query_sql = replace_limit(check[-1].strip(), 1000)
else:
query_sql = replace_limit(check[-1].strip(), limit.get('limit'))
data_set = f.search(sql=query_sql)
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(e)
else:
for l in data_set['data']:
for k, v in l.items():
if isinstance(v, bytes):
for n in range(data_set['len']):
data_set['data'][n].update({k: 'blob字段为不可呈现类型'})
for i in custom_com['sensitive_list']:
if k == i:
for n in range(data_set['len']):
data_set['data'][n].update({k: '********'})
querypermissions.objects.create(
work_id=user.work_id,
username=request.user,
statements=query_sql
)
return HttpResponse(simplejson.dumps(data_set, cls=DateEncoder, bigint_as_string=True))
else:
return Response('非法请求,账号无查询权限！')
def post(self,request):
try:
data = request.data
un_init = util.init_conf()
custom_com = ast.literal_eval(un_init['other'])
redis_faina = custom_com['redis_faina_file']
redis_faina_cnt = custom_com['redis_faina_cnt']
try:
cmd_args='redis-cli -h %s -p %s -a %s monitor|head -n %s| %s' %(data['redis_host'],data['redis_port'], data['redis_pwd'],redis_faina_cnt,redis_faina)
p = subprocess.Popen(cmd_args,
shell=True,
stdout=subprocess.PIPE,
stderr=subprocess.PIPE,
universal_newlines=True)
try:
p.wait(timeout=30)
except Exception as e:
return Response({'error': 'Redis faina执行超时!'})
res = []
for line in iter(p.stdout.readline, ''):
res.append(line)
return Response({'data': res})
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return Response({'error': 'Redis faina失败2:' + e.args[0]})
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return Response({'error': 'Redis faina失败1:' + e.args[0]})
def push_message(message=None,
type=None,
user=None,
to_addr=None,
work_id=None,
status=None):
try:
tag = globalpermissions.objects.filter(authorization='global').first()
if tag.message['mail']:
try:
put_mess = send_email.send_email(to_addr=to_addr)
put_mess.send_mail(mail_data=message, type=type)
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
if tag.message['ding']:
un_init = util.init_conf()
webhook = ast.literal_eval(un_init['message'])
util.dingding(
content=
'# <font face=\"微软雅黑\">工单提交通知</font> # \n <br> \n **工单编号:** %s \n \n **提交人员:** <font color=\"#000080\">%s</font><br /> \n \n **状态:** <font color=\"#FF9900\">%s</font><br /> \n'
% (work_id, user, status),
url=webhook['webhook'])
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
def __init__(self):
un_init = util.init_conf()
custom_com = ast.literal_eval(un_init['other'])
self.path = custom_com['Binlog2sql']
self.required_args = []
self.disable_args = []
super(Plugin, self).__init__()
def get(self, request, args: str = None):
try:
page = request.GET.get('page')
qurey = json.loads(request.GET.get('query'))
un_init = util.init_conf()
custom_com = ast.literal_eval(un_init['other'])
except KeyError as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
else:
try:
start = (int(page) - 1) * 20
end = int(page) * 20
if qurey['valve']:
if len(qurey['picker']) == 0:
info = SqlOrder.objects.filter(
username=request.user,
version__contains=qurey['version'],
text__contains=qurey['text']).order_by(
'-id').defer('sql')[start:end]
page_number = SqlOrder.objects.filter(
username=request.user,
text__contains=qurey['text']).only('id').count()
else:
print("查询")
picker = []
query_builder = SqlOrder.objects.filter(
username=request.user,
text__contains=qurey['text'],
version__contains=qurey['version'])
for i in qurey['picker']:
picker.append(i)
if picker[0] == "":
info = query_builder.defer('sql').order_by(
'-id')[start:end]
else:
info = query_builder.filter(
date__gte=picker[0],
date__lte=picker[1]).defer('sql').order_by(
'-id')[start:end]
page_number = SqlOrder.objects.filter(
username=request.user,
text__contains=qurey['text']).only('id').count()
else:
info = SqlOrder.objects.filter(
username=request.user).defer('sql').order_by(
'-id')[start:end]
page_number = SqlOrder.objects.filter(
username=request.user).only('id').count()
data = util.ser(info)
return Response({
'page': page_number,
'data': data,
'multi': custom_com['multi']
})
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
def get(self, request, args=None):
'''
:argument 管理页面数据展示
:return
{
'page': page_number,
'data': serializers.data,
'ding_switch': switch_dingding,
'mail_switch': switch_email
}
'''
try:
page = request.GET.get('page')
con = json.loads(request.GET.get('con'))
except KeyError as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
else:
try:
un_init = util.init_conf()
custom_com = ast.literal_eval(un_init['other'])
start = int(page) * 10 - 10
end = int(page) * 10
if con['valve']:
page_number = DatabaseList.objects.filter(
connection_name__contains=con['connection_name'],
computer_room__contains=con['computer_room']).count()
info = DatabaseList.objects.filter(
connection_name__contains=con['connection_name'],
computer_room__contains=con['computer_room']
)[start:end]
else:
page_number = DatabaseList.objects.count()
if page_number == 0:
return Response({
'page': page_number,
'data': [],
'custom': custom_com['con_room']
})
info = DatabaseList.objects.all().order_by(
'connection_name')[start:end]
serializers = Sqllist(info, many=True)
return Response({
'page': page_number,
'data': serializers.data,
'custom': custom_com['con_room']
})
except Exception as e:
print("调试########")
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
def __enter__(self):
un_init = util.init_conf()
inception = ast.literal_eval(un_init['inception'])
self.con = pymysql.connect(host=inception['host'],
user=inception['user'],
passwd=inception['password'],
port=int(inception['port']),
db='',
charset="utf8")
return self
def __enter__(self):
un_init = util.init_conf()
inception = ast.literal_eval(un_init['inception'])
self.con = pymysql.connect(host=inception['host'],
user=inception['user'],
passwd=inception['password'],
port=int(inception['port']),
db='',
charset="utf8")
return self
def roll(backdb=None, opid=None):
un_init = util.init_conf()
inception = ast.literal_eval(un_init['inception'])
with con_database.SQLgo(ip=inception["back_host"],
user=inception["back_user"],
password=inception["back_password"],
port=inception["back_port"]) as f:
data = f.query_info(sql='''
select rollback_statement from %s where opid_time =%s;
''' % (backdb, opid))
return data
def rollbackSQL(db=None, opid=None):
un_init = util.init_conf()
inception = ast.literal_eval(un_init['inception'])
with con_database.SQLgo(ip=inception["back_host"],
user=inception["back_user"],
password=inception["back_password"],
db=db,
port=inception["back_port"]) as f:
data = f.query_info(sql='''
select tablename from $_$Inception_backup_information$_$ where opid_time =%s;
''' % opid)
return data[0][0]
def post(self, request, args=None):
sql_offset = request.data["sql_offset"]
limit = util.get_conf_limit()
sql = request.data['sql']
check = str(sql).strip().split(';\n')
# user = query_order.objects.filter(username=request.user).order_by('-id').first()
un_init = util.init_conf()
custom_com = ast.literal_eval(un_init['other'])
dbname_list = get_dblist_foruser(request.user)
if request.data['address'] not in dbname_list:
return Response({'error': '无权限，请先申请权限！'})
if check[-1].strip().lower().startswith('s') != 1:
return Response({'error': '只支持查询功能或删除不必要的空白行！'})
else:
_c = DatabaseList.objects.filter(
connection_name=request.data['address']).first()
try:
with con_database.SQLgo(ip=_c.ip,
password=_c.password,
user=_c.username,
port=_c.port,
db=_c.dbname) as f:
query_sql = check[-1].strip().strip(";") + ' limit ' + str(
limit) + ' offset ' + str(sql_offset)
data_set = f.search(sql=query_sql)
for l in data_set['data']:
for k, v in l.items():
if isinstance(v, bytes):
for n in range(data_set['len']):
data_set['data'][n].update(
{k: 'blob字段为不可呈现类型'})
for i in custom_com['sensitive_list']:
if k == i:
for n in range(data_set['len']):
data_set['data'][n].update(
{k: '********'})
querypermissions.objects.create(
# work_id=user.work_id,
username=request.user,
statements=query_sql,
create_time=util.datetime(),
connect_name=request.data['address'])
return HttpResponse(
simplejson.dumps(data_set,
cls=DateEncoder,
bigint_as_string=True))
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return Response({'error': str(e)})
def roll(backdb=None, opid=None):
un_init = util.init_conf()
inception = ast.literal_eval(un_init['inception'])
with con_database.SQLgo(
ip=inception["back_host"],
user=inception["back_user"],
password=inception["back_password"],
port=inception["back_port"]
) as f:
data = f.dic_data(
sql=
'''
select rollback_statement from %s where opid_time =%s;
''' % (backdb, opid))
return data
def rollbackSQL(db=None, opid=None):
un_init = util.init_conf()
inception = ast.literal_eval(un_init['inception'])
with con_database.SQLgo(
ip=inception["back_host"],
user=inception["back_user"],
password=inception["back_password"],
db=db,
port=inception["back_port"]
) as f:
data = f.execute(
sql=
'''
select tablename from $_$Inception_backup_information$_$ where opid_time =%s;
''' % opid)
return data[0][0]
def push_message(message=None, type=None, user=None, to_addr=None, work_id=None, status=None):
try:
tag = globalpermissions.objects.filter(authorization='global').first()
if tag.message['mail']:
put_mess = send_email.send_email(to_addr=to_addr, ssl=tag.message['ssl'])
put_mess.send_mail(mail_data=message, type=type)
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
else:
try:
if tag.message['ding']:
un_init = util.init_conf()
webhook = ast.literal_eval(un_init['message'])
util.dingding(content='权限申请通知\n工单编号:%s\n发起人:%s\n状态:%s' % (work_id, user, status),
url=webhook['webhook'])
except ValueError as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
def get(self, request, args=None):
'''
:argument 管理页面数据展示
:return
{
'page': page_number,
'data': serializers.data,
'diclist': data,
'ding_switch': switch_dingding,
'mail_switch': switch_email
}
'''
try:
page = request.GET.get('page')
except KeyError as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
else:
try:
un_init = util.init_conf()
custom_com = ast.literal_eval(un_init['other'])
page_number = DatabaseList.objects.count()
start = int(page) * 10 - 10
end = int(page) * 10
info = DatabaseList.objects.all()[start:end]
serializers = Sqllist(info, many=True)
data = SqlDictionary.objects.all().values('Name')
data.query.group_by = ['Name'] # 不重复表名
return Response(
{
'page': page_number,
'data': serializers.data,
'diclist': data,
'custom': custom_com['con_room']
}
)
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
def push_message(message=None, type=None, user=None, to_addr=None, work_id=None, status=None):
try:
tag = globalpermissions.objects.filter(authorization='global').first()
if tag.message['mail']:
put_mess = send_email.send_email(to_addr=to_addr)
put_mess.send_mail(mail_data=message, type=type)
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
else:
try:
if tag.message['ding']:
un_init = util.init_conf()
webhook = ast.literal_eval(un_init['message'])
util.dingding(content='查询申请通知\n工单编号:%s\n发起人:%s\n状态:%s' % (work_id, user, status),
url=webhook['webhook'])
except ValueError as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
def get(self, request, args: str = None):
'''
:argument 审核页面数据展示请求接口
:param None
:return 数据条数, 数据
'''
try:
page = request.GET.get('page')
username = request.GET.get('username')
except KeyError as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
else:
try:
un_init = util.init_conf()
custom_com = ast.literal_eval(un_init['other'])
page_number = SqlOrder.objects.filter(
assigned=username).count()
start = (int(page) - 1) * 20
end = int(page) * 20
info = SqlOrder.objects.raw('''
select o.id,o.work_id,o.text,o.backup,o.date,o.assigned,
o.username,o.real_name,o.status,o.basename,o.delay,core_databaselist.connection_name, \
core_databaselist.computer_room from core_sqlorder as o \
INNER JOIN core_databaselist on \
o.bundle_id = core_databaselist.id where o.assigned = '%s'\
ORDER BY o.id desc
''' % username)[start:end]
data = util.ser(info)
info = Account.objects.filter(group='perform').all()
ser = serializers.UserINFO(info, many=True)
return Response({
'page': page_number,
'data': data,
'multi': custom_com['multi'],
'multi_list': ser.data
})
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
def post(self, request, args=None):
try:
username = request.data['username']
password = request.data['password']
group = request.data['group']
department = request.data['department']
email = request.data['email']
# //添加密码过期时间
un_init = util.init_conf()
limit = ast.literal_eval(un_init['other'])
expire_days = limit.get('expire_days') if limit.get('expire_days') is not None else 90
expire_date = (datetime.now() + timedelta(days=expire_days)).isoformat()
except KeyError as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
else:
try:
if group == 'admin' or group == 'perform':
user = Account.objects.create_user(
username=username,
password=password,
department=department,
group=group,
is_staff=1,
email=email,
expire_date=expire_date
)
user.save()
grained.objects.get_or_create(username=username, permissions=PERMISSION)
return Response('%s 用户注册成功!' % username)
elif group == 'guest':
user = Account.objects.create_user(
username=username,
password=password,
department=department,
group=group,
email=email,
expire_date=expire_date
)
user.save()
grained.objects.get_or_create(username=username, permissions=PERMISSION)
return Response('%s 用户注册成功!' % username)
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return Response(e)
def get(self, request, args=None):
'''
:argument 管理页面数据展示
:return
{
'page': page_number,
'data': serializers.data,
'diclist': data,
'ding_switch': switch_dingding,
'mail_switch': switch_email
}
'''
try:
page = request.GET.get('page')
except KeyError as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
else:
try:
un_init = util.init_conf()
custom_com = ast.literal_eval(un_init['other'])
page_number = DatabaseList.objects.count()
start = int(page) * 10 - 10
end = int(page) * 10
info = DatabaseList.objects.all().order_by(
'connection_name')[start:end]
serializers = Sqllist(info, many=True)
data = SqlDictionary.objects.all().values('Name')
data.query.group_by = ['Name'] # 不重复表名
return Response({
'page': page_number,
'data': serializers.data,
'diclist': data,
'custom': custom_com['con_room']
})
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
def get(self, request, args: str = None):
'''
:argument 审核页面数据展示请求接口
:param None
:return 数据条数, 数据
'''
try:
page = request.GET.get('page')
username = request.GET.get('username')
except KeyError as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
else:
try:
un_init = util.init_conf()
custom_com = ast.literal_eval(un_init['other'])
page_number = SqlOrder.objects.filter(assigned=username).count()
start = (int(page) - 1) * 20
end = int(page) * 20
info = SqlOrder.objects.raw(
'''
select core_sqlorder.*,core_databaselist.connection_name, \
core_databaselist.computer_room from core_sqlorder \
INNER JOIN core_databaselist on \
core_sqlorder.bundle_id = core_databaselist.id where core_sqlorder.assigned = '%s'\
ORDER BY core_sqlorder.id desc
''' % username
)[start:end]
data = util.ser(info)
info = Account.objects.filter(group='perform').all()
ser = serializers.UserINFO(info, many=True)
return Response(
{'page': page_number, 'data': data, 'multi': custom_com['multi'], 'multi_list': ser.data})
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
def put(self, request, args: str = None):
if request.data['mode'] == 'put':
instructions = request.data['instructions']
connection_name = request.data['connection_name']
computer_room = request.data['computer_room']
real = request.data['real_name']
export = request.data['export']
audit = request.data['audit']
un_init = util.init_conf()
query_switch = ast.literal_eval(un_init['other'])
query_per = 2
work_id = util.workId()
if not query_switch['query']:
query_per = 2
else:
userinfo = Account.objects.filter(username=audit,
group='admin').first()
try:
thread = threading.Thread(target=push_message,
args=({
'to_user': request.user,
'workid': work_id
}, 5, request.user,
userinfo.email, work_id,
'提交'))
thread.start()
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
query_order.objects.create(work_id=work_id,
instructions=instructions,
username=request.user,
date=util.date(),
query_per=query_per,
connection_name=connection_name,
computer_room=computer_room,
export=export,
audit=audit,
time=util.date(),
real_name=real)
if not query_switch['query']:
query_order.objects.filter(work_id=work_id).update(query_per=1)
## 钉钉及email站内信推送
return Response('查询工单已提交，等待管理员审核！')
elif request.data['mode'] == 'agree':
work_id = request.data['work_id']
query_info = query_order.objects.filter(
work_id=work_id).order_by('-id').first()
query_order.objects.filter(work_id=work_id).update(query_per=1)
userinfo = Account.objects.filter(
username=query_info.username).first()
try:
thread = threading.Thread(target=push_message,
args=({
'to_user': query_info.username,
'workid': query_info.work_id
}, 6, query_info.username,
userinfo.email, work_id, '同意'))
thread.start()
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return Response('查询工单状态已更新！')
elif request.data['mode'] == 'disagree':
work_id = request.data['work_id']
query_order.objects.filter(work_id=work_id).update(query_per=0)
query_info = query_order.objects.filter(
work_id=work_id).order_by('-id').first()
userinfo = Account.objects.filter(
username=query_info.username).first()
try:
thread = threading.Thread(target=push_message,
args=({
'to_user': query_info.username,
'workid': query_info.work_id
}, 7, query_info.username,
userinfo.email, work_id, '驳回'))
thread.start()
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return Response('查询工单状态已更新！')
elif request.data['mode'] == 'status':
try:
status = query_order.objects.filter(
username=request.user).order_by('-id').first()
return Response(status.query_per)
except:
return Response(0)
elif request.data['mode'] == 'end':
try:
query_order.objects.filter(
username=request.data['username']).order_by('-id').update(
query_per=3)
return Response('已结束查询！')
except Exception as e:
return HttpResponse(e)
elif request.data['mode'] == 'info':
tablelist = []
highlist = []
database = query_order.objects.filter(
username=request.user).order_by('-id').first()
_connection = DatabaseList.objects.filter(
connection_name=database.connection_name).first()
with con_database.SQLgo(ip=_connection.ip,
user=_connection.username,
password=_connection.password,
port=_connection.port) as f:
dataname = f.query_info(sql='show databases')
children = []
ignore = exclued_db_list()
for index, uc in sorted(enumerate(dataname), reverse=True):
for cc in ignore:
if uc['Database'] == cc:
del dataname[index]
for i in dataname:
with con_database.SQLgo(ip=_connection.ip,
user=_connection.username,
password=_connection.password,
port=_connection.port,
db=i['Database']) as f:
tablename = f.query_info(sql='show tables')
highlist.append({'vl': i['Database'], 'meta': '库名'})
for c in tablename:
key = 'Tables_in_%s' % i['Database']
highlist.append({'vl': c[key], 'meta': '表名'})
children.append({'title': c[key]})
tablelist.append({
'title': i['Database'],
'children': children
})
children = []
data = [{
'title': database.connection_name,
'expand': 'true',
'children': tablelist
}]
return Response({
'info': json.dumps(data),
'status': database.export,
'highlight': highlist
})
def get(self, request, args: str = None):
'''
:argument 审核页面数据展示请求接口
:param None
:return 数据条数, 数据
'''
try:
page = request.GET.get('page')
qurey = json.loads(request.GET.get('query'))
except KeyError as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
else:
try:
un_init = util.init_conf()
custom_com = ast.literal_eval(un_init['other'])
start = (int(page) - 1) * 20
end = int(page) * 20
u_mulit = Account.objects.filter(username=request.user).first()
if u_mulit.group == 'perform':
if qurey['valve']:
if len(qurey['picker']) == 0:
info = SqlOrder.objects.filter(
executor=request.user,
username__contains=qurey['user']).defer(
'sql').order_by('-id')[start:end]
page_number = SqlOrder.objects.filter(
executor=request.user,
username__contains=qurey['user']).only(
'id').count()
else:
picker = []
for i in qurey['picker']:
picker.append(i)
info = SqlOrder.objects.filter(
executor=request.user,
username__contains=qurey['user'],
date__gte=picker[0],
date__lte=picker[1]).defer('sql').order_by(
'-id')[start:end]
page_number = SqlOrder.objects.filter(
executor=request.user,
username__contains=qurey['user'],
date__gte=picker[0],
date__lte=picker[1]).only('id').count()
else:
page_number = SqlOrder.objects.filter(
executor=request.user).only('id').count()
info = SqlOrder.objects.filter(
executor=request.user).defer('sql').order_by(
'-id')[start:end]
else:
if qurey['valve']:
if len(qurey['picker']) == 0:
info = SqlOrder.objects.filter(
assigned=request.user,
username__contains=qurey['user']).defer(
'sql').order_by('-id')[start:end]
page_number = SqlOrder.objects.filter(
assigned=request.user,
username__contains=qurey['user']).only(
'id').count()
else:
picker = []
for i in qurey['picker']:
picker.append(i)
info = SqlOrder.objects.filter(
assigned=request.user,
username__contains=qurey['user'],
date__gte=picker[0],
date__lte=picker[1]).defer('sql').order_by(
'-id')[start:end]
page_number = SqlOrder.objects.filter(
assigned=request.user,
username__contains=qurey['user'],
date__gte=picker[0],
date__lte=picker[1]).only('id').count()
else:
page_number = SqlOrder.objects.filter(
assigned=request.user).only('id').count()
info = SqlOrder.objects.filter(
assigned=request.user).defer('sql').order_by(
'-id')[start:end]
data = util.ser(info)
info = Account.objects.filter(group='perform').all()
ser = serializers.UserINFO(info, many=True)
return Response({
'page': page_number,
'data': data,
'multi': custom_com['multi'],
'multi_list': ser.data
})
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
def put(self, request, args=None):
if args == 'connection':
try:
assigned = set_auth_group(request.user)
un_init = util.init_conf()
custom_com = ast.literal_eval(un_init['other'])
if request.data['permissions_type'] == 'user' or request.data[
'permissions_type'] == 'own_space':
info = DatabaseList.objects.all()
con_name = Area(info, many=True).data
dic = SqlDictionary.objects.all().values('Name')
dic.query.distinct = ['Name']
elif request.data['permissions_type'] == 'query':
con_name = []
permission_spec = grained.objects.filter(
username=request.user).first()
if permission_spec.permissions['query'] == '1':
for i in permission_spec.permissions['querycon']:
con_instance = DatabaseList.objects.filter(
connection_name=i).first()
if con_instance:
con_name.append({
'id':
con_instance.id,
'connection_name':
con_instance.connection_name,
'ip':
con_instance.ip,
'computer_room':
con_instance.computer_room
})
assigned = set_auth_group(request.user)
return Response({
'assigend': assigned['person'],
'connection': con_name,
'custom': custom_com['con_room']
})
else:
con_name = []
_type = request.data['permissions_type'] + 'con'
permission_spec = grained.objects.filter(
username=request.user).first()
for i in permission_spec.permissions[_type]:
con_instance = DatabaseList.objects.filter(
connection_name=i).first()
if con_instance:
con_name.append({
'id':
con_instance.id,
'connection_name':
con_instance.connection_name,
'ip':
con_instance.ip,
'computer_room':
con_instance.computer_room
})
dic = ''
info = Account.objects.filter(group='admin').all()
serializers = UserINFO(info, many=True)
return Response({
'connection': con_name,
'person': serializers.data,
'dic': dic,
'assigend': assigned['person'],
'custom': custom_com['con_room'],
'multi': custom_com['multi']
})
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
elif args == "basename":
try:
con_id = request.data['id']
except KeyError as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
else:
_connection = DatabaseList.objects.filter(id=con_id).first()
try:
with con_database.SQLgo(ip=_connection.ip,
user=_connection.username,
password=_connection.password,
port=_connection.port) as f:
res = f.baseItems(sql='show databases')
return Response(res)
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
elif args == 'tablename':
try:
data = json.loads(request.data['data'])
basename = data['basename']
con_id = request.data['id']
except KeyError as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
else:
_connection = DatabaseList.objects.filter(id=con_id).first()
try:
with con_database.SQLgo(ip=_connection.ip,
user=_connection.username,
password=_connection.password,
port=_connection.port,
db=basename) as f:
res = f.baseItems(sql='show tables')
return Response(res)
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
elif args == 'field':
try:
connection_info = json.loads(request.data['connection_info'])
table = connection_info['tablename']
basename = connection_info['basename']
con_id = request.data['id']
except KeyError as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
else:
try:
_connection = DatabaseList.objects.filter(
id=con_id).first()
with con_database.SQLgo(ip=_connection.ip,
user=_connection.username,
password=_connection.password,
port=_connection.port,
db=basename) as f:
res = f.gen_alter(table_name=table)
return Response(res)
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
elif args == 'indexdata':
try:
login = json.loads(request.data['login'])
table = request.data['table']
basename = login['basename']
con_id = request.data['id']
except KeyError as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
else:
try:
_connection = DatabaseList.objects.filter(
id=con_id).first()
with con_database.SQLgo(ip=_connection.ip,
user=_connection.username,
password=_connection.password,
port=_connection.port,
db=basename) as f:
res = f.index(table_name=table)
return Response(res)
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(e)
def put(self, request, args=None):
if args == 'connection':
try:
assigned = set_auth_group(request.user)
un_init = util.init_conf()
custom_com = ast.literal_eval(un_init['other'])
print("user:"******"basename":
try:
con_id = request.data['id']
except KeyError as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
else:
_connection = DatabaseList.objects.filter(id=con_id).first()
try:
with con_database.SQLgo(ip=_connection.ip,
user=_connection.username,
password=_connection.password,
port=_connection.port) as f:
res = f.baseItems(sql='show databases')
exclude_db = serachsql.exclued_db_list()
for db in exclude_db:
if db in res:
res.remove(db)
return Response(res)
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
elif args == 'tablename':
try:
data = json.loads(request.data['data'])
basename = data['basename']
con_id = request.data['id']
except KeyError as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
else:
_connection = DatabaseList.objects.filter(id=con_id).first()
try:
with con_database.SQLgo(ip=_connection.ip,
user=_connection.username,
password=_connection.password,
port=_connection.port,
db=basename) as f:
res = f.baseItems(sql='show tables')
return Response(res)
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
elif args == 'field':
try:
connection_info = json.loads(request.data['connection_info'])
table = connection_info['tablename']
basename = connection_info['basename']
con_id = request.data['id']
except KeyError as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
else:
try:
_connection = DatabaseList.objects.filter(
id=con_id).first()
with con_database.SQLgo(ip=_connection.ip,
user=_connection.username,
password=_connection.password,
port=_connection.port,
db=basename) as f:
field = f.gen_alter(table_name=table)
idx = f.index(table_name=table)
return Response({'idx': idx, 'field': field})
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
def post(self, request, args=None):
un_init = util.init_conf()
limit = ast.literal_eval(un_init['other'])
sql = request.data['sql']
check = str(sql).lower().strip().split(';\n')
raw_sql = str(sql).strip().split(';\n')[-1]
user = query_order.objects.filter(
username=request.user).order_by('-id').first()
un_init = util.init_conf()
custom_com = ast.literal_eval(un_init['other'])
critical = len(custom_com['sensitive_list'])
if user.query_per == 1:
if check[-1].startswith('s') is not True:
return Response('请勿使用非查询语句,请删除不必要的空白行！')
else:
address = json.loads(request.data['address'])
_c = DatabaseList.objects.filter(
connection_name=user.connection_name,
computer_room=user.computer_room).first()
with con_database.SQLgo(ip=_c.ip,
password=_c.password,
user=_c.username,
port=_c.port,
db=address['basename']) as f:
try:
if search.sql_parse(check[-1]):
return Response(
'语句中不得含有违禁关键字: update insert alter into for drop'
)
if check[-1].startswith('show'):
query_sql = raw_sql
else:
if limit.get('limit').strip() == '':
CUSTOM_ERROR.error('未设置全局最大limit值，系统自动设置为1000')
query_sql = replace_limit(raw_sql, 1000)
else:
query_sql = replace_limit(
raw_sql, limit.get('limit'))
data_set = f.search(sql=query_sql)
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(e)
else:
if critical:
as_list = search.sql_as_ex(
sql, custom_com['sensitive_list'])
if data_set['data']:
fe = []
for k, v in data_set['data'][0].items():
if isinstance(v, bytes):
fe.append(k)
for l in data_set['data']:
if len(fe) != 0:
for i in fe:
l[i] = 'blob字段为不可呈现类型'
for s in as_list:
l[s] = '********'
else:
if data_set['data']:
fe = []
for k, v in data_set['data'][0].items():
if isinstance(v, bytes):
fe.append(k)
if len(fe) != 0:
for l in data_set['data']:
for i in fe:
l[i] = 'blob字段为不可呈现类型'
querypermissions.objects.create(work_id=user.work_id,
username=request.user,
statements=query_sql)
return HttpResponse(
simplejson.dumps(data_set,
cls=DateEncoder,
bigint_as_string=True))
else:
return Response('非法请求,账号无查询权限！')
def __init__(self, to_addr=None):
self.to_addr = to_addr
un_init = util.init_conf()
self.email = ast.literal_eval(un_init['message'])
self.email_suffix_list = ast.literal_eval(util.init_conf().get('other', '')).get('email_suffix_list',[]) # 获取可以注册邮箱后缀
def get(self, request, args: str = None):
'''
:argument 审核页面数据展示请求接口
:param None
:return 数据条数, 数据
'''
try:
if request.GET.get('page') == 'n':
mess = request.GET.get('mess')
opt = request.GET.get('opt')
un_init = util.init_conf()
custom_com = ast.literal_eval(un_init['other'])
if opt == 'u' :
page_number = SqlOrder.objects.filter(username__contains = mess).count()
sql = (
f'select core_sqlorder.*,core_databaselist.connection_name,\n'
f'core_databaselist.computer_room from core_sqlorder\n'
f'INNER JOIN core_databaselist on\n'
f'core_sqlorder.bundle_id = core_databaselist.id where core_sqlorder.username like %s\n'
f'ORDER BY core_sqlorder.id desc;')
elif opt == 't':
page_number = SqlOrder.objects.filter(text__contains=mess).count()
sql = (
f'select core_sqlorder.*,core_databaselist.connection_name,\n'
f'core_databaselist.computer_room from core_sqlorder\n'
f'INNER JOIN core_databaselist on\n'
f'core_sqlorder.bundle_id = core_databaselist.id where core_sqlorder.text like %s\n'
f'ORDER BY core_sqlorder.id desc;')
else:
page_number = SqlOrder.objects.filter(work_id__contains = mess).count()
sql = (
f'select core_sqlorder.*,core_databaselist.connection_name,\n'
f'core_databaselist.computer_room from core_sqlorder\n'
f'INNER JOIN core_databaselist on\n'
f'core_sqlorder.bundle_id = core_databaselist.id where core_sqlorder.work_id like %s\n'
f'ORDER BY core_sqlorder.id desc;')
# //where core_sqlorder.work_id like '%'''+mess+'''%' ORDER BY core_sqlorder.id desc '''
info = SqlOrder.objects.raw(sql, ["%"+mess+"%"])
data = util.ser(info)
info = Account.objects.filter(group='perform').all()
ser = serializers.UserINFO(info, many=True)
return Response(
{'page': page_number, 'data': data, 'multi': custom_com['multi'], 'multi_list': ser.data})
else:
try:
page = request.GET.get('page')
username = request.GET.get('username')
except KeyError as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
else:
try:
un_init = util.init_conf()
custom_com = ast.literal_eval(un_init['other'])
page_number = SqlOrder.objects.filter(assigned=username).count()
start = (int(page) - 1) * 20
end = int(page) * 20
info = SqlOrder.objects.raw(
'''
select core_sqlorder.*,core_databaselist.connection_name, \
core_databaselist.computer_room from core_sqlorder \
INNER JOIN core_databaselist on \
core_sqlorder.bundle_id = core_databaselist.id where core_sqlorder.assigned = '%s'\
ORDER BY core_sqlorder.id desc
''' % username
)[start:end]
data = util.ser(info)
info = Account.objects.filter(group='perform').all()
ser = serializers.UserINFO(info, many=True)
return Response(
{'page': page_number, 'data': data, 'multi': custom_com['multi'], 'multi_list': ser.data})
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
except KeyError as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
def put(self, request, args=None):
if args == 'changepwd':
try:
un_init = util.init_conf()
limit = ast.literal_eval(un_init['other'])
expire_days = limit.get('expire_days') if limit.get('expire_days') is not None else 90
username = request.data['username']
new_password = request.data['new']
except KeyError as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
else:
try:
user = Account.objects.get(username__exact=username)
user.set_password(new_password)
user.expire_date = (datetime.now() + timedelta(days=expire_days)).isoformat()
user.save()
return Response('%s--密码修改成功!' % username)
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
elif args == 'changegroup':
try:
username = request.data['username']
group = request.data['group']
department = request.data['department']
permission = json.loads(request.data['permission'])
brfore = Account.objects.filter(username=username).first()
except KeyError as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
else:
try:
if brfore.group == 'admin' and group == 'guest':
per = grained.objects.all().values('username', 'permissions')
for i in per:
for c in i['permissions']:
if isinstance(i['permissions'][c], list) and c == 'person':
i['permissions'][c] = list(filter(lambda x: x != username, i['permissions'][c]))
grained.objects.filter(username=i['username']).update(permissions=i['permissions'])
grained.objects.filter(username=username).update(permissions=permission)
if group == 'admin' or group == 'perform':
Account.objects.filter(username=username).update(
group=group,
department=department,
is_staff=1
)
else:
Account.objects.filter(username=username).update(
group=group,
department=department,
is_staff=0
)
return Response('%s--权限修改成功!' % username)
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
elif args == 'changemail':
try:
username = request.data['username']
mail = request.data['mail']
except KeyError as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
else:
try:
Account.objects.filter(username=username).update(email=mail)
return Response('%s--E-mail修改成功!' % username)
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
def post(self, request):
try:
data = request.data
if data['mongo_text'] == 'test':
try:
client = pymongo.MongoClient(data['mongo_url'])
db = client.test
databases = client.list_database_names()
if db:
return Response({
'ok': 'Mongo连接测试,成功!！',
'databases': databases
})
else:
return Response({'error': 'Mongo连接测试,失败！'})
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return Response({'error': 'Mongo连接测试失败:' + e.args[0]})
elif data['mongo_text'] == 'get_collections':
client = pymongo.MongoClient(data['mongo_url'])
db_name = data['mongo_db']
db = client[db_name]
res1 = db.collection_names()
if res1:
return Response({'data': res1})
else:
return Response({'error': 'Mongo执行失败！'})
else:
try:
client = pymongo.MongoClient(data['mongo_url'])
db_name = data['mongo_db']
db = client[db_name]
res2 = []
a = 1
# 获取limit限制
un_init = util.init_conf()
limit_num = int(
ast.literal_eval(un_init['other'])['limit'])
for i in data['mongo_text'].strip().split(';'):
if i == '':
continue
if i.strip()[0:1] == '#':
continue
query_check = self.query_check(sql=i.strip())
if query_check['bad_query'] == False:
sql = query_check['filtered_sql']
collect = db[sql.split('.')[0]]
match = re.compile(r'[(](.*)[)]', re.S)
sql = re.findall(match, sql)[0]
if sql != '':
sql = json.loads(sql)
result = collect.find(sql).limit(limit_num)
else:
result = collect.find().limit(limit_num)
rows = json.loads(json_util.dumps(result))
# if isinstance(rows, list):
# rows = tuple([json.dumps(x, ensure_ascii=False)] for x in rows)
Mongo_ops_log.objects.get_or_create(
user=str(request.user),
mongo_instance='addr: ' + data['mongo_url'],
ops=i.strip(),
create_time=datetime.datetime.now().strftime(
'%Y-%m-%d %H:%M:%S'),
update_time=datetime.datetime.now().strftime(
'%Y-%m-%d %H:%M:%S'))
res2.append(
'## result:%d ########################################################'
% a)
res2.append(rows)
a = a + 1
else:
res2.append(
'## result:%d ########################################################'
% a)
res2.append(
"""禁止执行该命令！正确格式为：{collection_name}.find() or {collection_name}.find(expression) 如 : 'test.find({"id":{"$gt":1.0}})'"""
)
a = a + 1
if res2:
return Response({'data': res2})
else:
return Response({'error': 'Mongo执行失败！'})
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return Response({'error': 'Mongo执行失败:' + e.args[0]})
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
def post(self, request, args=None):
un_init = util.init_conf()
limit = ast.literal_eval(un_init['other'])
sql = request.data['sql'].replace('\n', ' ').strip()
if sql[-1] != ';':
sql += ';'
check = str(sql).strip().split(';')
v_sql = check[-2]
if check[0].startswith('explain') and len(check) > 2:
v_sql = 'explain ' + check[-2]
# user = query_order.objects.filter(username=request.user).order_by('-id').first()
un_init = util.init_conf()
custom_com = ast.literal_eval(un_init['other'])
# if user.query_per == 1:
if True:
if v_sql.strip().lower().startswith('explain') or v_sql.strip().lower().startswith('select') == 1 \
or v_sql.strip().lower().startswith('desc') == 1 or v_sql.strip().lower().startswith('show') == 1 :
address = json.loads(request.data['address'])
_c = DatabaseList.objects.filter(
connection_name=address['connection_name'],
computer_room=address['computer_room']).first()
try:
with con_database.SQLgo(ip=_c.ip,
password=_c.password,
user=_c.username,
port=_c.port,
db=address['basename']) as f:
query_sql = replace_limit(v_sql.strip(),
limit['limit'])
data_set = f.search(sql=query_sql)
for l in data_set['data']:
for k, v in l.items():
# if isinstance(v, bytes):
# for n in range(data_set['len']):
# data_set['data'][n].update({k: 'blob字段为不可呈现类型'})
for i in custom_com['sensitive_list']:
if k == i:
for n in range(data_set['len']):
data_set['data'][n].update(
{k: '********'})
db_info = address
querypermissions.objects.create(
work_id=datetime.datetime.now().strftime(
'%Y-%m-%d %H:%M:%S'),
username=request.user,
statements=query_sql,
db_info=db_info)
data_set['query_time'] = f.cost
def default(o):
if isinstance(o, datetime.timedelta):
t = (datetime.datetime.min + o).time()
return t.isoformat()
if isinstance(o, datetime.datetime):
return str(o.date()) + ' ' + str(o.time())
# return o.isoformat()
return HttpResponse(
simplejson.dumps(data_set,
cls=DateEncoder,
bigint_as_string=True,
default=default))
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return Response({'error': e.args[1]})
else:
return Response({'error': '只支持查询功能或删除不必要的空白行！'})
else:
return Response({'error': '已超过申请时限请刷新页面后重新提交申请'})
def put(self, request, args=None):
if args == 'connection':
try:
assigned = grained.objects.filter(username=request.user).first()
un_init = util.init_conf()
custom_com = ast.literal_eval(un_init['other'])
if request.data['permissions_type'] == 'user' or request.data['permissions_type'] == 'own_space':
info = DatabaseList.objects.all()
con_name = Area(info, many=True).data
dic = SqlDictionary.objects.all().values('Name')
dic.query.distinct = ['Name']
elif request.data['permissions_type'] == 'query':
con_name = []
permission_spec = grained.objects.filter(username=request.user).first()
if permission_spec.permissions['query'] == '1':
for i in permission_spec.permissions['querycon']:
con_instance = DatabaseList.objects.filter(connection_name=i).first()
if con_instance:
con_name.append(
{
'id': con_instance.id,
'connection_name': con_instance.connection_name,
'ip': con_instance.ip ,
'computer_room': con_instance.computer_room
})
assigned = grained.objects.filter(username=request.user).first()
return Response({'assigend': assigned.permissions['person'], 'connection': con_name,
'custom': custom_com['con_room']})
else:
con_name = []
_type = request.data['permissions_type'] + 'con'
permission_spec = grained.objects.filter(username=request.user).first()
for i in permission_spec.permissions[_type]:
con_instance = DatabaseList.objects.filter(connection_name=i).first()
if con_instance:
con_name.append(
{
'id': con_instance.id,
'connection_name': con_instance.connection_name,
'ip': con_instance.ip,
'computer_room': con_instance.computer_room
})
dic = ''
info = Account.objects.filter(group='admin').all()
serializers = UserINFO(info, many=True)
return Response(
{
'connection': con_name,
'person': serializers.data,
'dic': dic,
'assigend': assigned.permissions['person'],
'custom': custom_com['con_room'],
'multi': custom_com['multi']
}
)
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
elif args == "basename":
try:
con_id = request.data['id']
except KeyError as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
else:
_connection = DatabaseList.objects.filter(id=con_id).first()
try:
with con_database.SQLgo(
ip=_connection.ip,
user=_connection.username,
password=_connection.password,
port=_connection.port
) as f:
res = f.basename()
return Response(res)
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
elif args == 'tablename':
try:
data = json.loads(request.data['data'])
basename = data['basename']
con_id = request.data['id']
except KeyError as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
else:
_connection = DatabaseList.objects.filter(id=con_id).first()
try:
with con_database.SQLgo(
ip=_connection.ip,
user=_connection.username,
password=_connection.password,
port=_connection.port,
db=basename
) as f:
res = f.tablename()
return Response(res)
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
elif args == 'field':
try:
connection_info = json.loads(request.data['connection_info'])
table = connection_info['tablename']
basename = connection_info['basename']
con_id = request.data['id']
except KeyError as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
else:
try:
_connection = DatabaseList.objects.filter(id=con_id).first()
with con_database.SQLgo(
ip=_connection.ip,
user=_connection.username,
password=_connection.password,
port=_connection.port,
db=basename
) as f:
res = f.gen_alter(table_name=table)
return Response(res)
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
elif args == 'indexdata':
try:
login = json.loads(request.data['login'])
table = request.data['table']
basename = login['basename']
con_id = request.data['id']
except KeyError as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
else:
try:
_connection = DatabaseList.objects.filter(id=con_id).first()
with con_database.SQLgo(
ip=_connection.ip,
user=_connection.username,
password=_connection.password,
port=_connection.port,
db=basename
) as f:
res = f.index(table_name=table)
return Response(res)
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return Response(e)
def put(self, request, args: str = None):
if request.data['mode'] == 'put':
instructions = request.data['instructions']
connection_name = request.data['connection_name']
computer_room = request.data['computer_room']
timer = int(request.data['timer'])
export = request.data['export']
audit = request.data['audit']
un_init = util.init_conf()
query_switch = ast.literal_eval(un_init['other'])
query_per = 2
work_id = util.workId()
if not query_switch['query']:
query_per = 1
userinfo = Account.objects.filter(username=audit, group='admin').first()
thread = threading.Thread(
target=push_message,
args=({'to_user': request.user, 'workid': work_id}, 5, request.user, userinfo.email, work_id, '提交')
)
thread.start()
query_order.objects.create(
work_id=work_id,
instructions=instructions,
username=request.user,
timer=timer,
date=util.date(),
query_per=query_per,
connection_name=connection_name,
computer_room=computer_room,
export=export,
audit=audit,
time=util.date()
)
if not query_switch['query']:
t = threading.Thread(target=query_worklf.query_callback, args=(timer, work_id))
t.start()
## 钉钉及email站内信推送
return Response('查询工单已提交，等待管理员审核！')
elif request.data['mode'] == 'agree':
work_id = request.data['work_id']
query_info = query_order.objects.filter(work_id=work_id).order_by('-id').first()
t = threading.Thread(target=query_worklf.query_callback, args=(query_info.timer, work_id))
t.start()
userinfo = Account.objects.filter(username=query_info.username).first()
thread = threading.Thread(target=push_message, args=(
{'to_user': query_info.username, 'workid': query_info.work_id}, 6, query_info.username, userinfo.email,
work_id, '同意'))
thread.start()
return Response('查询工单状态已更新！')
elif request.data['mode'] == 'disagree':
work_id = request.data['work_id']
query_order.objects.filter(work_id=work_id).update(query_per=0)
query_info = query_order.objects.filter(work_id=work_id).order_by('-id').first()
userinfo = Account.objects.filter(username=query_info.username).first()
thread = threading.Thread(target=push_message, args=(
{'to_user': query_info.username, 'workid': query_info.work_id}, 7, query_info.username, userinfo.email,
work_id, '驳回'))
thread.start()
return Response('查询工单状态已更新！')
elif request.data['mode'] == 'status':
try:
status = query_order.objects.filter(username=request.user).order_by('-id').first()
return Response(status.query_per)
except:
return Response(0)
elif request.data['mode'] == 'end':
try:
query_order.objects.filter(username=request.user).order_by('-id').update(query_per=3)
return Response('结束查询工单成功！')
except Exception as e:
return Response(e)
elif request.data['mode'] == 'info':
tablelist = []
database = query_order.objects.filter(username=request.user).order_by('-id').first()
_connection = DatabaseList.objects.filter(connection_name=database.connection_name).first()
with con_database.SQLgo(ip=_connection.ip,
user=_connection.username,
password=_connection.password,
port=_connection.port) as f:
dataname = f.query_info(sql='show databases')
children = []
ignore = ['information_schema', 'sys', 'performance_schema', 'mysql']
for index, uc in enumerate(dataname):
for cc in ignore:
if uc['Database'] == cc:
del dataname[index]
index = index - 1
for i in dataname:
with con_database.SQLgo(ip=_connection.ip,
user=_connection.username,
password=_connection.password,
port=_connection.port,
db=i['Database']) as f:
tablename = f.query_info(sql='show tables')
for c in tablename:
key = 'Tables_in_%s' % i['Database']
children.append({
'title': c[key]
})
tablelist.append({
'title': i['Database'],
'children': children
})
children = []
data = [{
'title': database.connection_name,
'expand': 'true',
'children': tablelist
}]
return Response({'info': json.dumps(data), 'status': database.export})
def put(self, request, args: str = None):
if request.data['mode'] == 'put':
instructions = request.data['instructions']
connection_name = request.data['connection_name']
computer_room = request.data['computer_room']
timer = int(request.data['timer'])
export = request.data['export']
audit = request.data['audit']
un_init = util.init_conf()
query_switch = ast.literal_eval(un_init['other'])
query_per = 2
work_id = util.workId()
if not query_switch['query']:
query_per = 1
else:
userinfo = Account.objects.filter(username=audit,
group='admin').first()
try:
thread = threading.Thread(target=push_message,
args=({
'to_user': request.user,
'workid': work_id
}, 5, request.user,
userinfo.email, work_id,
'提交'))
thread.start()
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
# 最大可申请7天权限
timer = 168 if timer > 168 else timer
query_order.objects.create(
work_id=work_id,
instructions=instructions,
username=request.user,
timer=timer,
date=datetime.datetime.now() + datetime.timedelta(hours=timer),
query_per=query_per,
connection_name=','.join(connection_name),
computer_room=computer_room,
export=export,
audit=audit,
time=util.datetime())
if not query_switch['query']:
t = threading.Thread(target=query_worklf.query_callback,
args=(timer, work_id))
t.start()
## 钉钉及email站内信推送
return Response('查询工单已提交，等待管理员审核！')
elif request.data['mode'] == 'agree':
work_id = request.data['work_id']
query_info = query_order.objects.filter(
work_id=work_id).order_by('-id').first()
t = threading.Thread(target=query_worklf.query_callback,
args=(query_info.timer, work_id))
t.start()
userinfo = Account.objects.filter(
username=query_info.username).first()
thread = threading.Thread(target=push_message,
args=({
'to_user': query_info.username,
'workid': query_info.work_id
}, 6, query_info.username,
userinfo.email, work_id, '同意'))
thread.start()
return Response('查询工单状态已更新！')
elif request.data['mode'] == 'disagree':
work_id = request.data['work_id']
query_order.objects.filter(work_id=work_id).update(query_per=0)
query_info = query_order.objects.filter(
work_id=work_id).order_by('-id').first()
userinfo = Account.objects.filter(
username=query_info.username).first()
thread = threading.Thread(target=push_message,
args=({
'to_user': query_info.username,
'workid': query_info.work_id
}, 7, query_info.username,
userinfo.email, work_id, '驳回'))
thread.start()
return Response('查询工单状态已更新！')
elif request.data['mode'] == 'status':
try:
limit = util.get_conf_limit()
curr_time = util.datetime()
status = query_order.objects.filter(
username=request.user).filter(query_per=1).filter(
date__gt=curr_time).aggregate(count=Count('id'))
if status['count'] > 0:
return Response({'limit': limit, 'status': 1})
else:
return Response({'limit': limit, 'status': 0})
except:
return Response(0)
elif request.data['mode'] == 'end':
try:
query_order.objects.filter(
username=request.user).order_by('-id').update(query_per=3)
return Response('结束查询工单成功！')
except Exception as e:
return Response(e)
elif request.data['mode'] == 'dblist':
dbname_list = get_dblist_foruser(request.user)
return Response(dbname_list)
elif request.data['mode'] == 'tablist':
dbname = request.data['dbname']
_connection = DatabaseList.objects.filter(
connection_name=dbname).first()
with con_database.SQLgo(ip=_connection.ip,
user=_connection.username,
password=_connection.password,
port=_connection.port,
db=_connection.dbname) as f:
tabname = f.query_info(sql='show tables')
return Response([list(i.values())[0] for i in tabname])
elif request.data['mode'] == 'tabmeta':
dbname = request.data['dbname']
tabname = request.data['tabname']
get_tabmeta_sql = ("show create table {0}".format(tabname))
_connection = DatabaseList.objects.filter(
connection_name=dbname).first()
with con_database.SQLgo(ip=_connection.ip,
user=_connection.username,
password=_connection.password,
port=_connection.port,
db=_connection.dbname) as f:
tabmeta = f.query_info(sql=get_tabmeta_sql)
try:
tabmeta_deal = tabmeta[0]["Create Table"]
return Response(tabmeta_deal)
except Exception:
return Response('')
elif request.data['mode'] == 'addcollect':
try:
user_collect.objects.create(
username=request.user,
connect_name=request.data["conn_name"],
sql_text=request.data["sql_text"],
collect_alias=request.data["alias_name"],
create_time=util.datetime(),
update_time=util.datetime())
return Response('ok')
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
elif request.data['mode'] == 'getcollect':
try:
my_collect = user_collect.objects.filter(
username=request.user).order_by("-sort_flag").order_by(
"-update_time")
data_dict = [
{
'title': "别名",
"key": "collect_alias",
"fixed": "left",
"sortable": "true",
"width": 150
},
{
'title': "连接名",
"key": "connect_name",
"fixed": "left",
"sortable": "true",
"width": 150
},
{
'title': "sql",
"key": "sql_text",
"fixed": "left",
"sortable": "true",
"width": 265
},
]
data_result = []
len = 0
for i in my_collect:
len = len + 1
data_result.append({
"id": i.id,
"collect_alias": i.collect_alias,
"connect_name": i.connect_name,
"sql_text": i.sql_text
})
collect_fin = {
'data': data_result,
'title': data_dict,
'len': len
}
return HttpResponse(
simplejson.dumps(collect_fin,
cls=DateEncoder,
bigint_as_string=True))
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
elif request.data['mode'] == 'delcollect':
try:
user_collect.objects.filter(id=request.data["id"]).delete()
return Response('ok')
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
elif request.data['mode'] == 'setcollecttop':
try:
user_collect.objects.filter(id=request.data["id"]).update(
sort_flag=999, update_time=util.datetime())
return Response('ok')
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
elif request.data['mode'] == 'info':
tablelist = []
database_list_from_db = query_order.objects.filter(
username=request.user).filter(query_per=1)
database_list = []
dbname_list = []
for row in database_list_from_db:
for i in row.connection_name.split(","):
if i not in dbname_list:
dbname_list.append(i)
database_list.append({
"connection_name": i,
"export": row.export
})
data = []
for database in database_list:
_connection = DatabaseList.objects.filter(
connection_name=database["connection_name"]).first()
with con_database.SQLgo(ip=_connection.ip,
user=_connection.username,
password=_connection.password,
port=_connection.port) as f:
dataname = f.query_info(sql='show databases')
children = []
ignore = [
'information_schema', 'sys', 'performance_schema', 'mysql'
]
for index, uc in enumerate(dataname):
for cc in ignore:
if uc['Database'] == cc:
del dataname[index]
index = index - 1
for i in dataname:
with con_database.SQLgo(ip=_connection.ip,
user=_connection.username,
password=_connection.password,
port=_connection.port,
db=i['Database']) as f:
tablename = f.query_info(sql='show tables')
for c in tablename:
key = 'Tables_in_%s' % i['Database']
children.append({'title': c[key]})
tablelist.append({
'title': i['Database'],
'children': children
})
children = []
data.append({
'title': database,
'expand': 'true',
'children': tablelist
})
return Response({
'info': json.dumps(data),
'status': database["export"]
})
def execute(self):
'''
:argument 将获得的sql语句提交给inception执行并将返回结果写入SqlRecord表,最后更改该工单SqlOrder表中的status
:param
self.order
self.id
:return: none
'''
if self.order.type != 2:
try:
detail = DatabaseList.objects.filter(
id=self.order.bundle_id).first()
with call_inception.Inception(
LoginDic={
'host': detail.ip,
'user': detail.username,
'password': detail.password,
'db': self.order.basename,
'port': detail.port
}) as f:
res = f.Execute(sql=self.order.sql,
backup=self.order.backup)
for i in res:
if i['errlevel'] != 0:
SqlOrder.objects.filter(
work_id=self.order.work_id).update(status=4)
SqlRecord.objects.get_or_create(
state=i['stagestatus'],
sql=i['sql'],
error=i['errormessage'],
workid=self.order.work_id,
affectrow=i['affected_rows'],
sequence=i['sequence'],
execute_time=i['execute_time'],
SQLSHA1=i['SQLSHA1'],
backup_dbname=i['backup_dbname'])
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
finally:
status = SqlOrder.objects.filter(
work_id=self.order.work_id).first()
if status.status != 4:
SqlOrder.objects.filter(id=self.id).update(status=1)
else:
# dql 操作
un_init = util.init_conf()
limit = ast.literal_eval(un_init['other'])
sql = self.order.sql
check = str(self.order.sql).lower().strip().split(';\n')
raw_sql = str(self.order.sql).strip().split(';\n')[-1]
un_init = util.init_conf()
custom_com = ast.literal_eval(un_init['other'])
critical = len(custom_com['sensitive_list'])
# 操作实例
detail = DatabaseList.objects.filter(
id=self.order.bundle_id).first()
with con_database.SQLgo(ip=detail.ip,
password=detail.password,
user=detail.username,
port=detail.port,
db=self.order.basename) as f:
try:
if check[-1].startswith('show'):
query_sql = raw_sql
else:
if limit.get('limit').strip() == '':
CUSTOM_ERROR.error('未设置全局最大limit值，系统自动设置为1000')
query_sql = replace_limit(raw_sql, 1000)
else:
query_sql = replace_limit(raw_sql,
limit.get('limit'))
data_set = f.search(sql=query_sql)
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
else:
if self.order.sensitive:
as_list = sql_as_ex(sql, custom_com['sensitive_list'])
if data_set['data']:
fe = []
for k, v in data_set['data'][0].items():
if isinstance(v, bytes):
fe.append(k)
for l in data_set['data']:
if len(fe) != 0:
for i in fe:
l[i] = 'blob字段为不可呈现类型'
for s in as_list:
if l.get(s):
if s == "email":
pattern = re.compile(r"(.*)@(.*)")
res = re.findall(pattern, l[s])
if len(res) != 0:
l[s] = res[0][0] + "*****"
elif s == "phone":
pattern = re.compile(
r"(.{3})(.*)(.{4})")
res = re.findall(pattern, l[s])
if len(res) != 0:
l[s] = res[0][
0] + "*****" + res[0][-1]
elif s == "idno":
pattern = re.compile(
r"(.*)(.{4})$")
res = re.findall(pattern, l[s])
if len(res) != 0:
l[s] = res[0][0] + "*****"
else:
l[s] = l[s][:3] + "****" + l[s][-3:]
else:
continue
else:
if data_set['data']:
fe = []
for k, v in data_set['data'][0].items():
if isinstance(v, bytes):
fe.append(k)
if len(fe) != 0:
for l in data_set['data']:
for i in fe:
l[i] = 'blob字段为不可呈现类型'
if len(data_set["data"]) > 200:
from settingConf.settings import MEDIA_ROOT
filename, status = file_help.save_file(
data_set, MEDIA_ROOT)
if status:
querypermissions.objects.create(
work_id=self.order.work_id,
username=self.order.username,
statements=query_sql,
filename=filename,
answer={
"data": [],
"title": [],
"len": ""
})
else:
CUSTOM_ERROR.error("***file save fail***",
filename)
else:
querypermissions.objects.create(
work_id=self.order.work_id,
username=self.order.username,
statements=query_sql,
answer=json.dumps(data_set))
SqlOrder.objects.filter(work_id=self.order.work_id).update(
status=1)
def __init__(self, to_addr=None):
self.to_addr = to_addr
un_init = util.init_conf()
self.email = ast.literal_eval(un_init['message'])
def put(self, request, args: str = None):
if request.data['mode'] == 'put':
instructions = request.data['instructions']
connection_name = request.data['connection_name']
computer_room = request.data['computer_room']
timer = int(request.data['timer'])
export = request.data['export']
audit = request.data['audit']
un_init = util.init_conf()
query_switch = ast.literal_eval(un_init['other'])
query_per = 2
work_id = util.workId()
if not query_switch['query']:
query_per = 1
userinfo = Account.objects.filter(username=audit,
group='admin').first()
thread = threading.Thread(target=push_message,
args=({
'to_user': request.user,
'workid': work_id
}, 5, request.user, userinfo.email,
work_id, '提交'))
thread.start()
query_order.objects.create(work_id=work_id,
instructions=instructions,
username=request.user,
timer=timer,
date=util.date(),
query_per=query_per,
connection_name=connection_name,
computer_room=computer_room,
export=export,
audit=audit,
time=util.date())
if not query_switch['query']:
t = threading.Thread(target=query_worklf.query_callback,
args=(timer, work_id))
t.start()
## 钉钉及email站内信推送
return Response('查询工单已提交，等待管理员审核！')
elif request.data['mode'] == 'agree':
work_id = request.data['work_id']
query_info = query_order.objects.filter(
work_id=work_id).order_by('-id').first()
t = threading.Thread(target=query_worklf.query_callback,
args=(query_info.timer, work_id))
t.start()
userinfo = Account.objects.filter(
username=query_info.username).first()
thread = threading.Thread(target=push_message,
args=({
'to_user': query_info.username,
'workid': query_info.work_id
}, 6, query_info.username,
userinfo.email, work_id, '同意'))
thread.start()
return Response('查询工单状态已更新！')
elif request.data['mode'] == 'disagree':
work_id = request.data['work_id']
query_order.objects.filter(work_id=work_id).update(query_per=0)
query_info = query_order.objects.filter(
work_id=work_id).order_by('-id').first()
userinfo = Account.objects.filter(
username=query_info.username).first()
thread = threading.Thread(target=push_message,
args=({
'to_user': query_info.username,
'workid': query_info.work_id
}, 7, query_info.username,
userinfo.email, work_id, '驳回'))
thread.start()
return Response('查询工单状态已更新！')
elif request.data['mode'] == 'status':
try:
status = query_order.objects.filter(
username=request.user).order_by('-id').first()
return Response(status.query_per)
except:
return Response(0)
elif request.data['mode'] == 'end':
try:
query_order.objects.filter(
username=request.user).order_by('-id').update(query_per=3)
return Response('结束查询工单成功！')
except Exception as e:
return Response(e)
elif request.data['mode'] == 'info':
tablelist = []
database = query_order.objects.filter(
username=request.user).order_by('-id').first()
_connection = DatabaseList.objects.filter(
connection_name=database.connection_name).first()
with con_database.SQLgo(ip=_connection.ip,
user=_connection.username,
password=_connection.password,
port=_connection.port) as f:
dataname = f.query_info(sql='show databases')
children = []
ignore = [
'information_schema', 'sys', 'performance_schema', 'mysql'
]
for index, uc in enumerate(dataname):
for cc in ignore:
if uc['Database'] == cc:
del dataname[index]
index = index - 1
for i in dataname:
with con_database.SQLgo(ip=_connection.ip,
user=_connection.username,
password=_connection.password,
port=_connection.port,
db=i['Database']) as f:
tablename = f.query_info(sql='show tables')
for c in tablename:
key = 'Tables_in_%s' % i['Database']
children.append({'title': c[key]})
tablelist.append({
'title': i['Database'],
'children': children
})
children = []
data = [{
'title': database.connection_name,
'expand': 'true',
'children': tablelist
}]
return Response({
'info': json.dumps(data),
'status': database.export
})
def put(self, request, args: str = None):
if request.data['mode'] == 'put':
instructions = request.data['instructions']
connection_name = request.data['connection_name']
computer_room = request.data['computer_room']
real = request.data['real_name']
export = request.data['export']
audit = request.data['audit']
un_init = util.init_conf()
query_switch = ast.literal_eval(un_init['other'])
query_per = 2
work_id = util.workId()
if not query_switch['query']:
query_per = 2
else:
userinfo = Account.objects.filter(username=audit, group='admin').first()
try:
thread = threading.Thread(
target=push_message,
args=(
{'to_user': request.user, 'workid': work_id}, 5, request.user, userinfo.email, work_id,
'提交')
)
thread.start()
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
query_order.objects.create(
work_id=work_id,
instructions=instructions,
username=request.user,
date=util.date(),
query_per=query_per,
connection_name=connection_name,
computer_room=computer_room,
export=export,
audit=audit,
time=util.date(),
real_name=real
)
if not query_switch['query']:
query_order.objects.filter(work_id=work_id).update(query_per=1)
## 钉钉及email站内信推送
return Response('查询工单已提交，等待管理员审核！')
elif request.data['mode'] == 'agree':
work_id = request.data['work_id']
query_info = query_order.objects.filter(work_id=work_id).order_by('-id').first()
query_order.objects.filter(work_id=work_id).update(query_per=1)
userinfo = Account.objects.filter(username=query_info.username).first()
try:
thread = threading.Thread(target=push_message, args=(
{'to_user': query_info.username, 'workid': query_info.work_id}, 6, query_info.username,
userinfo.email,
work_id, '同意'))
thread.start()
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return Response('查询工单状态已更新！')
elif request.data['mode'] == 'disagree':
work_id = request.data['work_id']
query_order.objects.filter(work_id=work_id).update(query_per=0)
query_info = query_order.objects.filter(work_id=work_id).order_by('-id').first()
userinfo = Account.objects.filter(username=query_info.username).first()
try:
thread = threading.Thread(target=push_message, args=(
{'to_user': query_info.username, 'workid': query_info.work_id}, 7, query_info.username,
userinfo.email,
work_id, '驳回'))
thread.start()
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return Response('查询工单状态已更新！')
elif request.data['mode'] == 'status':
try:
status = query_order.objects.filter(username=request.user).order_by('-id').first()
return Response(status.query_per)
except:
return Response(0)
elif request.data['mode'] == 'end':
try:
query_order.objects.filter(username=request.data['username']).order_by('-id').update(query_per=3)
return Response('已结束查询！')
except Exception as e:
return HttpResponse(e)
elif request.data['mode'] == 'info':
tablelist = []
highlist = []
database = query_order.objects.filter(username=request.user).order_by('-id').first()
_connection = DatabaseList.objects.filter(connection_name=database.connection_name).first()
with con_database.SQLgo(ip=_connection.ip,
user=_connection.username,
password=_connection.password,
port=_connection.port) as f:
dataname = f.query_info(sql='show databases')
children = []
ignore = exclued_db_list()
for index, uc in sorted(enumerate(dataname), reverse=True):
for cc in ignore:
if uc['Database'] == cc:
del dataname[index]
for i in dataname:
with con_database.SQLgo(ip=_connection.ip,
user=_connection.username,
password=_connection.password,
port=_connection.port,
db=i['Database']) as f:
tablename = f.query_info(sql='show tables')
highlist.append({'vl': i['Database'], 'meta': '库名'})
for c in tablename:
key = 'Tables_in_%s' % i['Database']
highlist.append({'vl': c[key], 'meta': '表名'})
children.append({
'title': c[key]
})
tablelist.append({
'title': i['Database'],
'children': children
})
children = []
data = [{
'title': database.connection_name,
'expand': 'true',
'children': tablelist
}]
return Response({'info': json.dumps(data), 'status': database.export, 'highlight': highlist})
def ding_url():
un_init = util.init_conf()
webhook = ast.literal_eval(un_init['message'])
return webhook['webhook']
def put(self, request, args=None):
if args == 'connection':
try:
assigned = grained.objects.filter(
username=request.user).first()
last_query = querypermissions.objects.filter(
username=request.user).order_by('-id').first()
un_init = util.init_conf()
custom_com = ast.literal_eval(un_init['other'])
if request.data['permissions_type'] == 'user' or request.data[
'permissions_type'] == 'own_space':
info = DatabaseList.objects.all()
con_name = Area(info, many=True).data
dic = SqlDictionary.objects.all().values('Name')
dic.query.distinct = ['Name']
# elif request.data['permissions_type'] == 'query':
# con_name = []
# permission_spec = grained.objects.filter(username=request.user).first()
# if permission_spec.permissions['query'] == '1':
# for i in permission_spec.permissions['querycon']:
# con_instance = DatabaseList.objects.filter(connection_name=i).first()
# if con_instance:
# con_name.append(
# {
# 'id': con_instance.id,
# 'connection_name': con_instance.connection_name,
# 'ip': con_instance.ip ,
# 'computer_room': con_instance.computer_room
# })
# assigned = grained.objects.filter(username=request.user).first()
# return Response({'assigend': assigned.permissions['person'], 'connection': con_name,
# 'custom': custom_com['con_room']})
else:
con_name = []
_type = request.data['permissions_type'] + 'con'
permission_spec = grained.objects.filter(
username=request.user).first()
for i in permission_spec.permissions[_type]:
con_instance = DatabaseList.objects.filter(
connection_name=i).first()
if con_instance:
con_name.append({
'id':
con_instance.id,
'connection_name':
con_instance.connection_name,
'ip':
con_instance.ip,
'computer_room':
con_instance.computer_room
})
dic = ''
info = Account.objects.filter(group='admin').all()
serializers = UserINFO(info, many=True)
history = querypermissions.objects.filter(
username=request.user).order_by('-id')[0:20]
serializer_his = QueryPermissions(history, many=True)
return Response({
'connection':
con_name,
'person':
serializers.data,
'history':
serializer_his.data,
'dic':
dic,
'assigend':
assigned.permissions['person'],
'custom':
custom_com['con_room'],
'multi':
custom_com['multi'],
'limit_num':
custom_com['limit'],
'sql_display':
custom_com['sql_display'],
'last_query':
ast.literal_eval(last_query.db_info) if last_query else {},
'last_sql':
last_query.statements if last_query else ""
})
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
elif args == 'optimizer':
try:
assigned = grained.objects.filter(
username=request.user).first()
last_query = sql_optimize_his.objects.filter(
username=request.user).order_by('-id').first()
un_init = util.init_conf()
custom_com = ast.literal_eval(un_init['other'])
if request.data['permissions_type'] == 'user' or request.data[
'permissions_type'] == 'own_space':
info = DatabaseList.objects.all()
con_name = Area(info, many=True).data
dic = SqlDictionary.objects.all().values('Name')
dic.query.distinct = ['Name']
else:
con_name = []
_type = request.data['permissions_type'] + 'con'
permission_spec = grained.objects.filter(
username=request.user).first()
for i in permission_spec.permissions[_type]:
con_instance = DatabaseList.objects.filter(
connection_name=i).first()
if con_instance:
con_name.append({
'id':
con_instance.id,
'connection_name':
con_instance.connection_name,
'ip':
con_instance.ip,
'computer_room':
con_instance.computer_room
})
dic = ''
info = Account.objects.filter(group='admin').all()
serializers = UserINFO(info, many=True)
history = sql_optimize_his.objects.filter(
username=request.user).order_by('-id')[0:10]
serializer_his = Sql_Optimize_His(history, many=True)
return Response({
'connection':
con_name,
'person':
serializers.data,
'history':
serializer_his.data,
'dic':
dic,
'assigend':
assigned.permissions['person'],
'custom':
custom_com['con_room'],
'multi':
custom_com['multi'],
'limit_num':
custom_com['limit'],
'last_query':
ast.literal_eval(last_query.db_info) if last_query else {},
'last_sql':
last_query.statements if last_query else ""
})
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
elif args == "basename":
try:
con_id = request.data['id']
except KeyError as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
else:
_connection = DatabaseList.objects.filter(id=con_id).first()
try:
with con_database.SQLgo(ip=_connection.ip,
user=_connection.username,
password=_connection.password,
port=_connection.port) as f:
res = f.basename()
return Response(res)
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
elif args == "binlogs":
try:
con_id = request.data['id']
except KeyError as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
else:
_connection = DatabaseList.objects.filter(id=con_id).first()
try:
with con_database.SQLgo(ip=_connection.ip,
user=_connection.username,
password=_connection.password,
port=_connection.port) as f:
res = f.binlogs()
return Response(res)
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
elif args == "table_names":
try:
con_id = request.data['id']
db_name = request.data['db']
except KeyError as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
else:
_connection = DatabaseList.objects.filter(id=con_id).first()
try:
with con_database.SQLgo(ip=_connection.ip,
user=_connection.username,
password=_connection.password,
port=_connection.port,
db=db_name) as f:
res = f.table_names()
return Response(res)
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
elif args == "column_names":
try:
con_id = request.data['id']
db_name = request.data['db']
except KeyError as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
else:
_connection = DatabaseList.objects.filter(id=con_id).first()
try:
with con_database.SQLgo(ip=_connection.ip,
user=_connection.username,
password=_connection.password,
port=_connection.port,
db=db_name) as f:
res = f.column_names()
return Response(res)
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
elif args == 'tablename':
try:
data = json.loads(request.data['data'])
basename = data['basename']
con_id = request.data['id']
except KeyError as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
else:
_connection = DatabaseList.objects.filter(id=con_id).first()
try:
with con_database.SQLgo(ip=_connection.ip,
user=_connection.username,
password=_connection.password,
port=_connection.port,
db=basename) as f:
res = f.tablename()
return Response(res)
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
elif args == 'field':
try:
connection_info = json.loads(request.data['connection_info'])
table = connection_info['tablename']
basename = connection_info['basename']
con_id = request.data['id']
except KeyError as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
else:
try:
_connection = DatabaseList.objects.filter(
id=con_id).first()
with con_database.SQLgo(ip=_connection.ip,
user=_connection.username,
password=_connection.password,
port=_connection.port,
db=basename) as f:
res = f.gen_alter(table_name=table)
return Response(res)
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
elif args == 'indexdata':
try:
login = json.loads(request.data['login'])
table = request.data['table']
basename = login['basename']
con_id = request.data['id']
except KeyError as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
else:
try:
_connection = DatabaseList.objects.filter(
id=con_id).first()
with con_database.SQLgo(ip=_connection.ip,
user=_connection.username,
password=_connection.password,
port=_connection.port,
db=basename) as f:
res = f.index(table_name=table)
return Response(res)
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return Response(e)
elif args == 'binlog2sql':
try:
assigned = grained.objects.filter(
username=request.user).first()
un_init = util.init_conf()
custom_com = ast.literal_eval(un_init['other'])
if request.data['permissions_type'] == 'admin':
info = DatabaseList.objects.all()
con_name = Area(info, many=True).data
info = Account.objects.filter(group='admin').all()
serializers = UserINFO(info, many=True)
return Response({
'connection': con_name,
'person': serializers.data,
'assigend': assigned.permissions['person'],
'custom': custom_com['con_room'],
'sql_display': custom_com['sql_display'],
})
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
elif args == 'slowlog':
try:
assigned = grained.objects.filter(
username=request.user).first()
un_init = util.init_conf()
custom_com = ast.literal_eval(un_init['other'])
if request.data['permissions_type'] == 'query':
con_name = []
_type = request.data['permissions_type'] + 'con'
permission_spec = grained.objects.filter(
username=request.user).first()
for i in permission_spec.permissions[_type]:
con_instance = DatabaseList.objects.filter(
connection_name=i).first()
if con_instance:
con_name.append({
'id':
con_instance.id,
'connection_name':
con_instance.connection_name,
'ip':
con_instance.ip,
'computer_room':
con_instance.computer_room
})
dic = ''
info = Account.objects.filter(group='admin').all()
serializers = UserINFO(info, many=True)
return Response({
'connection': con_name,
'assigend': assigned.permissions['person'],
'custom': custom_com['con_room'],
'sql_display': custom_com['sql_display'],
})
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
elif args == 'events':
try:
un_init = util.init_conf()
custom_com = ast.literal_eval(un_init['other'])
if request.data['permissions_type'] == 'query':
con_name = []
res_data = []
_type = request.data['permissions_type'] + 'con'
permission_spec = grained.objects.filter(
username=request.user).first()
for i in permission_spec.permissions[_type]:
con_instance = DatabaseList.objects.filter(
connection_name=i).first()
if con_instance:
con_name.append({
'id':
con_instance.id,
'connection_name':
con_instance.connection_name,
'ip':
con_instance.ip,
'computer_room':
con_instance.computer_room
})
# data_set=Event.get_data(self,con_instance)
# for i in data_set['data']:
# res_data.append(i)
total = len(res_data)
res = {
'connection': con_name,
'custom': custom_com['con_room'],
'sql_display': custom_com['sql_display'],
'sql_limit': custom_com['limit'],
'data': res_data,
'total': total
}
return HttpResponse(
simplejson.dumps(res,
cls=DateEncoder,
bigint_as_string=True))
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
def __init__(self, to_addr=None):
self.to_addr = to_addr
un_init = util.init_conf()
self.email = ast.literal_eval(un_init['message'])
def ding_url():
un_init = util.init_conf()
webhook = ast.literal_eval(un_init['message'])
return webhook['webhook']