def db_exec(request):
if request.method == 'GET':
dbs = DBConfig.objects.prefetch_related('db_group').all()
user_groups = request.user.groups.all()
user_dbs = []
for db in dbs:
if not set(db.db_group.all()).isdisjoint(set(user_groups)):
user_dbs.append(db)
return render(request, 'dbmanager/db_exec.html', {'user_dbs': user_dbs})
elif request.method == 'POST':
pk = request.POST.get('pk')
sql = request.POST.get('sql')
sql_type = request.POST.get('sql_type')
sql_file = request.FILES.get('upload_file')
if sql_file:
sql = ''.join({chunk.decode('utf-8') for chunk in sql_file.chunks(chunk_size=1024)})
sql_file.close()
return JsonResponse({'code': 200, 'data': sql, 'msg': 'sql上传成功！'})
db = DBConfig.objects.select_related('db_server').get(id=pk)
try:
conn = MysqlPool(db.db_server.service_asset.asset_management_ip, db.db_port, db.db_user,
CryptPwd().decrypt_pwd(db.db_password), db.db_name)
if sql == 'show tables':
res = conn.get_tables(sql)
return JsonResponse({'code': 200, 'data': res, 'msg': 'sql执行成功！'})
elif sql_type == 'select':
table_heads, res = conn.exec_select(sql)
if isinstance(table_heads, list):
db_log_id = sql_log(db_config=db, db_login_user=request.user, db_sql_content=sql, db_sql_res=res,
db_sql_res_thead=str(table_heads))
return JsonResponse({'code': 200, 'table_heads': table_heads, 'data': res, 'db_log_id': db_log_id,
'msg': 'sql执行成功！'})
else:
db_log_id = sql_log(db_config=db, db_login_user=request.user, db_sql_content=sql, db_sql_res=res)
return JsonResponse({'code': 507, 'data': res, 'db_log_id': db_log_id, 'msg': 'sql执行失败！'})
elif sql_type == 'sql-one':
res = conn.exec_sql_one(sql)
db_sql_res = '受影响的行数: {}'.format(res) if isinstance(res, int) else str(res)
db_log_id = sql_log(db_config=db, db_login_user=request.user, db_sql_content=sql,
db_sql_res=db_sql_res)
return JsonResponse(
{'code': 200, 'data': db_sql_res, 'db_log_id': db_log_id, 'msg': 'sql执行成功！'})
elif sql_type == 'sql-many':
sql_list = sql.split('args=')
run_sql = sql_list[0].rstrip()
args = eval(sql_list[1])
res = conn.exec_sql_many(run_sql, args)
db_log_id = sql_log(db_config=db, db_login_user=request.user, db_sql_content=sql,
db_sql_res='受影响的行数: {}'.format(res))
return JsonResponse(
{'code': 200, 'data': '受影响的行数: {}'.format(res), 'db_log_id': db_log_id, 'msg': 'sql执行成功！'})
except Exception as e:
return JsonResponse({'code': 500, 'data': None, 'msg': 'sql执行失败！{}'.format(e)})
def db_user(request):
if request.method == 'POST':
pk = request.POST.get('pk')
port = request.POST.get('port')
action = request.POST.get('action')
db_server = ServerAssets.objects.select_related('assets').get(id=pk)
try:
conn = MysqlPool(db_server.assets.asset_management_ip, int(port),
settings.MYSQL_USER, settings.MYSQL_PASS, 'mysql')
if action == 'show databases':
res = conn.get_dbs()
return JsonResponse({
'code': 200,
'data': res,
'msg': 'sql执行成功！'
})
elif action == 'show users':
res = conn.user_all()
return JsonResponse({
'code': 200,
'data': res,
'msg': 'sql执行成功！'
})
elif action == 'add user':
user = request.POST.get('user').split('@')
password = request.POST.get('password')
db_table = request.POST.get('db_table', None)
privs = request.POST.getlist('privs', None)
conn.user_add(user[0], user[1], password, db_table, privs)
return JsonResponse({
'code': 200,
'data': None,
'msg': '用户添加成功！'
})
elif action == 'delete user':
user = request.POST.get('user').split('@')
conn.user_delete(user[0], user[1])
return JsonResponse({
'code': 200,
'data': None,
'msg': '用户删除成功！'
})
elif action == 'mod user':
old_user = request.POST.get('old_user').split('@')
new_user = request.POST.get('new_user').split('@')
conn.user_mod(old_user[0],
old_user[1],
new_user=new_user[0],
new_host=new_user[1])
return JsonResponse({
'code': 200,
'data': None,
'msg': '用户修改成功！'
})
elif action == 'mod pass':
user = request.POST.get('user').split('@')
password = request.POST.get('password')
conn.user_mod(user[0], user[1], password=password)
return JsonResponse({
'code': 200,
'data': None,
'msg': '密码修改成功！'
})
elif action == 'add priv':
user = request.POST.get('user').split('@')
db_table = request.POST.get('db_table')
privs = request.POST.getlist('privs')
conn.privileges_grant(user[0], user[1], db_table, privs)
return JsonResponse({
'code': 200,
'data': None,
'msg': '权限添加成功！'
})
elif action == 'del priv':
user = request.POST.get('user').split('@')
db_table = request.POST.get('db_table')
privs = request.POST.getlist('privs')
conn.privileges_revoke(user[0], user[1], db_table, privs)
return JsonResponse({
'code': 200,
'data': None,
'msg': '权限删除成功！'
})
except Exception as e:
return JsonResponse({
'code': 500,
'data': None,
'msg': 'sql执行失败！{}'.format(e)
})
privs = VALID_PRIVS
server_assets = ServerAssets.objects.select_related('assets').all()
return render(request, 'dbmanager/db_user.html', locals())