1.解析上传数据
class DataUploadAPIView(APIView):
# authentication_classes = (JSONWebTokenAuthentication, SessionAuthentication)
# permission_classes = (IsAuthenticated,)
@transaction.atomic
def post(self, request, *arge, **kwargs):
'''上传数据'''
data = request.data
# {'type': ['0'], 'table_name': ['16'], 'new_table_name': ['11'], 'field_name': ['id'], 'versions': ['0'], 'versions_name': ['222'], 'Fdata': [<InMemoryUploadedFile: drug_base_copy2.xls (application/vnd.ms-excel)>]}
filed_name = request.data['Fdata']
if not filed_name:
raise Forbidden('没有获取到文件')
destination = open(
os.path.join(os.path.dirname(os.path.abspath(__file__)) + '\\excel_upload\\', filed_name.name),
'wb+') # 打开特定的文件进行二进制的写操作
for chunk in filed_name.chunks(): # 分块写入文件
destination.write(chunk)
destination.close()
type_name = data['type']
table_name_id = data['table_name']
new_table_name = data['new_table_name']
field_name = data['field_name']
versions = data['versions']
versions_name = data['versions_name']
table_name = 'database_data_' + DrugTables.objects.filter(pk=table_name_id)[0].table_name
if int(type_name) == int(2):
cursor = connection.cursor()
cursor.execute(f'CREATE TABLE database_data_{new_table_name} LIKE {table_name};')
table_datas = DrugTables.objects.filter(table_name=table_name.replace('database_data_', ''))[0]
DrugTables.objects.create(table_name=new_table_name, table_comment=table_datas.table_comment,
# drug_cls=table_datas.drug_cls, user_cls=table_datas.user_cls)
drug_cls=table_datas.drug_cls, user_cls=request.user)
if str(versions) == str(0):
versions = versions_name
# 读取文件数据
workbook = xlrd.open_workbook(
os.path.join(os.path.dirname(os.path.abspath(__file__)) + '\\excel_upload\\', filed_name.name))
table = workbook.sheets()[0]
# 获取总行数
nrows = table.nrows
head_data = []
queryset = get_query_dawnload(connection, table_name, DATABASES['default']['NAME'])
for sql_info in queryset:
if sql_info['COLUMN_NAME'] != 'id' and sql_info['COLUMN_NAME'] != 'version':
head_data.append(sql_info['COLUMN_NAME']) # 设置标题字段
# 从第三行开始
cursor = connection.cursor()
for x in range(2, nrows):
row = table.row_values(x)
table_data = {}
# 获取数据字段
for i, info in enumerate(head_data):
ctype = table.cell(x, i).ctype
if ctype == 2 and row[i] % 1 == 0:
table_data[info] = int(row[i])
elif ctype == 3:
table_data[info] = xlrd.xldate_as_datetime(row[i], 0)
else:
table_data[info] = str(row[i])
if int(type_name) == int(0):
list_key = []
list_lalues = []
for key, lalues in table_data.items():
list_key.append(key)
list_lalues.append("'" + str(lalues) + "'")
# 拼接sql语句
sql_insert = 'insert into {}({}, version) values({}, "{}")'.format(table_name,
', '.join(list_key),
', '.join(list_lalues), versions)
elif int(type_name) == int(1):
list_lalues = []
field_value = ''
for key, lalues in table_data.items():
list_lalues.append(key + '=' + "'" + str(lalues) + "'")
if key == field_name:
field_value = lalues
lalues = ','.join(list_lalues)
sql_insert = f'update {table_name} set {lalues} where {field_name}="{field_value}" and version="{versions}"'
elif int(type_name) == int(2):
list_key = []
list_lalues = []
for key, lalues in table_data.items():
list_key.append(key)
list_lalues.append("'" + str(lalues) + "'")
sql_insert = 'insert into database_data_{}({}, version) values({}, "{}")'.format(new_table_name,
', '.join(list_key),
', '.join(list_lalues),
versions)
try:
cursor.execute(sql_insert)
except Exception as e:
raise Forbidden('上传解析失败,第{}指端长度超索引, 错误类型:{}'.format(x + 1, e))
return HttpResponse(json.dumps({"status": "上传数据成功", 'data': filed_name.name}), status=status.HTTP_200_OK)
