def get(self, request, *args, **kwargs):
table_name = request.data.get('name')
username = request.session['username']
password = request.session['password']
host = request.session['host']
port = request.session['port']
database_name = request.session['dbdatabase_name']
obj = DataSource.objects
conn = pymssql.connect(database=database_name, user=username, password=password, host=host, port=port)
client = Client(HDFS_HOST)
cur = conn.cursor()
for i in table_name:
global rels
cur.execute("select name from syscolumns where id = object_id('%s');" % (i))
rels = []
rel = []
rows = cur.fetchall()
for i in rows:
for item in i:
rel.append(item)
rels.append(rel)
# 类似于其他语言的 query 函数， execute 是 python 中的执行查询函数
cur.execute("SELECT * FROM %s" % (i))
# 使用 fetchall 函数，将结果集（多维元组）存入 rows 里面
rows1 = cur.fetchall()
# 依次遍历结果集，发现每个元素，就是表中的一条记录，用一个元组来显示
for row in rows1:
rels.append(list(row))
file_name = i + '.sql'
format_name = uuid.uuid1()
filepath = settings.MEDIA_ROOT + format_name
with open(filepath, 'wb+') as writer:
for chunk in rels:
writer.write(chunk)
client.upload("/datahoop", filepath)
obj.create(file_name=file_name, format_name=format_name, user_id=1)
os.remove(filepath)
client.close()
cur.close()
conn.close()
return HttpResponse(json.dumps(rels), content_type='application/json')
def delete(self, request, *args, **kwargs):
file_id = request.data.get('file_id')
where = DataSource.objects.get(id=file_id).where
if where == 'hdfs':
file = DataSource.objects.get(id=file_id)
hdfs_name = DataSource.objects.get(id=file_id).format_filename
client = Client(HDFS_HOST)
client.delete('/datahoop/' + hdfs_name, recursive=True)
file.delete()
else:
client = pymongo.MongoClient(settings.MONGO_DB_HOST, settings.MONGO_DB_PORT)
db = client.datahoop.data
file_id = DataSource.objects.filter(id=id).first()
obj_id = file_id.obj_id
file_id.delete()
db.remove({"_id": ObjectId(obj_id)})
client.close()
return HttpResponse(content_type='application/json')
def get(self, request, *args, **kwargs):
# table_name = request.data.get('name')
table_name = 'files_datasource'
username = request.session['username']
password = request.session['password']
host = request.session['host']
port = request.session['port']
database_name = request.session['database_name']
obj = DataSource.objects
con = pymysql.connect(host, username, password, database_name)
client = Client(HDFS_HOST)
cur = con.cursor()
# for i in table_name:
sql = "select DISTINCT (COLUMN_NAME) from information_schema.COLUMNS where table_name = '%s'"
cur.execute(sql % (table_name))
rows = cur.fetchall()
rels = []
rel = []
for i in rows:
rel.append(i[0])
rels.append(rel) # 类似于其他语言的 query 函数， execute 是 python 中的执行查询函数
cur.execute("SELECT * FROM %s" % (table_name)) # 使用 fetchall 函数，将结果集（多维元组）存入 rows 里面
rows = cur.fetchall() # 依次遍历结果集，发现每个元素，就是表中的一条记录，用一个元组来显示
for row in rows:
rels.append(list(row))
file_name = table_name + '.sql'
format_name = uuid.uuid1()
filepath = settings.MEDIA_ROOT + str(format_name)
with open(filepath, 'wb+') as writer:
for chunk in rels:
writer.write(chunk)
client.upload("/datahoop", filepath)
obj.create(file_name=file_name, format_name=format_name, user_id=1)
os.remove(filepath)
client.close()
con.close()
cur.close()
return HttpResponse(json.dumps(rels), content_type='application/json')
def get(self, request): # delete mydata
file_id = request.GET.get('file_id')
try:
where = DataSource.objects.get(id=file_id).where
print(DataSource.objects.get(id=file_id))
print(where)
format_filename = DataSource.objects.get(
id=file_id).format_filename
format_name_count = DataSource.objects.filter(
format_filename=format_filename).count()
if where == 'hdfs' and format_name_count == 1:
file = DataSource.objects.get(id=file_id)
hdfs_name = DataSource.objects.get(id=file_id).format_filename
client = Client(HDFS_HOST)
client.delete('/datahoop/' + hdfs_name, recursive=True)
file.delete()
item = Collect.objects.filter(file_id=file_id)
if item:
item.delete()
elif where == 'hdfs' and format_name_count > 1:
file = DataSource.objects.get(id=file_id)
file.delete()
item = Collect.objects.filter(file_id=file_id)
if item:
item.delete()
else:
client = pymongo.MongoClient(settings.MONGO_DB_URI)
db = client.datahoop.data
data_obj = DataSource.objects.filter(id=file_id).first()
obj_id = data_obj.obj_id
data_obj.delete()
db.remove({"_id": ObjectId(obj_id)})
client.close()
item = Collect.objects.filter(file_id=file_id)
if item:
item.delete()
return JsonResponse({'status': True})
except:
return JsonResponse({'status': False})
def get(self, request, detail_id):
id = detail_id
view_num = DataSource.objects.get(id=id).view_num
DataSource.objects.filter(id=id).update(view_num=view_num + 1)
user_id = request.user.id
object = DataSource.objects.get(id=id)
where = object.obj_id
if where == '':
try:
file = object.format_filename
file = str(file)
header = 'media/' + str(object.user.image)
username = object.user.username
hdfs_path = '/datahoop/' + file
client = Client(HDFS_HOST)
json = {}
json['header'] = header
file_user_id = object.user.id
relation = Relationship.objects.filter(author_id=user_id)
all = []
for i in relation:
all.append(i.User_ByID)
if str(file_user_id) in all:
json['is_focus'] = 1
else:
json['is_focus'] = 0
objects = Collect.objects.filter(source=1, user=user_id, file_id=id)
if objects:
json['is_collect'] = 1
else:
json['is_collect'] = 0
love = Love.objects.filter(user=user_id, file_id=id, source=1)
if love:
json['is_love'] = 1
else:
json['is_love'] = 0
file_user_id = object.user_id
if user_id == file_user_id:
json['is_me'] = 1
else:
json['is_me'] = 0
json['file_name'] = str(object.file_name)
json['title'] = object.detail
json['hdfs'] = object.format_filename
json['fav_num'] = object.fav_num
json['view_num'] = object.view_num
json['thumb_num'] = object.thumb_num
json['label'] = [object.label_name, '']
json['username'] = username
json['data'] = []
with client.read(hdfs_path, encoding='utf-8') as reader:
for i in (reader.readlines())[0:20]:
print(i)
json['data'].append(i.split(','))
return Response(json, status=status.HTTP_200_OK)
except Exception as e:
print(e)
return Response(status=status.HTTP_404_NOT_FOUND)
else:
try:
client = pymongo.MongoClient(MONGO_DB_URI)
db = client.datahoop.data
json = {}
fileName = str(object.file_name)
obj_id = object.obj_id
file_user_id = object.user.id
relation = Relationship.objects.filter(author_id=user_id)
all = []
for i in relation:
all.append(i.User_ByID)
if str(file_user_id) in all:
json['is_focus'] = 1
else:
json['is_focus'] = 0
result = db.find({'_id': ObjectId(obj_id)})
fileType = (fileName.split('.')[-1]).lower() # 获取文件后缀名
table_name = fileName
sheetList = ''
sheet = ''
json['file_user_id'] = file_user_id
json['header'] = 'media/' + str(object.user.image)
json['file_name'] = str(object.file_name)
if user_id == file_user_id:
json['is_me'] = 1
else:
json['is_me'] = 0
json['title'] = object.detail
objects = Collect.objects.filter(source=1, user=user_id, file_id=id)
if objects:
json['is_collect'] = 1
else:
json['is_collect'] = 0
love = Love.objects.filter(user=user_id, file_id=id, source=1)
if love:
json['is_love'] = 1
else:
json['is_love'] = 0
json['obj_id'] = object.obj_id
json['fav_num'] = object.fav_num
json['view_num'] = object.view_num
json['thumb_num'] = object.thumb_num
json['label'] = [object.label_name, '']
json['username'] = object.user.username
if fileType == 'xls' or fileType == 'xlsx': # 读取ｅｘｃｅｌ文件 返回列表
sheetList = (sorted((result[0]['fileData'])))
rel = sorted((result[0]['fileData']).items())[0][1][0:501]
default_sheet = sorted((result[0]['fileData']).items())[0][0]
sheet = request.GET.get('sheet')
if sheet:
rel = (result[0]['fileData'][sheet][0:501])
else:
sheet = default_sheet
elif fileType == 'csv' or fileType == 'txt':
import pandas as pd
# empty = pandas.DataFrame()
# data = empty.append(result[0]['fileData'])
# rel = data[0:].values.tolist()[0:20]
data = pd.DataFrame(result[0]['fileData'])
rel = data.values.tolist()[0:200]
for i in range(len(rel)):
for j in range(len(rel[i])):
if str(rel[i][j]) == "nan":
rel[i][j] = ""
json['data'] = rel
elif fileType == 'sql':
rel = (result[0]['fileData'])[0:20]
json['data'] = rel
client.close()
return Response(json, status=status.HTTP_200_OK)
except Exception as e:
print(e)
return Response(status=status.HTTP_404_NOT_FOUND)
