def __init__(self,
db,
sql,
is_explain,
is_superuser,
permission_db,
permission,
limit=True,
limit_num=50,
page=1):
""" 初始化"""
self.db = db
self.sql = sql
self.is_explain = is_explain
self.limit = limit
self.limit_num = limit_num
self.page = page
self.is_superuser = is_superuser
self.permission_db = permission_db
self.permission = permission
self.have_pagination = False
self.session = sql_connect(db.get_db_connect_str(),
encoding=db.get_encode_display())
self.must_secret_columns = True
if self.is_superuser or self.is_explain:
self.must_secret_columns = False
if str(self.db.pk) in self.permission_db and self.permission_db[str(
self.db.pk)]['have_secret_columns']:
self.must_secret_columns = False
def get(self, request, *args, **kwargs):
""" 返回一个表所有字段名
"""
try:
dbid = int(request.GET.get('dbid', 0))
except:
dbid = 0
if not dbid:
return HttpResponse(json.dumps({}),
content_type="application/json")
table_name = request.GET.get('table_name')
# 判断db是否删除
db = get_object_or_404(DbInfo, pk=dbid)
session = sql_connect(db.get_db_connect_str(),
encoding=db.get_encode_display())
try:
columns = get_columns(session, db.db_name, table_name, db.encode)
except:
logger.error(traceback.format_exc())
return HttpResponse(json.dumps({
'success': False,
'msg': u"获取失败"
}),
content_type="application/json")
finally:
session.close()
r = {'success': True, 'data': columns}
return HttpResponse(json.dumps(r), content_type="application/json")
def get(self, request, *args, **kwargs):
""" 区分db查询
"""
try:
dbid = int(request.GET.get('dbid', 0))
except:
dbid = 0
if not dbid:
return HttpResponse(json.dumps({}),
content_type="application/json")
db = get_object_or_404(DbInfo, pk=dbid)
r = redis.StrictRedis(host=settings.REDIS_SETTINGS['host'],
port=settings.REDIS_SETTINGS['port'],
db=0)
db_keywords = r.hgetall('db_keyword_{}'.format(dbid))
if db_keywords:
_db_keywords = {}
for k, v in db_keywords.items():
_db_keywords[k] = ast.literal_eval(v)
return HttpResponse(json.dumps(_db_keywords),
content_type="application/json")
session = sql_connect(db.get_db_connect_str(),
encoding=db.get_encode_display())
db_keywords = {}
error = False
try:
data = session.execute(
'select TABLE_NAME, COLUMN_NAME from information_schema.COLUMNS WHERE `TABLE_SCHEMA`= :db_name',
{'db_name': db.db_name})
data = data.fetchall()
data = encode_data(data, db.encode)
for c in data:
if c[0].islower() and c[1].islower():
if c[1] not in db_keywords:
db_keywords[c[1]] = []
if c[0] not in db_keywords:
db_keywords[c[0]] = [c[1]]
else:
db_keywords[c[0]].append(c[1])
except:
error = True
logger.error(traceback.format_exc())
try:
session.commit()
except:
error = True
logger.error(traceback.format_exc())
finally:
session.close()
# print(db_keywords)
if not error:
r.hmset('db_keyword_{}'.format(dbid), db_keywords)
# 60 * 60 * 24 = 86400
r.expire('db_keyword_{}'.format(dbid), 86400)
return HttpResponse(json.dumps(db_keywords),
content_type="application/json")
def get(self, request, *args, **kwargs):
dbid = int(request.GET.get('dbid', 0))
table_name = request.GET.get('table')
dbs = DbInfo.objects.get_db_choices(user=self.request.user)
context = {'table_name': table_name, 'dbid': dbid, 'dbs': dbs}
if not dbs:
return render(request, "schema/schema.html", context)
if dbid and dbid not in [d[0] for d in dbs]:
dbid = dbs[0][0]
if not dbid:
dbid = dbs[0][0]
db = get_object_or_404(DbInfo, pk=dbid)
session = sql_connect(db.get_db_connect_str(),
encoding=db.get_encode_display())
try:
# 所有表
context['tables'] = get_tables(session, db.db_name, db.encode)
context['tables_count'] = len(context['tables'])
# 表结构
if table_name:
data = session.execute(
"""SELECT `COLUMN_NAME` AS `Field`, `COLUMN_TYPE` AS `Type`, `COLUMN_KEY` AS `Key`,
`COLUMN_DEFAULT` AS `Default`, `COLUMN_COMMENT` AS `Comment`, `EXTRA`,
`CHARACTER_SET_NAME` AS `Encoding`, `IS_NULLABLE` AS `Allow_Null`, `COLLATION_NAME` AS `Collation`
FROM information_schema.columns
WHERE `table_name`= :table_name and `TABLE_SCHEMA`= :db_name;
""", {
'table_name': table_name,
'db_name': db.db_name
})
context['keys'] = data.keys()
data = data.fetchall()
context['columns_count'] = len(data)
context['data'] = encode_data(data, db.encode)
except:
logger.error(traceback.format_exc())
context['tables'] = []
return render(request, "schema/schema.html", context)
def get(self, request, *args, **kwargs):
""" 返回一个DB的所有表名
perm: 返回有权限的表名列表
"""
try:
dbid = int(request.GET.get('dbid', 0))
except:
dbid = 0
if not dbid:
return HttpResponse(json.dumps({}),
content_type="application/json")
perm = request.GET.get('perm')
db = get_object_or_404(DbInfo, pk=dbid)
if perm:
tables = TablePermission.objects.get_user_tables(request.user, db)
if tables != u'__have_all__':
r = {'success': True, 'data': tables}
return HttpResponse(json.dumps(r),
content_type="application/json")
session = sql_connect(db.get_db_connect_str(),
encoding=db.get_encode_display())
try:
tables = get_tables(session, db.db_name, db.encode)
except:
logger.error(traceback.format_exc())
return HttpResponse(json.dumps({
'success': False,
'msg': u"获取失败"
}),
content_type="application/json")
finally:
session.close()
r = {'success': True, 'data': tables}
return HttpResponse(json.dumps(r), content_type="application/json")