Django上传CSV批量导入时使用update_or_create实现存则更新无则创建

直接用Django ORM内置的update_or_create()替换原有手动实例化模型后save()的逻辑即可。该方法会先根据传入的查询条件匹配已有记录：

• 匹配到记录：用defaults字典里的值更新对应字段
• 未匹配到记录：结合查询条件和defaults里的值新建一条记录

改造后代码 #

import pandas as pd
from rest_framework import generics, status
from rest_framework.response import Response
from django.db import transaction
from .models import Plant, Family, PlantCategory
from .serializers import FileUploadSerializer


class UploadFileView(generics.CreateAPIView):
    serializer_class = FileUploadSerializer

    def post(self, request, *args, **kwargs):
        serializer = self.get_serializer(data=request.data)
        serializer.is_valid(raise_exception=True)
        file = serializer.validated_data['file']
        reader = pd.read_csv(file)
        
        # 开启数据库事务，保证导入操作原子性，避免中途出错导致数据不一致
        with transaction.atomic():
            for _, row in reader.iterrows():
                # 提前查询关联外键对象，和原有逻辑保持一致
                family = Family.objects.get(name=row['Family'])
                category = PlantCategory.objects.get(name=row['Category'])
                
                # 构造需要写入/更新的字段键值对
                plant_data = {
                    "plant_description": row["Plant description"],
                    "family": family,
                    "category": category,
                    "facility_rate": row["Facility rate"],
                    "seedling_depth": row['Seedling depth'],
                    "seedling_distance": row['Seedling distance'],
                    "row_spacing": row['Row spacing'],
                    "sunshine_index": row["Sunshine index"],
                    "irrigation_index": row["Irrigation index"],
                    "soil_nature": row['Soil nature'],
                    "soil_type": row['Soil type'],
                    "fertilizer_type": row['Fertilizer type'],
                    "acidity_index": row["Acidity index"],
                    "days_before_sprouting": row["Days before sprouting"],
                    "average_harvest_time": row["Average harvest time"],
                    "soil_depth": row["Soil depth"],
                    "plant_height": row['Plant height'],
                    "suitable_for_indoor_growing": row["Suitable for indoor growing"],
                    "suitable_for_outdoor_growing": row["Suitable for outdoor growing"],
                    "suitable_for_pot_culture": row["Suitable for pot culture"],
                    "hardiness_index": row["Hardiness index"],
                    "no_of_plants_per_meter": row['No of plants per meter'],
                    "no_of_plants_per_square_meter": row["No of plants per square meter"],
                    "min_temperature": row["Min temperature"],
                    "max_temperature": row["Max temperature"],
                    "time_to_transplant": row["Time to transplant"],
                }

                # 按name字段匹配已有植物记录，执行更新或新建逻辑
                Plant.objects.update_or_create(
                    name=row['Name'],
                    defaults=plant_data
                )

        return Response({"status": "Success: plant(s) created/updated"}, status=status.HTTP_201_CREATED)

注意事项 #

• 匹配条件可根据你的业务规则调整：如果植物名称不能作为唯一判定依据（比如存在同名不同科属的植物），可以把其他判定字段加到update_or_create的查询参数中，例如同时匹配名称和分类：

  Plant.objects.update_or_create(
      name=row['Name'],
      category=category,
      defaults=plant_data
  )
  

• 原有逻辑中Family.objects.get()、PlantCategory.objects.get()在匹配不到对应分类时会抛出DoesNotExist异常，如果需要兼容CSV中分类不存在的场景，可以加异常捕获，或者改用get_or_create自动创建缺失的分类数据。
• 如果单次导入CSV数据量超过1000条，逐行调用update_or_create性能较差，可替换为bulk_create+bulk_update实现批量写入，小数据量场景下当前写法逻辑简单、足够稳定。

内容的提问来源于stack exchange，提问作者RitalCharmant