Constraint Violation

Using Django
#1

Hi All,

I’ve had to make a change to a model to ensure that a combination of attributes is unique.

class Case(models.Model):
    uuid = models.UUIDField(primary_key=True, default=uuid.uuid4())
    # combination of species and ID to be unique
    id = models.IntegerField()
    species = models.ForeignKey(Species, on_delete=models.CASCADE)
    # and several other attributes

    class Meta:
        constraints = [models.UniqueConstraint(fields=["id", "species"], name="unique_case")]

I’m in the unfortunate position where I have to load data from excel into my database. Pandas is great, humans inputting non-normalised, non-relational data, not so much.

For my Case model, I have some data in CSV format which looks like this:
Headers
ID, Species, name, problem
Data
1, rabbit, carmen, unwell
2, rabbit, bob, bad hair day
3, rabbit, tramper, jelly legs

My importer code effectively does this:

for row in excel:
     build a dict of keys and values (case_dict in my code example below)
     create or update a case

Here is what I have got:

species, _ = Species.objects.get_or_create(species='rabbit')

# case_data looks like this
case_dict = {
    "name": "carmen",
     "problem": "unwell"
}
case, created = Case.objects.filter(species=species).get_or_create(
    id=data.number,
    species=species,
    defaults={
        **case_dict,
    },
)

If I run this code, the first case will be created, but I will receive an exception when attempting to create the second case. If I rerun the code, the second case will be created, and attempting to create the third case will throw an exception.

The exception summary (full exception at the end of this post):

