Django导入CSV触发UNIQUE约束失败错误求助

错误现象 #

运行批量从CSV读取数据、关联Lot和Owner的Django函数时，抛出以下错误：

UNIQUE constraint failed: valuation_lot.lot_number

模型定义（models.py） #

class Owner(models.Model):
    account_number = models.CharField(max_length=30, primary_key=True, unique=True)
    first_name = models.CharField(max_length=30)
    last_name = models.CharField(max_length=30)
    occupation = models.CharField(max_length=30, null=True, blank=True)

    class Meta:
        ordering = ["last_name"]
        verbose_name = 'Owner'
        verbose_name_plural = 'Owners'

    def __str__(self):
        return f"{self.first_name} {self.last_name}"

class Lot(models.Model):
    lot_number = models.CharField(max_length=6, primary_key=True, unique=True)
    owner = models.ManyToManyField(Owner)
    lot_description = models.CharField(max_length=300,null=True, blank=True)
    lot_rate = models.DecimalField(decimal_places=2, max_digits=15, default=10)
    payment = models.DecimalField(decimal_places=2, max_digits=8, default=0)
    lot_area = models.DecimalField(decimal_places=2, max_digits=7, default=0)
   
    class Meta:
        ordering= ["lot_number"]
        verbose_name = 'Lot'
        verbose_name_plural = 'Lots'

    def __str__(self):
        return f"Parcel No. {self.lot_number}"

触发错误的处理函数 #

def handle_csv(csv):
    database = pd.read_csv(csv)
    lot= database[['lot_description','lot_number', 'lot_rate']]
    owner = database[['account_number', 'first_name', 'last_name', 'occupation']]

    address = database[['area','lot_number', 'account_number','street_name', 'street_number']]

    for index in database.index:
        try:
            owner_new = Owner.objects.get(pk=owner["account_number"][index])
        except:
            owner_new = Owner.objects.create(
            account_number=owner["account_number"][index],
            first_name= owner["first_name"][index],
            last_name= owner["last_name"][index],
            occupation= owner["occupation"][index]
            )
            owner_new.save()
        try:
            lot_new = Lot.objects.get(pk=lot["lot_number"][index])
            lot_new.owner.add(owner_new)
            lot_new.save()
        except: 
            lot_new = Lot.objects.create(
                lot_number=lot["lot_number"][index],
                lot_description= lot["lot_description"][index],
                lot_rate = Decimal(float(lot['lot_rate'][index])),
                )
            lot_new.owner.add(owner_new)
            lot_new.save()

完整错误栈（Traceback） #

Traceback:

Environment:


Request Method: POST
Request URL: http://127.0.0.1:8000/upload/upload_file

Django Version: 4.0.6
Python Version: 3.8.10
Installed Applications:
['bootstrap4',
 'upload.apps.UploadConfig',
 'accounts.apps.AccountsConfig',
 'valuation.apps.ValuationConfig',
 'django.contrib.admin',
 'django.contrib.auth',
 'django.contrib.contenttypes',
 'django.contrib.sessions',
 'django.contrib.messages',
 'django.contrib.staticfiles']
Installed Middleware:
('django.middleware.security.SecurityMiddleware',
 'whitenoise.middleware.WhiteNoiseMiddleware',
 'django.contrib.sessions.middleware.SessionMiddleware',
 'django.middleware.common.CommonMiddleware',
 'django.middleware.csrf.CsrfViewMiddleware',
 'django.contrib.auth.middleware.AuthenticationMiddleware',
 'django.contrib.messages.middleware.MessageMiddleware',
 'django.middleware.clickjacking.XFrameOptionsMiddleware')



