Django Import Export – 唯一约束失败

huangapple
go评论73阅读模式
英文:

Django Import Export - UNIQUE constraint failed

问题

我正在遵循这份关于如何使用django-import-export的文档：

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

我有一个类似下面的Excel表格：

Django Import Export – 唯一约束失败

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

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',)

这是我得到的结果：

Django Import Export – 唯一约束失败

这是CSV文件：

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

当我尝试导入多行数据时，像这样：

Django Import Export – 唯一约束失败

我得到了这个错误：

Line number: 2 - UNIQUE constraint failed: myapp_exceldata.var2
b, e, h, k
Traceback (most recent call last):
...
django.db.utils.IntegrityError: UNIQUE constraint failed: myapp_exceldata.var2

这是CSV文件：

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

感谢任何建议。

英文:

I am following this documentation on how to use django-import-export:

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

I have an excel sheet that looks like below

Django Import Export – 唯一约束失败

I want to store the data in this model:

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)

This is how far I got:

@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',)

This is what I get:

Django Import Export – 唯一约束失败

Here is the CSV File:

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

When I try to import multiple rows like this:

Django Import Export – 唯一约束失败

I get this error:

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

Here is the CSV file:

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

Thank you for any suggestions

答案1

得分: 1

导入工作的方式如下：

  • 您需要一个唯一标识符，以便在导入时，导入-导出模块"知道"是否需要创建新行或修改现有行。
  • 如果该唯一标识符不是模型的 id 字段（在您的情况下是自增的行号），那么它必须是某个其他唯一字段。您的模型上的字段 var1 ... var4 都没有声明为唯一字段，所以根据您当前定义的模型，导入是不可能的。
  • 如果一组字段的组合在一起是唯一的，那么可以使用多个 "id 字段"，但在您的模型中也不是这种情况。
  • 导入模块实际上会在数据库上执行 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 字段，导入-导出知道它，您必须决定如何处理它，因为它不在您的导入数据中。这就是为什么您收到您提到的错误，因为它是您的模型中的一个字段，您没有分配任何内容给它。最好是将其排除，因为在这里它没有相关性（当您使用 ExcelData.objects.create() 创建模型时，Django 将自动增加一个新的 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 文件，它可以正常工作，我已经测试过：

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：django-import-export 中存在一个错误，当您尝试更新现有数据时，它会导致唯一约束异常：这发生在您的 unique_id_field 与您导入的数据中的 column_name 的名称不同（例如 var2 对应 SAP_ID），并且您尝试重新导入包含已存在行的数据时（例如因为您想要更改其他值）。它应该_更新_行，但当前会引发异常。这不解释 var4 上的异常（这是因为您将 var4 也设置为唯一，如果导入一个具有重复 var4（Max. Capa）值的不同行，那么您也会收到异常）。

英文:

The way import works is the following:

  • You need a unique identifier so that when importing, the import-export module "knows" whether it needs to create a new row or modify an existing row.
  • If that unique identifier is not the model's id field (which in your case is the auto-incremented row number), then it must be some other unique field. None of the fields var1 ... var4 on your model are declared as unique, so as you've defined your model currently, it's impossible to import.
  • You can use multiple "id fields" if a combination of fields is unique together, but that's also not the case on your model.
  • The import module with actually do a get_or_create() call on your database, using the unique_id_fields as the get parameters. This should never return more than 1 row, if it were to return multiple rows, the import would crash.

To give you an idea, and assuming SAP_ID is a unique identifier (I doubt Max. Capacity is, the name doesn't indicate it would be), this should be your model:

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)

Note your model also has the implicit field id, which import-export knows about and you have to decide what to do with it, since it's not in your import data. This is why you're receiving the error you mentioned, because it's a field in your model that you haven't assigned to anything. Best is to exclude it, since it's not relevant here (Django will auto-increment a new id if the row doesn't exist yet, like when you create the model with 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',)

With this code and the following csv file, it works, I've tested it:

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

When trying to import a second time or if there's already some data in the database where the value of var2 is d, e or f, you'll get UNIQUE constraint failed errors for exceldata.var2.

Note: If Max. Capa really is your unique id field, then you shouldn't assign it to var4 but to id. That would also work, although as I mentioned above I doubt that's your desired behaviour.

UPDATE/Note 2: There's a bug in django-import-export that will cause UNIQUE constraints exceptions when you try to update existing data: This happens if your unique_id_field has a different name than your column_name in the data you import (e.g. var2 for SAP_ID) and you try to re-import data which contains an already existing row with that same id (e.g. because you want to change the other values). It should update the row, but currently it throws an exception. This doesn't explain the exception on var4 (that's because you set var4 to be unique as well and if you import a different row with a duplicate var4 (Max. Capa) value, then you'll also get an exception).

huangapple
  • 本文由 发表于 2020年1月6日 19:25:08
  • 转载请务必保留本文链接：https://go.coder-hub.com/59611268.html
  • django
  • django-import-export
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定