Traceback (most recent call last):
  File "/usr/local/lib/python3.8/site-packages/django/db/models/query.py", line 559, in get_or_create
    return self.get(**kwargs), False
  File "/usr/local/lib/python3.8/site-packages/django/db/models/query.py", line 415, in get
    raise self.model.DoesNotExist(
cases.models.DoesNotExist: Case matching query does not exist.

#### Lots of text omitted - full stacktrace at the end of the post

django.db.utils.IntegrityError: duplicate key value violates unique constraint "cases_case_pkey"
DETAIL:  Key (uuid)=(b57252e3-2c9e-4514-905c-c132f85fa6e4) already exists.

What’s confusing me is that one, the code only works one case at a time before throwing an exception, and two, I would expect that a case that already existed would be updated. I’m also confused as to why it is telling me that a case doesn’t exist, as I thought that if a case with the specific attributes from the spreadsheet didn’t exist, then and instance of case would be created.

One thought that crossed my mind is that id is used by Django in a way with which I am not familiar, but I haven’t managed to determine if that is the case or not.

Well, it’s very late here and I should probably sleep on it. Any help would be most welcome.

Cheers,

C

Full stacktrace

! Updated Image: images/C1CI1.jpeg
ID: 1 SPECIES: rabbit
! Updated Image: images/C2CI1.jpeg
ID: 2 SPECIES: rabbit
! Updated Image: images/C3CI1.jpeg
ID: 3 SPECIES: rabbit
! Updated Image: images/C4CI1.jpeg
ID: 4 SPECIES: rabbit
! Updated Image: images/C5CI1.jpeg
ID: 5 SPECIES: rabbit
Traceback (most recent call last):
  File "/usr/local/lib/python3.8/site-packages/django/db/models/query.py", line 559, in get_or_create
    return self.get(**kwargs), False
  File "/usr/local/lib/python3.8/site-packages/django/db/models/query.py", line 415, in get
    raise self.model.DoesNotExist(
cases.models.DoesNotExist: Case matching query does not exist.

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/usr/local/lib/python3.8/site-packages/django/db/backends/utils.py", line 86, in _execute
    return self.cursor.execute(sql, params)
psycopg2.errors.UniqueViolation: duplicate key value violates unique constraint "cases_case_pkey"
DETAIL:  Key (uuid)=(dc5a1adc-bb29-457d-a9f3-243746f3231c) already exists.


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

Traceback (most recent call last):
  File "import_tool.py", line 3, in <module>
    main()
  File "/opt/project/populator/import_tool.py", line 102, in main
    import_cases(case_data, media)
  File "/opt/project/populator/importer/cases.py", line 77, in import_cases
    case, created = Case.objects.filter(species=species).get_or_create(
  File "/usr/local/lib/python3.8/site-packages/django/db/models/query.py", line 562, in get_or_create
    return self._create_object_from_params(kwargs, params)
  File "/usr/local/lib/python3.8/site-packages/django/db/models/query.py", line 604, in _create_object_from_params
    raise e
  File "/usr/local/lib/python3.8/site-packages/django/db/models/query.py", line 596, in _create_object_from_params
    obj = self.create(**params)
  File "/usr/local/lib/python3.8/site-packages/django/db/models/query.py", line 433, in create
    obj.save(force_insert=True, using=self.db)
  File "/usr/local/lib/python3.8/site-packages/django/db/models/base.py", line 745, in save
    self.save_base(using=using, force_insert=force_insert,
  File "/usr/local/lib/python3.8/site-packages/django/db/models/base.py", line 782, in save_base
    updated = self._save_table(
  File "/usr/local/lib/python3.8/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 "/usr/local/lib/python3.8/site-packages/django/db/models/base.py", line 924, in _do_insert
    return manager._insert(
  File "/usr/local/lib/python3.8/site-packages/django/db/models/manager.py", line 82, in manager_method
    return getattr(self.get_queryset(), name)(*args, **kwargs)
  File "/usr/local/lib/python3.8/site-packages/django/db/models/query.py", line 1204, in _insert
    return query.get_compiler(using=using).execute_sql(returning_fields)
  File "/usr/local/lib/python3.8/site-packages/django/db/models/sql/compiler.py", line 1392, in execute_sql
    cursor.execute(sql, params)
  File "/usr/local/lib/python3.8/site-packages/django/db/backends/utils.py", line 100, in execute
    return super().execute(sql, params)
  File "/usr/local/lib/python3.8/site-packages/django/db/backends/utils.py", line 68, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/usr/local/lib/python3.8/site-packages/django/db/backends/utils.py", line 77, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/usr/local/lib/python3.8/site-packages/django/db/backends/utils.py", line 86, in _execute
    return self.cursor.execute(sql, params)
  File "/usr/local/lib/python3.8/site-packages/django/db/utils.py", line 90, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/usr/local/lib/python3.8/site-packages/django/db/backends/utils.py", line 86, in _execute
    return self.cursor.execute(sql, params)
django.db.utils.IntegrityError: duplicate key value violates unique constraint "cases_case_pkey"
DETAIL:  Key (uuid)=(dc5a1adc-bb29-457d-a9f3-243746f3231c) already exists.
#2

Hi,

It’s very helpful to try to break things down to isolate whatever might cause exceptions to be thrown. What I did was to start a project with a single app and copied your models like so:

#casehandler/models.py
from django.db import models

import uuid


class Species(models.Model):
    name = models.CharField(max_length=200)


class Case(models.Model):
    uuid = models.UUIDField(primary_key=True, default=uuid.uuid4())
    # combination of species and ID to be unique
    id = models.IntegerField()
    species = models.ForeignKey(Species, on_delete=models.CASCADE)
    problem = models.CharField(max_length=200)

    class Meta:
        constraints = [models.UniqueConstraint(fields=["id", "species"], name="unique_case")]

I then created a set of tests

#casehandler/tests.py
from django.test import TestCase
from django.db import IntegrityError

from .models import Case, Species

class CaseTestCase(TestCase):
    def setUp(self):
        self.s1 = Species.objects.create(name='Rabbit')

    def test_one_rabbit(self):
        self.c1 = Case.objects.create(id=1, species=self.s1, problem='unwell')

    def test_fail_duplicate_rabbit(self):
        self.c1 = Case.objects.create(id=1, species=self.s1, problem='unwell')
        self.assertRaises(IntegrityError, Case.objects.create,
                                     id=1, species=self.s1, problem='bad hair day')

    def test_two_rabbit(self):
        self.c1 = Case.objects.create(id=1, species=self.s1, problem='unwell')
        self.c2 = Case.objects.create(id=2, species=self.s1, problem='bad hair day')

The second test did generate an IntegrityError as expected. But the test_two_rabbit test did so as well, with the specific error message: django.db.utils.IntegrityError: UNIQUE constraint failed: casehandler_case.uuid.

The main issue is simply that you used uuid = models.UUIDField(primary_key=True, default=uuid.uuid4()) instead of uuid = models.UUIDField(primary_key=True, default=uuid.uuid4). This means that the default is set to a specific uuid that happens to be produced by uuid.uuid4() the one time it is run. What you want the default to be is that the uuid.uuid4() function is called anew each time to produce a new default value (rather than simply reusing one particular randomly generated value).

The point is that the issue you were facing wasn’t related to pandas or the data imports, and using unit tests is great for breaking things down and building things up one piece at a time to see at what points that issues arise. Testing is awesome :slight_smile:

2 Likes
#3

You meant default=uuid.uuid4 , without the parentheses. Currently each time django imports that module, your code passes it a single value to be used for all model instances. And that changes at each import, since UUID’s are random. If you don’t pass the parentheses, Django will call the function for each instance.

You should see “fields.W161 : Fixed default value provided” as a system check

1 Like
#4

Lowe, Adam,

Thank you very much for both for showing me the wood amongst the trees. Funny how the (my) old brain works. 40+ models with uuid.uuid4 and I can still chop my own legs off when adding a couple of models more. And good point about the System Check, Adam. Thank you. (Enjoying your book, too).

And Lowe, thank you for the detailed response. You’re spot on about break down problems and testing. I should do more of that. Please allow me to say that I wasn’t pointing the blame at Pandas, my colleagues or the bird on the roof, but merely being a tad facetious and giving some back story as to why I was writing what I was writing. I didn’t expect for a moment that the problem wouldn’t be lying squarely at my feet. Death, taxes and problems square at my feet.

Righto, onto writing more tests!

And once more, thank you Lowe, thank you Adam.

1 Like