Django Import Export - UNIQUE 约束失败

Question

我正在关注如何使用 django-import-export 的文档:

https://django-import-export.readthedocs.io/en/latest/getting_started.html#declaring-fields

我有一个如下所示的 excel 表



我想将数据存储在这个模型中:

class ExcelData(models.Model):
    var1 = models.CharField(max_length=200)
    var2 = models.CharField(max_length=200,unique=True)
    var3 = models.CharField(max_length=200)
    var4 = models.CharField(max_length=200)

这是我走了多远:

@admin.register(ExcelData)
class ViewAdmin(ImportExportModelAdmin):
    exclude = ('id',)

class ExcelDataResource(resources.ModelResource):
    var1 = Field(attribute='var1', column_name='Name')
    var2 = Field(attribute='var2', column_name='SAP_ID')
    var3 = Field(attribute='var3', column_name='Abbreviation')
    var4 = Field(attribute='var4', column_name='Max. Capa')

    class Meta:
        model = ExcelData
        import_id_fields = ('var2',)
        exclude = ('id',)

这就是我得到的:



这是 CSV 文件:

http://www.sharecsv.com/s/9d1112392cd7f10378de7fc0811dd0c9/REAL_CSV_SIMPLE.csv



当我尝试像这样导入多行时:



我收到此错误:

Line number: 2 - UNIQUE constraint failed: myapp_exceldata.var2
b, e, h, k
Traceback (most recent call last):
File "D:\Users\...\env\lib\site-packages\django\db\backends\utils.py", line 86, in _execute
return self.cursor.execute(sql, params)
File "D:\Users\...\env\lib\site-packages\django\db\backends\sqlite3\base.py", line 396, in execute
return Database.Cursor.execute(self, query, params)
sqlite3.IntegrityError: UNIQUE constraint failed: myapp_exceldata.var2

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
File "D:\Users\...\env\lib\site-packages\import_export\resources.py", line 527, in import_row
self.save_instance(instance, using_transactions, dry_run)
File "D:\Users\...\env\lib\site-packages\import_export\resources.py", line 320, in save_instance
instance.save()
File "D:\Users\...\env\lib\site-packages\django\db\models\base.py", line 746, in save
force_update=force_update, update_fields=update_fields)
File "D:\Users\...\env\lib\site-packages\django\db\models\base.py", line 784, in save_base
force_update, using, update_fields,
File "D:\Users\...\env\lib\site-packages\django\db\models\base.py", line 887, in _save_table
results = self._do_insert(cls._base_manager, using, fields, returning_fields, raw)
File "D:\Users\...\env\lib\site-packages\django\db\models\base.py", line 926, in _do_insert
using=using, raw=raw,
File "D:\Users\...\env\lib\site-packages\django\db\models\manager.py", line 82, in manager_method
return getattr(self.get_queryset(), name)(*args, **kwargs)
File "D:\Users\...\env\lib\site-packages\django\db\models\query.py", line 1204, in _insert
return query.get_compiler(using=using).execute_sql(returning_fields)
File "D:\Users\...\env\lib\site-packages\django\db\models\sql\compiler.py", line 1384, in execute_sql
cursor.execute(sql, params)
File "D:\Users\...\env\lib\site-packages\django\db\backends\utils.py", line 100, in execute
return super().execute(sql, params)
File "D:\Users\...\env\lib\site-packages\django\db\backends\utils.py", line 68, in execute
return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
File "D:\Users\...\env\lib\site-packages\django\db\backends\utils.py", line 77, in _execute_with_wrappers
return executor(sql, params, many, context)
File "D:\Users\...\env\lib\site-packages\django\db\backends\utils.py", line 86, in _execute
return self.cursor.execute(sql, params)
File "D:\Users\...\env\lib\site-packages\django\db\utils.py", line 90, in __exit__
raise dj_exc_value.with_traceback(traceback) from exc_value
File "D:\Users\...\env\lib\site-packages\django\db\backends\utils.py", line 86, in _execute
return self.cursor.execute(sql, params)
File "D:\Users\...\env\lib\site-packages\django\db\backends\sqlite3\base.py", line 396, in execute
return Database.Cursor.execute(self, query, params)
django.db.utils.IntegrityError: UNIQUE constraint failed: myapp_exceldata.var2

