django-rest-framework 实现文件批量导入

最近做了一个读取外部excel文件内容并批量导入到数据库的功能：

使用的环境：python3.5 django-rest-framework

读取excel文件需要的包：xlrd

实现批量导入使用的是：bulk_create方法，此方法可以一次性将数据导入到数据库，速度快，但是不能去重；

如果想要对数据去重，还有一个方法是get_or_create,但是导入比较耗时；

 

注：bulk_create() 函数 可以接收的参数  列表list

我们将模型类数据添加到列表list中，然后传递给bulk_create函数即可

 

 

 

代码如下：

class Resource_Distribution(ModelViewSet):
    serializer_class = Resource_DisSerializer
    pagination_class = PageNumberII()

    def batch(self, request, pk, *args, **kwargs):
        # 获取前端传过来的excel文件
        files = request.FILES.get('rest_file')
        file = files.file
        if not all(files):
            return Response({'info': 'error', 'code': 400})
        if (request.user.perm_id == 2) \
                or request.user.perm_id == 1 \
                or (request.user.perm_id == 3):
            # 打开excel文件，并读取去内容
            ExcelFile = xlrd.open_workbook(filename=None, file_contents=file.read())
            sheet = ExcelFile.sheet_by_index(0)
            total_rows = sheet.nrows
            head = sheet.row_values(0)
            emp_queryset = Employee.objects.filter(com_id=request.user.com_id)
            emp_dict = {}
            for emp in emp_queryset:
                emp_dict[emp.employee_number] = emp.employee_name
            col_list = []
            row_list = []
            num_list = []
            for i in range(3, 8):
                col_list.append(sheet.col_values(i)[1:])
            rest_list = col_list
            none_name = []
            for list in rest_list:
                for number in list:
                    if number not in emp_dict:
                        none_name.append(number)
            if len(none_name) > 0:
                return Response({'info': 'repeat', 'code': 406, 'data': json.dumps(none_name)})
            flo_obj = Floors.objects.filter(department_id=request.user.department_id).all()
            eqt_obj = Equipment.objects.filter(department_id=request.user.department_id).all()
            ori_obj = Orientations.objects.filter(case_id=int(pk)).all()
            floor_dict = {}
            eqt_dict = {}
            ori_dict = {}
            None_floor = []
            None_ori = []
            None_eqt = []

            for obj in flo_obj:
                floor_dict[obj.floor] = obj.id
            result = check_exist(sheet, 0, 9, floor_dict, None_floor)
            if result['code'] == 400:
                return Response(result)
            for obj in eqt_obj:
                eqt_dict[obj.equipment_name] = obj.id
            result = check_exist(sheet, 2, 1, dict, None_eqt)
            if result['code'] == 400:
                return Response(result)
            for obj in ori_obj:
                ori_dict[obj.orientation] = obj.id
            result = check_exist(sheet, 1, 13, ori_dict, None_ori)
            if result['code'] == 400:
                return Response(result)
            for v in range(1, total_rows):
                row_list.append(sheet.row_values(v)[::])
            for v in row_list:
                num_list.append(v)
            # 定义一个空列表保存excel文件内容
            datalist = []
            if head[0] == '楼层' and head[1] == '机台' and head[2] == '设备编号' and head[3] == '生产部门负责人' and head[
                4] == '销售负责人' and head[5] == '设计负责人' and head[6] == '品管负责人' and head[7] == '维修负责人':
                dr = False
                try:
                    for n in num_list: 
                        datalist.append(models.Resource_distribution(floor_id=int(n[0]), orientation_id=int(n[1]),
                                                                     equipment_id=int(n[2]),
                                                                     production_number=n[3], production=emp_dict[n[3]],
                                                                     technology_number=n[4], technology=emp_dict[n[4]],
                                                                     manufacture_number=n[5],
                                                                     manufacture=emp_dict[n[5]],
                                                                     QC_number=n[6], QC=emp_dict[n[6]],
                                                                     maintain_number=n[7], maintain=emp_dict[n[7]],
                                                                     ))
                    # 使用bulk_create方法进行批量导入
                    models.Resource_distribution.objects.bulk_create(datalist)
                    dr = True
                except:
                    pass
                if dr == False:
                    return raise_repeat()
                else:
                    return raise_success()
            else:
                return raise_error()
        else:
            return raise_error()