Django: how to find which CHECK constraint failed

user_13565158 0
Viewed 1083

I am using django 3. sqlite database. I have a situation in which an an instance in one of my models will not save. I get an integrity error and "CHECK constraint failed", followed by the name of my model ("post" in my "press" app: press_post). I have looked this up, and i guess it means that a value for one of my fields is impossible? If someone can explain what it means more accurately and precisely, it would be helpful. Mostly, I want to know how to find out which check constraint failed so I can fix it (which field or which piece of data in the model is causing the problem). other instances in the model save without any issues, while a few others have the same problem as this instance.

I can access the instance in shell_plus and look at the data. it looks ok ... but obviously i'm missing something.

the error output:

---------------------------------------------------------------------------
IntegrityError                            Traceback (most recent call last)
~/pastrami/rye/lib/python3.8/site-packages/django/db/backends/utils.py in _execute(self, sql, params, *ignored_wrapper_args)
     83             else:
---> 84                 return self.cursor.execute(sql, params)
     85 

~/pastrami/rye/lib/python3.8/site-packages/django/db/backends/sqlite3/base.py in execute(self, query, params)
    422         query = self.convert_query(query)
--> 423         return Database.Cursor.execute(self, query, params)
    424 

IntegrityError: CHECK constraint failed: press_post

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

IntegrityError                            Traceback (most recent call last)
<ipython-input-8-0c61e89703f4> in <module>
----> 1 post.save()

~/pastrami/pastrami/press/models.py in save(self, *args, **kwargs)
    438         #     postreport_check(self)
    439 
--> 440         super(Post, self).save(*args, **kwargs)
    441 
    442     # tags

~/pastrami/rye/lib/python3.8/site-packages/django/db/models/base.py in save(self, force_insert, force_update, using, update_fields)
    724                 update_fields = frozenset(loaded_fields)
    725 
--> 726         self.save_base(using=using, force_insert=force_insert,
    727                        force_update=force_update, update_fields=update_fields)
    728     save.alters_data = True

~/pastrami/rye/lib/python3.8/site-packages/django/db/models/base.py in save_base(self, raw, force_insert, force_update, using, update_fields)
    761             if not raw:
    762                 parent_inserted = self._save_parents(cls, using, update_fields)
--> 763             updated = self._save_table(
    764                 raw, cls, force_insert or parent_inserted,
    765                 force_update, using, update_fields,

~/pastrami/rye/lib/python3.8/site-packages/django/db/models/base.py in _save_table(self, raw, cls, force_insert, force_update, using, update_fields)
    843                       for f in non_pks]
    844             forced_update = update_fields or force_update
--> 845             updated = self._do_update(base_qs, using, pk_val, values, update_fields,
    846                                       forced_update)
    847             if force_update and not updated:

~/pastrami/rye/lib/python3.8/site-packages/django/db/models/base.py in _do_update(self, base_qs, using, pk_val, values, update_fields, forced_update)
    897                 (filtered._update(values) > 0 or filtered.exists())
    898             )
--> 899         return filtered._update(values) > 0
    900 
    901     def _do_insert(self, manager, using, fields, returning_fields, raw):

~/pastrami/rye/lib/python3.8/site-packages/django/db/models/query.py in _update(self, values)
    800         query.annotations = {}
    801         self._result_cache = None
--> 802         return query.get_compiler(self.db).execute_sql(CURSOR)
    803     _update.alters_data = True
    804     _update.queryset_only = False

