Django Import Export - UNIQUE constraint failed

Question

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

---

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:

---

Here is the CSV File:

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

---

---

When I try to import multiple rows like this:

---

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

Whicker

How can Max. Capacity be your id field if it's not unique? id means the unique id of the object (the row number if you wish). And you're reassigning Max. Capacity to var4 in your ModelResource anyway, so that id isn't getting its value from anywhere. – Clemen

I'm also referring to var4 not being unique (which is what you assign Max. Capacity to). I find it strange that something called "max capacity" would be unique. – Clemen

It gives a unique constraint error on var4 but that isn’t unique in your model. That’s impossible. – Clemen

Well the data you’re trying to import isn’t unique. You have two rows with max. Capacity 5. – Clemen

That means you’re importing a row with an empty value for that column – Clemen

Ive copied the data into a new excel sheet. No empty values. Still same error – Whicker

The import_id_field tells the module how to recognize existing rows. The other columns are just imported So of course var4 is also saved into the db and if something is wrong with this column, like not unique, it will give an error. Now I don’t know why you’re getting the Null issue. Try making a csv file and import that instead. If you have the error still, show us the csv here – Clemen

Please try importing CSV instead of Excel. It looks like the format is wrong, since it tries to import row 2 (b, e, h, k) as var4. Make a csv file instead of .xls file. And open it with a text editor and show it to us. – Clemen

Same error with CSV File – Whicker

Show us the csv file (the text file, not the representation in excel) – Clemen

As you can see, this is not CSV (comma-separated values). There's a ; instead of a , to separate the values. – Clemen

Yes its interesting, because I saved it with Excel as a CSV, uploaded it to a site that shares CSV and it has semicolons instead of commas. Same with the other CSV-Export option excel provides... sharecsv.com/s/f8c25a853f41c8710c6db0f2b37d0e2c/CSVTEST.csv Anyway I need to upload excel files. – Whicker

But you should verify it works with csv first. Just edit your file (it’s a text file so open it with a text editor eg your code editor) and replace the ; then verify that importing works. That way you’ll know the problem is the way you save your xls doc and not with your python code. – Clemen

I replaced the semicolons with commas and got the same error. Here is the file sharecsv.com/s/2d29d879b7bfa91dbb6bfb4fbd210318/CSVTEST.csv – Whicker

Answer 1

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).

Clemen

Did you exclude = ('id', )? – Clemen

Yes, I did exclude 'id'. As per my post: "The error clearly states that the import_export module is still looking for a column named 'id', despite having set the import_id_fields to something else" – Whicker

import_id_fields has little to do with your error, your error is saying that the id field of the model isn't mapped to anything. import_id_fields doesn't tell which column to map to the id field, it tells which columns to use as unique identifiers. That's why you need to add exclude = ('id', ) in the Meta. I did read your post. – Clemen

I just tried this myself and it works. I cannot reproduce your error. – Clemen

It looks like something changed over night,now I get Line number: 2 - UNIQUE constraint failed: var2. I did set unique=True – Whicker

that would happen if you already imported once and then try to import again with the same id – Clemen

To make this easier, remove unique=True from every field in your model except the one you use to as import_id_field. So keep var2 unique, but remove the unique on the others. First it makes more sense (why should everything be unique?) and second it'll allow you to try more imports, just having to make sure var2 is unique. – Clemen

Ive left var2 to be unique and rebuild the db. Still same error – Whicker

Cannot reproduce (see my update), make sure the column names in your file correspond to the column names in your import resource. And make sure there isn't an empty line at the end of the file, your csv file contains an empty line. Make sure you make migrations/migrate if you change your model's unique properties. – Clemen

I've rebuild the db (which implies that I made the migrations), so this is not about updating existing data. In the first case with the single row, the id as well as the keys show up in the import preview - but the values don't. In the second case (with multiple rows), the error is about values in line 2 and 3 not being unique, and not about some line 5 being empty aka not null. In fact, I get the same error with the CSV you posted in your answer. What about my ViewAdmin, is it correct? I dont see why this should work on your side, if I have the same code as you. – Whicker

I don’t think the ModelAdmin makes much difference as long as it’s inheriting from ImportExportModelAdmin. And again, it works for me. If you don’t see the values that should be imported in the preview it means the column names in your csv don’t match the column names in your resource. I don’t see anything else – Clemen

I tried this out without using the import_id_fields, but still, the values don't get imported: #59753039 – Whicker

Did you check the version of tablib? – Clemen

Yes, Im using version 0.14.0. – Whicker

It works now !!! I don't know what changed, but I thank you very much for your help !! I now need to create one big model where all the data from the different imports gets stored. Do you have an idea how I would do that? Cheers – Whicker

I don’t understand this question. If the different imports have different columns they should be different models – Clemen

Here is an example #59753039 – Whicker