def db_sql_dumps(request):
if request.method == "POST":#执行原生SQL
try:
dbServer = DataBase_Server_Config.objects.get(id=int(request.POST.get('dbId')))
except Exception, ex:
dbServer = None
if dbServer:
mysql = MySQLPool(host=dbServer.db_host,port=dbServer.db_port,user=dbServer.db_user,passwd=dbServer.db_passwd,dbName=dbServer.db_name)
result = mysql.execute(sql=request.POST.get('sql'),num=1000)
if isinstance(result,str):
return JsonResponse({'msg':"不支持导出功能","code":500,'data':result,'tag':3})
else:
if result[0] == 0:return JsonResponse({'msg':"不支持导出功能","code":200,'data':"SQL执行成功",'tag':3})
file_name = "query_result.csv"
with open(file_name,"w") as csvfile:
writer = csv.writer(csvfile,dialect='excel')
#先写入columns_name
writer.writerow(result[2])
#写入多行用writerows
for ds in result[1]:
writer.writerows([list(ds)])
response = StreamingHttpResponse(base.file_iterator(file_name))
response['Content-Type'] = 'application/octet-stream'
response['Content-Disposition'] = 'attachment; filename="{file_name}'.format(file_name=file_name)
return response
def db_sql_dumps(request):
if request.method == "POST":#执行原生SQL
try:
dbServer = DataBase_Server_Config.objects.get(id=int(request.POST.get('dbId')))
except Exception, ex:
dbServer = None
if dbServer:
mysql = MySQLPool(host=dbServer.db_host,port=dbServer.db_port,user=dbServer.db_user,passwd=dbServer.db_passwd,dbName=dbServer.db_name)
result = mysql.execute(sql=request.POST.get('sql'),num=1000)
if isinstance(result,str):
return JsonResponse({'msg':"不支持导出功能","code":500,'data':result,'tag':3})
else:
if result[0] == 0:return JsonResponse({'msg':"不支持导出功能","code":200,'data':"SQL执行成功",'tag':3})
file_name = "query_result.csv"
with open(file_name,"w") as csvfile:
writer = csv.writer(csvfile,dialect='excel')
#先写入columns_name
writer.writerow(result[2])
#写入多行用writerows
for ds in result[1]:
writer.writerows([list(ds)])
response = StreamingHttpResponse(base.file_iterator(file_name))
response['Content-Type'] = 'application/octet-stream'
response['Content-Disposition'] = 'attachment; filename="{file_name}'.format(file_name=file_name)
return response
def db_status(request, id, format=None):
try:
dbServer = DataBase_Server_Config.objects.get(id=id)
except DataBase_Server_Config.DoesNotExist:
return Response(status=status.HTTP_404_NOT_FOUND)
if request.method == 'POST':
dataList = []
mysql = MySQLPool(host=dbServer.db_host,
port=dbServer.db_port,
user=dbServer.db_user,
passwd=dbServer.db_passwd,
dbName=dbServer.db_name)
version = mysql.execute(sql='SELECT VERSION() as version;')
if isinstance(version, tuple):
data = {}
data['name'] = 'Version'
try:
data['value'] = version[1][0][0]
dataList.append(data)
except Exception, ex:
data['value'] = '未知'
logger.warn(msg="获取MySQL版本信息失败: {ex}".format(ex=ex))
status = mysql.execute(sql='show status;')
if isinstance(status, tuple):
for ds in status[1]:
data = {}
if ds[0].lower() in MySQL.keysList:
data['value'] = ds[1]
data['name'] = ds[0].capitalize()
dataList.append(data)
logs = mysql.execute(sql='show global variables;')
if isinstance(logs, tuple):
for ds in logs[1]:
data = {}
if ds[0].lower() in MySQL.keysList:
data['value'] = ds[1]
data['name'] = ds[0].capitalize()
dataList.append(data)
return JsonResponse({"code": 200, "msg": "success", "data": dataList})
result = mysql.queryMany(sql=request.POST.get('sql'),num=1000)
if isinstance(result,str):
recordSQL.delay(exe_user=str(request.user),exe_db=dbServer,exe_sql=request.POST.get('sql'),exec_status=0,exe_result=str)
return JsonResponse({'msg':"数据查询失败","code":500,'data':result,'tag':3})
recordSQL.delay(exe_user=str(request.user),exe_db=dbServer,exe_sql=request.POST.get('sql'),exec_status=1,exe_result=None)
return JsonResponse({'msg':"数据查询成功","code":200,'data':{"colName":result[2],"dataList":result[1]},'count':result[0],'tag':3})
else:return JsonResponse({'msg':"数据查询失败","code":500,'data':"不是DQL类型语句",'tag':3})
elif request.method == "POST" and request.POST.get('opsTag') == '4' and request.user.is_superuser:#执行原生SQL
try:
dbServer = DataBase_Server_Config.objects.get(id=int(request.POST.get('dbId')))
except Exception, ex:
dbServer = None
if dbServer:
mysql = MySQLPool(host=dbServer.db_host,port=dbServer.db_port,user=dbServer.db_user,passwd=dbServer.db_passwd,dbName=dbServer.db_name)
result = mysql.execute(sql=request.POST.get('sql'),num=1000)
if isinstance(result,str):
recordSQL.delay(exe_user=str(request.user),exe_db=dbServer,exe_sql=request.POST.get('sql'),exec_status=0,exe_result=result)
return JsonResponse({'msg':"数据查询失败","code":500,'data':result,'tag':3})
else:
if result[0] == 0:return JsonResponse({'msg':"数据查询成功","code":200,'data':"SQL执行成功",'tag':3})
recordSQL.delay(exe_user=str(request.user),exe_db=dbServer,exe_sql=request.POST.get('sql'),exec_status=1,exe_result=None)
return JsonResponse({'msg':"数据查询成功","code":200,'data':{"colName":result[2],"dataList":result[1]},'count':result[0],'tag':3})
else:return JsonResponse({'msg':"数据查询失败","code":500,'data':str(ex),'tag':3})
else:return JsonResponse({'msg':"数据操作失败","code":500,'data':"您可能没有权限操作此项目",'tag':3})
@login_required()
@permission_required('OpsManage.can_read_sql_execute_histroy',login_url='/noperm/')
def db_sql_logs(request,page):
if request.method == "GET":
})
elif request.method == "POST" and request.POST.get(
'opsTag') == '4' and request.user.is_superuser: #执行原生SQL
try:
dbServer = DataBase_Server_Config.objects.get(
id=int(request.POST.get('dbId')))
except Exception, ex:
dbServer = None
if dbServer:
mysql = MySQLPool(host=dbServer.db_host,
port=dbServer.db_port,
user=dbServer.db_user,
passwd=dbServer.db_passwd,
dbName=dbServer.db_name)
result = mysql.execute(sql=request.POST.get('sql'), num=1000)
if isinstance(result, str):
recordSQL.delay(exe_user=str(request.user),
exe_db=dbServer,
exe_sql=request.POST.get('sql'),
exec_status=0,
exe_result=result)
return JsonResponse({
'msg': "数据查询失败",
"code": 500,
'data': result,
'tag': 3
})
else:
if result[0] == 0:
return JsonResponse({