def post(self, request, args=None):
sql = request.data['sql']
check = str(sql).strip().split(';\n')
user = query_order.objects.filter(
username=request.user).order_by('-id').first()
if user.query_per == 1:
if check[-1].strip().lower().startswith('s') != 1:
return Response({'error': '只支持查询功能或删除不必要的空白行！'})
else:
address = json.loads(request.data['address'])
_c = DatabaseList.objects.filter(
connection_name=user.connection_name,
computer_room=user.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(check[-1].strip(),
conf.limit)
data_set = f.search(sql=query_sql)
querypermissions.objects.create(work_id=user.work_id,
username=request.user,
statements=query_sql)
return Response(data_set)
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return Response({'error': '查询失败，请查看错误日志定位具体问题'})
else:
return Response({'error': '已超过申请时限请刷新页面后重新提交申请'})
def put(self, request, args: str = None):
base = request.data['base']
table = request.data['table']
query_per = query_order.objects.filter(
username=request.user).order_by('-id').first()
if query_per.query_per == 1:
_c = DatabaseList.objects.filter(
connection_name=query_per.connection_name,
computer_room=query_per.computer_room
).first()
try:
with con_database.SQLgo(
ip=_c.ip,
password=_c.password,
user=_c.username,
port=_c.port,
db=base
) as f:
data_set = f.search(sql='desc %s' % table)
return Response(data_set)
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return Response('')
else:
return Response({'error': '非法请求,账号无查询权限！'})
def DicGenerate(id, basename):
'''
字典生成
'''
_connection = DatabaseList.objects.filter(id=id).first()
with con_database.SQLgo(ip=_connection.ip,
user=_connection.username,
password=_connection.password,
db=basename,
port=_connection.port) as f:
res = f.tablename()
for i in res:
EveryData = f.showtable(table_name=i)
for c in EveryData:
if c['Default'] is not None:
SqlDictionary.objects.get_or_create(
Field=c['Field'],
Type=c['Type'],
Null=c['Null'],
Default=c['Default'],
Extra=c['Extra'],
BaseName=basename,
TableName=i,
TableComment=c['TableComment'],
Name=_connection.connection_name)
else:
SqlDictionary.objects.get_or_create(
Field=c['Field'],
Type=c['Type'],
Null=c['Null'],
Extra=c['Extra'],
BaseName=basename,
TableName=i,
TableComment=c['TableComment'],
Name=_connection.connection_name)
def put(self, request, args=None):
'''
:argument 测试数据库连接,并返回测试结果数据
:return: success or fail
'''
try:
ip = request.data['ip']
user = request.data['user']
password = request.data['password']
port = request.data['port']
except KeyError as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
else:
try:
with con_database.SQLgo(ip=ip,
user=user,
password=password,
port=port):
return Response('连接成功!')
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return Response('连接失败!')
def roll(backdb=None, opid=None):
with con_database.SQLgo(ip=conf.backupdb,
user=conf.backupuser,
password=conf.backuppassword,
port=conf.backupport) as f:
data = f.execute(sql='''
select rollback_statement from %s where opid_time =%s;
''' % (backdb, opid))
return data[0][0]
def init_conf():
with con_database.SQLgo(
ip=_conf.get('mysql', 'address'),
user=_conf.get('mysql', 'username'),
password=_conf.get('mysql', 'password'),
db=_conf.get('mysql', 'db'),
port=_conf.get('mysql', 'port')) as f:
res = f.query_info("select * from core_globalpermissions where authorization = 'global'")
return res[0]
def rollbackSQL(db=None, opid=None):
with con_database.SQLgo(ip=conf.backupdb,
user=conf.backupuser,
password=conf.backuppassword,
db=db,
port=conf.backupport) as f:
data = f.execute(sql='''
select tablename from $_$Inception_backup_information$_$ where opid_time =%s;
''' % opid)
return data[0][0]
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 put(self, request, args=None):
'''
:argument 测试数据库连接,并返回测试结果数据
:return: success or fail
'''
if args == 'test':
try:
ip = request.data['ip']
user = request.data['user']
password = request.data['password']
port = request.data['port']
except KeyError as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
else:
try:
with con_database.SQLgo(ip=ip,
user=user,
password=password,
port=port):
return Response('连接成功!')
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return Response('连接失败!')
elif args == 'update':
try:
update_data = json.loads(request.data['data'])
password = AES.encrypt(update_data['password'])
except KeyError as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
else:
try:
DatabaseList.objects.filter(
# connection_name=update_data['connection_name'],
# computer_room=update_data['computer_room']).update(
id=update_data['id']).update(
connection_name=update_data['connection_name'],
ip=update_data['ip'],
username=update_data['username'],
password=password,
port=update_data['port'],
)
return Response('数据信息更新成功！')
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
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 init_conf():
CONF_DATA = conf_path()
with con_database.SQLgo(ip=CONF_DATA.address,
user=CONF_DATA.username,
password=CONF_DATA.password,
db=CONF_DATA.db,
port=CONF_DATA.port) as f:
res = f.query_info(
"select * from core_globalpermissions where authorization = 'global'"
)
return res[0]
def get_data(self,_c,db=''):
if db == '':
sql = '''select '%s' room,'%s' instance_name,
EVENT_SCHEMA db,
EVENT_NAME name,
EVENT_DEFINITION body,
definer,
execute_at,
interval_value,
interval_field,
created,
LAST_ALTERED modified,
last_executed,
starts,
ends,
status,
on_completion,
sql_mode,
LAST_ALTERED comment,
time_zone from information_schema.events;''' %(_c.computer_room,_c.connection_name)
else:
sql = '''select '%s' room,'%s' instance_name,
EVENT_SCHEMA db,
EVENT_NAME name,
EVENT_DEFINITION body,
definer,
execute_at,
interval_value,
interval_field,
created,
LAST_ALTERED modified,
last_executed,
starts,
ends,
status,
on_completion,
sql_mode,
LAST_ALTERED comment,
time_zone from information_schema.events where EVENT_SCHEMA='%s';''' % (_c.computer_room, _c.connection_name,db)
try:
with con_database.SQLgo(
ip=_c.ip,
password=_c.password,
user=_c.username,
port=_c.port ) as f:
data_set = f.search(sql=sql)
return data_set
# return simplejson.dumps(data_set, cls=DateEncoder, bigint_as_string=True)
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
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 post(self, request, args=None):
sql = request.data['sql']
check = str(sql).strip().rstrip(';').split('\n')
if check[-1].lower().find('s') != 0:
return Response({'error': '只支持查询功能!'})
else:
address = json.loads(request.data['address'])
_c = DatabaseList.objects.filter(id=address['id']).first()
try:
with con_database.SQLgo(ip=_c.ip,
password=_c.password,
user=_c.username,
port=_c.port,
db=address['basename']) as f:
dataset = f.search(sql=check[-1] + ' limit 1000')
return Response(dataset)
except Exception as e:
return Response({'error': str(e)})
def post(self, request, args=None):
sql = request.data['sql']
check = str(sql).strip().split(';\n')
if check[-1].strip().lower().startswith('select') != 1:
return Response({'error': '只支持查询功能或删除不必要的空白行！'})
else:
address = json.loads(request.data['address'])
_c = DatabaseList.objects.filter(id=address['id']).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(check[-1].strip(), conf.limit)
data_set = f.search(sql=query_sql)
return Response(data_set)
except Exception as e:
return Response({'error': str(e)})
def put(self, request, args: str = None):
base = request.data['base']
table = request.data['table']
query_per = query_order.objects.filter(
username=request.user).order_by('-id').first()
if query_per.query_per == 1:
_c = DatabaseList.objects.filter(
connection_name=query_per.connection_name,
computer_room=query_per.computer_room).first()
try:
with con_database.SQLgo(ip=_c.ip,
password=_c.password,
user=_c.username,
port=_c.port,
db=base) as f:
data_set = f.search(sql='desc %s' % table)
return Response(data_set)
except:
return Response('')
else:
return Response({'error': '已超过申请时限请刷新页面后重新提交申请'})
def metadata():
print("begin metadata: " + datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')) # 日期格式化
# 这两行很重要，用来寻找项目根目录，os.path.dirname要写多少个根据要运行的python文件到根目录的层数决定
# BASE_DIR = os.path.dirname(os.path.dirname(os.path.dirname(os.path.abspath(__file__))))
# sys.path.append(BASE_DIR)
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'settingConf.settings')
django.setup()
from core.models import DatabaseList,Db_metadata,MetricList,grained
from django.db import connection
try:
databases = DatabaseList.objects.all()
cursor = connection.cursor()
cursor.execute('truncate table {0} ;'.format(Db_metadata._meta.db_table))
for database in databases:
print("begin:" + str(database.id) +":"+ database.connection_name)
try:
with con_database.SQLgo(
ip=database.ip,
user=database.username,
password=database.password,
port=database.port
) as f:
with f.con.cursor() as cursor:
sqllist = "select TABLE_SCHEMA,TABLE_NAME ,CREATE_TIME,TABLE_COMMENT from information_schema.tables where table_schema not in('sys','test','information_schema','performance_schema');"
cursor.execute(sqllist)
result = cursor.fetchall()
for i in result:
Db_metadata.objects.create(connection_name=database.computer_room,computer_room=database.connection_name,db_name=i[0],table_name=i[1],create_time=i[2],table_comment=i[3])
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
print("Error metadata: " + database.connection_name + ":" +datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S') + f'{e.__class__.__name__}: {e}') # 日期格式化
print("done metadata: " + datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')) # 日期格式化
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
print("Error metadata: " + f'{e.__class__.__name__}: {e}') # 日期格式化
def post(self, request, args=None):
sql = request.data['sql']
check = str(sql).strip()[0]
if check == 'S' or check == 's':
address = json.loads(request.data['address'])
_c = DatabaseList.objects.filter(id=address['id']).first()
try:
with con_database.SQLgo(ip=_c.ip,
password=_c.password,
user=_c.username,
port=_c.port,
db=address['basename']) as f:
dataset = f.search(sql=sql)
return Response(dataset)
except Exception as e:
return Response({'error': str(e)})
else:
return Response({'error': '只支持查询功能!'})
def DicGenerate(_connection, basename):
'''
:argument 字典生成
'''
with con_database.SQLgo(ip=_connection.ip,
user=_connection.username,
password=_connection.password,
db=basename,
port=_connection.port) as f:
res = f.baseItems(sql='show tables')
for i in res:
EveryData = f.showtable(table_name=i)
for c in EveryData:
SqlDictionary.objects.get_or_create(
Field=c['Field'],
Type=c['Type'],
Extra=c['Extra'],
BaseName=basename,
TableName=i,
TableComment=c['TableComment'],
Name=_connection.connection_name)
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: 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=None):
print(request.data)
if args == 'connection':
try:
if request.data['permissions_type'] == 'user':
info = DatabaseList.objects.all()
print('1')
con_name = Area(info, many=True).data
print('2')
dic = SqlDictionary.objects.all().values('Name')
print('3')
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(is_staff=1).all()
print('5')
serializers = UserINFO(info, many=True)
print('6')
assigned = grained.objects.filter(
username=request.user).first()
print(assigned.permissions)
return Response({
'connection': con_name,
'person': serializers.data,
'dic': dic,
'assigend': assigned.permissions['person']
})
except Exception as e:
print('dddddd')
print(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=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 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 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 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 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 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 put(self, request, args=None):
if args == 'connection':
try:
info = DatabaseList.objects.all()
_serializers = Area(info, many=True)
return Response(_serializers.data)
except Exception as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
return HttpResponse(status=500)
elif args == "basename":
try:
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=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']
id = request.data['id']
except KeyError as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
else:
_connection = DatabaseList.objects.filter(id=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']
id = request.data['id']
except KeyError as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
else:
try:
_connection = DatabaseList.objects.filter(id=id).first()
with con_database.SQLgo(
ip=_connection.ip,
user=_connection.username,
password=_connection.password,
port=_connection.port,
db=basename
) as f:
res = f.showtable(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']
id = request.data['id']
except KeyError as e:
CUSTOM_ERROR.error(f'{e.__class__.__name__}: {e}')
else:
try:
_connection = DatabaseList.objects.filter(id=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)