基于CSV文件更新Django数据库：增删改功能实现求助

问题背景 #

要实现CSV与数据库的同步，需求如下：

• VIN作为唯一标识，CSV中对应条目字段变更时，更新除VIN外的所有字段
• 删除数据库中存在但CSV里已不存在的条目
• 新增CSV中出现但数据库未有的条目

但目前修改CSV内容后上传，会产生重复数据，相关代码如下：

def upload_file__view(request):
    form = form(request.POST or None, request.FILES or None)
    company = Comp_info.objects.last()
    if form.is_valid():
        form.save()
        obj = c.objects.get(activated=False)
        with open(obj.file_name.path, 'r+') as f:
            reader = c.reader(f)

            for i, row in enumerate(reader):
                if i==0:
                    pass
                else:
                    print(row)
                    print(type(row))
                    vin = row[0].upper()
                    condition = row[1].replace("U", "Used").replace("N", "New")
                    stock_no = row[2]
                    year = int(row[5])
                    make = row[3]
                    model = row[4]
                    trim = row[6]
                    mileage = row[8]
                    mpg_city = row[18]
                    mpg_hwy = row[19]
                    engine = row[9]
                    transmission = row[12]
                    fuel_type = row[11]
                    vehicle_type = row[7]
                    drive_type = row[20].replace("4X2", "2WD").replace("4X4", "4WD")
                    exterior_color = row[15]
                    interior_color = row[16]
                    price = row[13].replace("0", "")
                    description = row[22]
                    features_2 = row[21]
                    images_data = row[23]
                    raw_images_list = images_data.split(',')
                    images_list = [""] * 25
                    # 注意：这里image_count未定义，需要补充或改为range(len(raw_images_list))
                    for x in range(image_count):
                        if x == 25:
                            break
                        images_list[x] = raw_images_list[x]
                    
                    for x in images_list:
                        print(x)
                    if images_list[0] == "":
                        images_list[0] = "https://www.beverlyhillscarclub.com/template/images/ina_f.jpg"
                    car_photo =  images_list[0]
                    car_photo_1 = images_list[1]
                    car_photo_2 = images_list[2]
                    car_photo_3 = images_list[3]
                    car_photo_4 = images_list[4]
                    car_photo_5 = images_list[5]
                    car_photo_6 = images_list[6]
                    car_photo_7 = images_list[7]
                    car_photo_8 = images_list[8]
                    car_photo_9 = images_list[9]
                    car_photo_10 = images_list[10]
                    car_photo_11 = images_list[11]
                    car_photo_12 = images_list[12]
                    car_photo_13 = images_list[13]
                    car_photo_14 = images_list[14]
                    car_photo_15 = images_list[15]
                    car_photo_16 = images_list[16]
                    car_photo_17 = images_list[17]
                    car_photo_18 = images_list[18]
                    car_photo_19 = images_list[19]
                    car_photo_20 = images_list[20]
                    car_photo_21 = images_list[21]
                    car_photo_22 = images_list[22]
                    car_photo_23 = images_list[23]
                    car_photo_24 = images_list[24]
                    Cars.objects.update_or_create(
                         vin = vin,
                         condition = condition,
                         stock_no = stock_no,
                         year = year,
                         make = make,
                         model = model,
                         trim = trim,
                         mileage = mileage,
                         mpg_city = mpg_city,
                         engine = engine,
                         transmission = transmission,
                         fuel_type = fuel_type,
                         vehicle_type = vehicle_type,
                         drive_type = drive_type,
                         exterior_color = exterior_color,
                         interior_color = interior_color,
                         price = price,
                         description = description,
                         company_name = company.company_name,
                         address = company.company_address,
                         city = company.city,
                         state = company.state,
                         zip = company.zip_code,
                         phone_number = company.phone_number,
                         email = company.fax_number,
                         features_2 = features_2,
                         car_photo = downloadFile(car_photo),
                         car_photo_1 = downloadFile(car_photo_1),
                         car_photo_2 = downloadFile(car_photo_2),
                         car_photo_3 = downloadFile(car_photo_3),
                         car_photo_4 = downloadFile(car_photo_4),
                         car_photo_5 = downloadFile(car_photo_5),
                         car_photo_6 = downloadFile(car_photo_6),
                         car_photo_7 = downloadFile(car_photo_7),
                         car_photo_8 = downloadFile(car_photo_8),
                         car_photo_9 = downloadFile(car_photo_9),
                         car_photo_10 = downloadFile(car_photo_10),
                         car_photo_11 = downloadFile(car_photo_11),
                         car_photo_12 = downloadFile(car_photo_12),
                         car_photo_13 = downloadFile(car_photo_13),
                         car_photo_14 = downloadFile(car_photo_14),
                         car_photo_15 = downloadFile(car_photo_15),
                         car_photo_16 = downloadFile(car_photo_16),
                         car_photo_17 = downloadFile(car_photo_17),
                         car_photo_18 = downloadFile(car_photo_18),
                         car_photo_19 = downloadFile(car_photo_19),
                         car_photo_20 = downloadFile(car_photo_20),
                         car_photo_21 = downloadFile(car_photo_21),
                         car_photo_22 = downloadFile(car_photo_22),
                         car_photo_23 = downloadFile(car_photo_23),
                         car_photo_24 = downloadFile(car_photo_24)                      
                     )
        
        obj.activated = True
        
        obj.save()

    data = {
        'form' : form,
        'now' : now,
    }
    return render(request, 'uploads.html', data)