Line number: 3 - UNIQUE constraint failed: myapp_exceldata.var2
c, f, i, l
Traceback (most recent call last):
File "D:\Users\...\env\lib\site-packages\django\db\backends\utils.py", line 86, in _execute
return self.cursor.execute(sql, params)
File "D:\Users\...\env\lib\site-packages\django\db\backends\sqlite3\base.py", line 396, in execute
return Database.Cursor.execute(self, query, params)
sqlite3.IntegrityError: UNIQUE constraint failed: myapp_exceldata.var2

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
File "D:\Users\...\env\lib\site-packages\import_export\resources.py", line 527, in import_row
self.save_instance(instance, using_transactions, dry_run)
File "D:\Users\...\env\lib\site-packages\import_export\resources.py", line 320, in save_instance
instance.save()
File "D:\Users\...\env\lib\site-packages\django\db\models\base.py", line 746, in save
force_update=force_update, update_fields=update_fields)
File "D:\Users\...\env\lib\site-packages\django\db\models\base.py", line 784, in save_base
force_update, using, update_fields,
File "D:\Users\...\env\lib\site-packages\django\db\models\base.py", line 887, in _save_table
results = self._do_insert(cls._base_manager, using, fields, returning_fields, raw)
File "D:\Users\...\env\lib\site-packages\django\db\models\base.py", line 926, in _do_insert
using=using, raw=raw,
File "D:\Users\...\env\lib\site-packages\django\db\models\manager.py", line 82, in manager_method
return getattr(self.get_queryset(), name)(*args, **kwargs)
File "D:\Users\...\env\lib\site-packages\django\db\models\query.py", line 1204, in _insert
return query.get_compiler(using=using).execute_sql(returning_fields)
File "D:\Users\...\env\lib\site-packages\django\db\models\sql\compiler.py", line 1384, in execute_sql
cursor.execute(sql, params)
File "D:\Users\...\env\lib\site-packages\django\db\backends\utils.py", line 100, in execute
return super().execute(sql, params)
File "D:\Users\...\env\lib\site-packages\django\db\backends\utils.py", line 68, in execute
return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
File "D:\Users\...\env\lib\site-packages\django\db\backends\utils.py", line 77, in _execute_with_wrappers
return executor(sql, params, many, context)
File "D:\Users\...\env\lib\site-packages\django\db\backends\utils.py", line 86, in _execute
return self.cursor.execute(sql, params)
File "D:\Users\...\env\lib\site-packages\django\db\utils.py", line 90, in __exit__
raise dj_exc_value.with_traceback(traceback) from exc_value
File "D:\Users\...\env\lib\site-packages\django\db\backends\utils.py", line 86, in _execute
return self.cursor.execute(sql, params)
File "D:\Users\...\env\lib\site-packages\django\db\backends\sqlite3\base.py", line 396, in execute
return Database.Cursor.execute(self, query, params)
django.db.utils.IntegrityError: UNIQUE constraint failed: myapp_exceldata.var2

这是 CSV 文件:

http://www.sharecsv.com/s/3f09eb14f7916933604481fd999d03db/REAL_CSV_FULL.csv

感谢您的任何建议

Answer 1

导入的工作方式如下:

您需要一个唯一标识符，以便在导入时，导入导出模块“知道”它是否需要创建新行或修改现有行。

如果该唯一标识符不是模型的 id字段(在您的情况下是自动递增的行号)，那么它必须是其他一些唯一字段。没有字段var1 ... var4在您的模型上被声明为唯一的，因此您当前已经定义了模型，因此无法导入。

如果 可以使用多个“id 字段”。组合 of fields 一起是唯一的，但在您的模型上也不是这样。

导入模块实际上做了一个 get_or_create()使用 unique_id_fields 调用您的数据库作为获取参数。这不应该返回超过 1 行，如果要返回多行，导入会崩溃。

给你一个想法，假设 SAP_ID是唯一标识符(我怀疑 Max. Capacity 是，名称并不表示它会是)，这应该是您的模型:

class ExcelData(models.Model):
    var1 = models.CharField(max_length=200)
    var2 = models.CharField(max_length=200, unique=True)
    var3 = models.CharField(max_length=200)
    var4 = models.CharField(max_length=200)

请注意，您的模型还具有隐式字段 id ，import-export 知道，您必须决定如何处理它，因为它不在您的导入数据中。这就是您收到您提到的错误的原因，因为它是您模型中的一个字段，您没有分配给任何东西。最好是排除它，因为它在这里不相关(如果该行尚不存在，Django 将自动增加一个新的 id，就像你使用 ExcelData.objects.create() 创建模型时一样):

class ExcelDataResource(resources.ModelResource):
    var1 = Field(attribute='var1', column_name='Name')
    var2 = Field(attribute='var2', column_name='SAP_ID')
    var3 = Field(attribute='var3', column_name='Abbreviation')
    var4 = Field(attribute='var4', column_name='Max. Capa')

    class Meta:
        model = ExcelData
        import_id_fields = ('var2',)
        exclude = ('id',)

使用此代码和以下 csv 文件，它可以工作，我已经对其进行了测试:

Name,SAP_ID,Abbreviation,Max. Capa
a,d,g,j
b,e,h,k
c,f,i,l

尝试第二次导入时，或者如果数据库中已经有一些数据的值是 var2是 d、e 或 f，你会得到 UNIQUE constraint failed exceldata.var2 的错误.

备注 : 如果 Max. Capa真的是你独一无二的 id字段，则不应将其分配给 var4但是到 id .这也可行，尽管正如我上面提到的，我怀疑这是你想要的行为。

更新/注 2 : 有一个 bug在 django-import-export 中，当您尝试 时将导致 UNIQUE 约束异常更新现有数据 : 如果您的 unique_id_field与您的 column_name 的名称不同在您导入的数据中(例如 var2 为 SAP_ID )并且您尝试 重新导入数据 其中包含一个已经存在的具有相同 ID 的行(例如，因为您想要 更改 其他值)。它应该更新该行，但目前它会引发异常。这并不能解释 var4 上的异常。 (那是因为您将 var4 设置为唯一，并且如果您导入具有重复 var4 ( Max. Capa ) 值的不同行，那么您也会得到一个异常)。