~/pastrami/rye/lib/python3.8/site-packages/django/db/models/sql/compiler.py in execute_sql(self, result_type)
   1557         related queries are not available.
   1558         """
-> 1559         cursor = super().execute_sql(result_type)
   1560         try:
   1561             rows = cursor.rowcount if cursor else 0

~/pastrami/rye/lib/python3.8/site-packages/django/db/models/sql/compiler.py in execute_sql(self, result_type, chunked_fetch, chunk_size)
   1173             cursor = self.connection.cursor()
   1174         try:
-> 1175             cursor.execute(sql, params)
   1176         except Exception:
   1177             # Might fail for server-side cursors (e.g. connection closed)

~/pastrami/rye/lib/python3.8/site-packages/django/db/backends/utils.py in execute(self, sql, params)
     96     def execute(self, sql, params=None):
     97         with self.debug_sql(sql, params, use_last_executed_query=True):
---> 98             return super().execute(sql, params)
     99 
    100     def executemany(self, sql, param_list):

~/pastrami/rye/lib/python3.8/site-packages/django/db/backends/utils.py in execute(self, sql, params)
     64 
     65     def execute(self, sql, params=None):
---> 66         return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
     67 
     68     def executemany(self, sql, param_list):

~/pastrami/rye/lib/python3.8/site-packages/django/db/backends/utils.py in _execute_with_wrappers(self, sql, params, many, executor)
     73         for wrapper in reversed(self.db.execute_wrappers):
     74             executor = functools.partial(wrapper, executor)
---> 75         return executor(sql, params, many, context)
     76 
     77     def _execute(self, sql, params, *ignored_wrapper_args):

~/pastrami/rye/lib/python3.8/site-packages/django/db/backends/utils.py in _execute(self, sql, params, *ignored_wrapper_args)
     82                 return self.cursor.execute(sql)
     83             else:
---> 84                 return self.cursor.execute(sql, params)
     85 
     86     def _executemany(self, sql, param_list, *ignored_wrapper_args):

~/pastrami/rye/lib/python3.8/site-packages/django/db/utils.py in __exit__(self, exc_type, exc_value, traceback)
     88                 if dj_exc_type not in (DataError, IntegrityError):
     89                     self.wrapper.errors_occurred = True
---> 90                 raise dj_exc_value.with_traceback(traceback) from exc_value
     91 
     92     def __call__(self, func):

~/pastrami/rye/lib/python3.8/site-packages/django/db/backends/utils.py in _execute(self, sql, params, *ignored_wrapper_args)
     82                 return self.cursor.execute(sql)
     83             else:
---> 84                 return self.cursor.execute(sql, params)
     85 
     86     def _executemany(self, sql, param_list, *ignored_wrapper_args):

~/pastrami/rye/lib/python3.8/site-packages/django/db/backends/sqlite3/base.py in execute(self, query, params)
    421             return Database.Cursor.execute(self, query)
    422         query = self.convert_query(query)
--> 423         return Database.Cursor.execute(self, query, params)
    424 
    425     def executemany(self, query, param_list):

IntegrityError: CHECK constraint failed: press_post

here is the [edit: simplified] model (just including the field that caused the problem)

class Post(models.Model):
    doi = models.CharField(max_length=50, blank=True)
    detailed_alts = models.JSONField(default=dict, blank=True)
    alts = models.JSONField(default=dict, blank=True)

here is the [edit: simplified] overridden save function.

    def save(self, *args, **kwargs):  # when saving, update alts

        if bool(self.doi) is True:
                self.detailed_alts = self.get_alts['detailed']
                self.alts = self.get_alts['simple']

[EDIT]

I realized from the helpful comment that the issue was in my custom save function in which i updated two fields. The Check constraint failed because for some instances I was asking to set something to these JSONFields that was not allowed: a nan value using numpy (np.nan)

here is the relevant (offensive) part of the code: the get_alts method for my model.

def get_alts(self):
            if 'context' in detailed_alts:
                [do stuff]
            else:
                alts['context'] = np.nan
                alts['rank'] = np.nan
    return {'detailed': detailed_alts, 'simple': alts}

for instances in which 'context' was not in the detailed_alts dictionary, i could not save the changes to the instance because i had set the value to np.nan, which I guess isn't allowed in a JSONField. Changing np.nan to 'NaN' fixed the problem.

As far as how this was figured out, i just had to try commenting out parts of the save function until I hit on the parts that caused save to fail. Then I tested each line in the save function, but never got errors until it tried to save it. So, it somehow dawned on me the problem might be the jsonfield because i had problems once before when serializing dates.

django-3.2 python-3.x sqlite django
2 Answers

Thank you for the help. the problem was that in my custom save function, when evaluating one of the conditions I assigned an np.nan value to a key in a dictionary that was then assigned to one of the jsonfield fields of my model. apparently that's a no-no. I guess jsonfields cannot serialize np.nan. I'm not sure what to do about it in the long run , but changing them to 'NaN' text worked. the reason why most instances had no problem is because most did not return nan values.

user_135651580

A Google search along the lines of the title (my specific search was django determine what field caused check constraint to fail) will pull up this answer high in the results (first for me). But there is little to actually answer the question posed by the title.

I finally was able to figure out which field was causing the issue by debugging deep into the call stack in PyCharm. In django.db.models.base.Model._do_insert as I inspected the values of the object to be inserted, before stepping into manager._insert, I found that there were fields I had given default values to long before I added my constraints, but those default values were now in conflict with my constraints. So, in hopes of saving someone some debugging effort, I recommend setting a break point in _do_insert and take a look at the model object's field values.

user_23642150
Related