问题原因及修复方案 #

1. 修复update_or_create的核心用法 #

当前代码把所有字段都作为update_or_create的查找条件，这意味着只要CSV中任意字段变化，系统会认为是新条目并创建，导致重复。正确的用法是：仅将唯一标识（VIN）作为查找条件，其余字段放入defaults字典中，这样系统会先根据VIN查找记录，存在则更新defaults里的字段，不存在则创建新记录。

2. 实现删除逻辑 #

需要收集本次CSV中所有的VIN，然后删除数据库中不在该集合里的Cars记录，满足“删除数据库存在但CSV没有的条目”的需求。

3. 代码细节优化 #

• image_count变量未定义，需替换为len(raw_images_list)避免索引越界
• price = row[13].replace("0", "")逻辑有问题，比如价格1000会被处理成1，需根据实际需求调整（比如去掉非数字字符或保留有效格式）
• 图片下载逻辑可添加缓存判断，避免重复下载相同URL的图片

修改后的完整代码示例 #

def upload_file__view(request):
    form = form(request.POST or None, request.FILES or None)
    company = Comp_info.objects.last()
    # 用于存储本次CSV中的所有VIN，后续删除用
    csv_vins = set()
    
    if form.is_valid():
        form.save()
        obj = c.objects.get(activated=False)
        with open(obj.file_name.path, 'r+') as f:
            reader = c.reader(f)

            for i, row in enumerate(reader):
                if i == 0:
                    continue  # 跳过表头
                vin = row[0].upper()
                csv_vins.add(vin)
                
                # 处理字段
                condition = row[1].replace("U", "Used").replace("N", "New")
                stock_no = row[2]
                year = int(row[5])
                make = row[3]
                model = row[4]
                trim = row[6]
                mileage = row[8]
                mpg_city = row[18]
                mpg_hwy = row[19]
                engine = row[9]
                transmission = row[12]
                fuel_type = row[11]
                vehicle_type = row[7]
                drive_type = row[20].replace("4X2", "2WD").replace("4X4", "4WD")
                exterior_color = row[15]
                interior_color = row[16]
                # 修正price处理逻辑，示例：仅保留数字和小数点
                price = ''.join(filter(lambda c: c.isdigit() or c == '.', row[13]))
                description = row[22]
                features_2 = row[21]
                images_data = row[23]
                raw_images_list = images_data.split(',')
                images_list = [""] * 25
                
                # 处理图片列表，避免索引越界
                for x in range(min(len(raw_images_list), 25)):
                    images_list[x] = raw_images_list[x]
                
                if images_list[0] == "":
                    images_list[0] = "https://www.beverlyhillscarclub.com/template/images/ina_f.jpg"
                
                # 整理图片字段
                photo_fields = {
                    f'car_photo{x if x > 0 else ""}': downloadFile(images_list[x]) 
                    for x in range(25)
                }
                
                # 正确使用update_or_create
                Cars.objects.update_or_create(
                    vin=vin,
                    defaults={
                        'condition': condition,
                        'stock_no': stock_no,
                        'year': year,
                        'make': make,
                        'model': model,
                        'trim': trim,
                        'mileage': mileage,
                        'mpg_city': mpg_city,
                        'engine': engine,
                        'transmission': transmission,
                        'fuel_type': fuel_type,
                        'vehicle_type': vehicle_type,
                        'drive_type': drive_type,
                        'exterior_color': exterior_color,
                        'interior_color': interior_color,
                        'price': price,
                        'description': description,
                        'company_name': company.company_name,
                        'address': company.company_address,
                        'city': company.city,
                        'state': company.state,
                        'zip': company.zip_code,
                        'phone_number': company.phone_number,
                        'email': company.fax_number,
                        'features_2': features_2,
                        **photo_fields  # 展开图片字段
                    }
                )
        
        # 删除数据库中不在本次CSV里的记录
        if csv_vins:
            Cars.objects.exclude(vin__in=csv_vins).delete()
        
        obj.activated = True
        obj.save()

    data = {
        'form': form,
        'now': now,
    }
    return render(request, 'uploads.html', data)

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