Traceback (most recent call last):
  File "/home/dayne/Desktop/pgtc/upload/functions.py", line 26, in handle_csv
    lot_new = Lot.objects.get(pk=lot["lot_number"][index])
  File "/home/dayne/Desktop/pgtc/venv/lib/python3.8/site-packages/django/db/models/manager.py", line 85, in manager_method
    return getattr(self.get_queryset(), name)(*args, **kwargs)
  File "/home/dayne/Desktop/pgtc/venv/lib/python3.8/site-packages/django/db/models/query.py", line 492, in get
    num = len(clone)
  File "/home/dayne/Desktop/pgtc/venv/lib/python3.8/site-packages/django/db/models/query.py", line 302, in __len__
    self._fetch_all()
  File "/home/dayne/Desktop/pgtc/venv/lib/python3.8/site-packages/django/db/models/query.py", line 1507, in _fetch_all
    self._result_cache = list(self._iterable_class(self))
  File "/home/dayne/Desktop/pgtc/venv/lib/python3.8/site-packages/django/db/models/query.py", line 87, in __iter__
    for row in compiler.results_iter(results):
  File "/home/dayne/Desktop/pgtc/venv/lib/python3.8/site-packages/django/db/models/sql/compiler.py", line 1299, in apply_converters
    value = converter(value, expression, connection)
  File "/home/dayne/Desktop/pgtc/venv/lib/python3.8/site-packages/django/db/backends/sqlite3/operations.py", line 343, in converter
    return create_decimal(value).quantize(

During handling of the above exception (argument must be int or float), another exception occurred:
  File "/home/dayne/Desktop/pgtc/venv/lib/python3.8/site-packages/django/db/backends/utils.py", line 89, in _execute
    return self.cursor.execute(sql, params)
  File "/home/dayne/Desktop/pgtc/venv/lib/python3.8/site-packages/django/db/backends/sqlite3/base.py", line 477, in execute
    return Database.Cursor.execute(self, query, params)

The above exception (UNIQUE constraint failed: valuation_lot.lot_number) was the direct cause of the following exception:
  File "/home/dayne/Desktop/pgtc/venv/lib/python3.8/site-packages/django/core/handlers/exception.py", line 55, in inner
    response = get_response(request)
  File "/home/dayne/Desktop/pgtc/venv/lib/python3.8/site-packages/django/core/handlers/base.py", line 197, in _get_response
    response = wrapped_callback(request, *callback_args, **callback_kwargs)
  File "/home/dayne/Desktop/pgtc/upload/views.py", line 12, in upload_file
    handle_csv(request.FILES['file'])
  File "/home/dayne/Desktop/pgtc/upload/functions.py", line 30, in handle_csv
    lot_new = Lot.objects.create(
  File "/home/dayne/Desktop/pgtc/venv/lib/python3.8/site-packages/django/db/models/manager.py", line 85, in manager_method
    return getattr(self.get_queryset(), name)(*args, **kwargs)
  File "/home/dayne/Desktop/pgtc/venv/lib/python3.8/site-packages/django/db/models/query.py", line 514, in create
    obj.save(force_insert=True, using=self.db)
  File "/home/dayne/Desktop/pgtc/venv/lib/python3.8/site-packages/django/db/models/base.py", line 806, in save
    self.save_base(
  File "/home/dayne/Desktop/pgtc/venv/lib/python3.8/site-packages/django/db/models/base.py", line 857, in save_base
    updated = self._save_table(
  File "/home/dayne/Desktop/pgtc/venv/lib/python3.8/site-packages/django/db/models/base.py", line 1000, in _save_table
    results = self._do_insert(
  File "/home/dayne/Desktop/pgtc/venv/lib/python3.8/site-packages/django/db/models/base.py", line 1041, in _do_insert
    return manager._insert(
  File "/home/dayne/Desktop/pgtc/venv/lib/python3.8/site-packages/django/db/models/manager.py", line 85, in manager_method
    return getattr(self.get_queryset(), name)(*args, **kwargs)
  File "/home/dayne/Desktop/pgtc/venv/lib/python3.8/site-packages/django/db/models/query.py", line 1434, in _insert
    return query.get_compiler(using=using).execute_sql(returning_fields)
  File "/home/dayne/Desktop/pgtc/venv/lib/python3.8/site-packages/django/db/models/sql/compiler.py", line 1621, in execute_sql
    cursor.execute(sql, params)
  File "/home/dayne/Desktop/pgtc/venv/lib/python3.8/site-packages/django/db/backends/utils.py", line 103, in execute
    return super().execute(sql, params)
  File "/home/dayne/Desktop/pgtc/venv/lib/python3.8/site-packages/django/db/backends/utils.py", line 67, in execute
    return self._execute_with_wrappers(
  File "/home/dayne/Desktop/pgtc/venv/lib/python3.8/site-packages/django/db/backends/utils.py", line 80, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/home/dayne/Desktop/pgtc/venv/lib/python3.8/site-packages/django/db/backends/utils.py", line 89, in _execute
    return self.cursor.execute(sql, params)
  File "/home/dayne/Desktop/pgtc/venv/lib/python3.8/site-packages/django/db/utils.py", line 91, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/home/dayne/Desktop/pgtc/venv/lib/python3.8/site-packages/django/db/backends/utils.py", line 89, in _execute
    return self.cursor.execute(sql, params)
  File "/home/dayne/Desktop/pgtc/venv/lib/python3.8/site-packages/django/db/backends/sqlite3/base.py", line 477, in execute
    return Database.Cursor.execute(self, query, params)

Exception Type: IntegrityError at /upload/upload_file
Exception Value: UNIQUE constraint failed: valuation_lot.lot_number

原因分析 #

从错误栈可以明确两个核心问题：

1. 裸except捕获所有异常：Lot部分的try-except没有指定具体异常，当get操作因lot_rate字段Decimal转换失败（提示argument must be int or float）时，错误被捕获并进入create分支，尝试创建已存在的Lot记录。
2. 重复记录触发唯一约束：CSV中存在多条相同lot_number的记录，第一次循环已创建该Lot，后续循环再次进入create分支时，违反lot_number的唯一约束。

解决方法 #

方案1：精确捕获异常+处理字段转换 #

from django.core.exceptions import ObjectDoesNotExist
from decimal import Decimal, InvalidOperation

def handle_csv(csv):
    database = pd.read_csv(csv)
    for index in database.index:
        # 处理Owner
        acc_num = database["account_number"][index]
        try:
            owner_new = Owner.objects.get(pk=acc_num)
        except ObjectDoesNotExist:
            owner_new = Owner.objects.create(
                account_number=acc_num,
                first_name= database["first_name"][index],
                last_name= database["last_name"][index],
                occupation= database["occupation"][index]
            )
        
        # 处理Lot
        lot_num = database["lot_number"][index]
        try:
            lot_new = Lot.objects.get(pk=lot_num)
            lot_new.owner.add(owner_new)
        except ObjectDoesNotExist:
            # 安全转换lot_rate
            try:
                lot_rate_val = Decimal(str(database['lot_rate'][index]))
            except (InvalidOperation, ValueError):
                lot_rate_val = Decimal('10.00')  # 使用默认值
            
            lot_new = Lot.objects.create(
                lot_number=lot_num,
                lot_description= database["lot_description"][index],
                lot_rate= lot_rate_val,
            )
            lot_new.owner.add(owner_new)

方案2：使用get_or_create简化逻辑 #

Django内置的get_or_create方法可以自动处理"查询-创建"逻辑，避免手动try-except：

from decimal import Decimal, InvalidOperation

def handle_csv(csv):
    database = pd.read_csv(csv)
    for index in database.index:
        # 处理Owner
        owner_data = {
            'first_name': database["first_name"][index],
            'last_name': database["last_name"][index],
            'occupation': database["occupation"][index]
        }
        owner_new, _ = Owner.objects.get_or_create(
            account_number=database["account_number"][index],
            defaults=owner_data
        )
        
        # 处理Lot
        lot_num = database["lot_number"][index]
        # 处理lot_rate转换
        try:
            lot_rate_val = Decimal(str(database['lot_rate'][index]))
        except (InvalidOperation, ValueError):
            lot_rate_val = Decimal('10.00')
        
        lot_data = {
            'lot_description': database["lot_description"][index],
            'lot_rate': lot_rate_val
        }
        lot_new, _ = Lot.objects.get_or_create(
            lot_number=lot_num,
            defaults=lot_data
        )
        lot_new.owner.add(owner_new)

额外优化建议 #

• 无需拆分DataFrame，直接从原表取字段即可，减少代码复杂度。
• create和ManyToMany添加操作后无需手动save()，Django会自动处理持久化。
• 导入前先清洗CSV数据，去除重复lot_number记录，统一数字格式，避免转换错误。

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