CODEWITHSUNDEEP
pythondjango
Joel G Mathew
Joel G Mathew

Reputation: 8081

Saving data fails because of null constraint when data is not null

I have the following python code in django which is deployed on heroku:

def newimportdb(request):
    import xlrd 
    loc = ("./CGHS Rates 2014- Trivandrum.xlsx") 
    wb = xlrd.open_workbook(loc) 
    sheet = wb.sheet_by_index(0) 
    for i in range(sheet.nrows): 
        try:                
            sl = int(sheet.cell_value(i, 0))
        except:
            title = sheet.cell_value(i, 1)
            hd = CGHSSection(title = title)   
            try:
                hd.save()
                heading = CGHSSection.objects.get(title = title)
                print(f"Saved new heading: {title}")
                continue
            except:
                print(f"Could not save new section: {title}. Maybe it existing. Reading it.")
                heading = CGHSSection.objects.get(title = title)
                continue

            test = sheet.cell_value(i, 1)
            nonnabh = int(sheet.cell_value(i, 2))
            nabh = int(sheet.cell_value(i, 3))
            print(f'{i} Test:{test} NonNABH:{nonnabh} NABH:{nabh} Under:{heading}') 

            it = CGHSRates(serial = sl, name =test, NABHrate = nabh, NonNrate=nonnabh, section=heading)
            print(f"Value of section heading is {heading.title}")
            print(f'it = CGHSRates(serial = {sl}, name ={test}, NABHrate = {nabh}, NonNrate={nonnabh}, section={heading}')
            it.save()
            print("Saved")

In my models, I have:

from django.db import models
class CGHSRates(models.Model):
    rid = models.AutoField(primary_key=True)
    serial = models.IntegerField(default=0)
    name = models.CharField(max_length=100)
    NonNrate = models.FloatField(blank=True)
    NABHrate = models.FloatField(blank=True)
    section = models.ForeignKey('CGHSSection', on_delete=models.SET_NULL, null=True, blank=True)

    class Meta:
        unique_together = ('name', 'NonNrate', 'NABHrate')

class CGHSSection(models.Model):
    num = models.AutoField(primary_key=True)
    title = models.CharField(max_length=150, unique=True)

On running the above, I get:

[22/Dec/2018 09:07:55] "GET /favicon.ico/ HTTP/1.1" 404 3346
Could not save new section: UNCLASSIFIED. Maybe it existing. Reading it.
1 Test:Consultation OPD NonNABH:135 NABH:135 Under:CGHSSection object (1)
Value of section heading is UNCLASSIFIED
it = CGHSRates(serial = 1, name =Consultation OPD, NABHrate = 135, NonNrate=135, section=CGHSSection object (1)
Internal Server Error: /app/import
Traceback (most recent call last):
File "/home/joel/sampledjango/venv/lib/python3.6/site-packages/django/db/backends/utils.py", line 85, in _execute
    return self.cursor.execute(sql, params)
psycopg2.IntegrityError: null value in column "section" violates not-null constraint
DETAIL:  Failing row contains (3, 1, Consultation OPD, 135, 135, null, 1).


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

    Traceback (most recent call last):
    File "/home/joel/sampledjango/venv/lib/python3.6/site-packages/django/core/handlers/exception.py", line 34, in inner
        response = get_response(request)
    File "/home/joel/sampledjango/venv/lib/python3.6/site-packages/django/core/handlers/base.py", line 126, in _get_response
        response = self.process_exception_by_middleware(e, request)
    File "/home/joel/sampledjango/venv/lib/python3.6/site-packages/django/core/handlers/base.py", line 124, in _get_response
        response = wrapped_callback(request, *callback_args, **callback_kwargs)
    File "/home/joel/peroorkada/app/views.py", line 134, in newimportdb
        it.save()
    File "/home/joel/sampledjango/venv/lib/python3.6/site-packages/django/db/models/base.py", line 718, in save
        force_update=force_update, update_fields=update_fields)
    File "/home/joel/sampledjango/venv/lib/python3.6/site-packages/django/db/models/base.py", line 748, in save_base
        updated = self._save_table(raw, cls, force_insert, force_update, using, update_fields)
    File "/home/joel/sampledjango/venv/lib/python3.6/site-packages/django/db/models/base.py", line 831, in _save_table
        result = self._do_insert(cls._base_manager, using, fields, update_pk, raw)
    File "/home/joel/sampledjango/venv/lib/python3.6/site-packages/django/db/models/base.py", line 869, in _do_insert
        using=using, raw=raw)
    File "/home/joel/sampledjango/venv/lib/python3.6/site-packages/django/db/models/manager.py", line 82, in manager_method
        return getattr(self.get_queryset(), name)(*args, **kwargs)
    File "/home/joel/sampledjango/venv/lib/python3.6/site-packages/django/db/models/query.py", line 1136, in _insert
        return query.get_compiler(using=using).execute_sql(return_id)
    File "/home/joel/sampledjango/venv/lib/python3.6/site-packages/django/db/models/sql/compiler.py", line 1289, in execute_sql
        cursor.execute(sql, params)
    File "/home/joel/sampledjango/venv/lib/python3.6/site-packages/django/db/backends/utils.py", line 100, in execute
        return super().execute(sql, params)
    File "/home/joel/sampledjango/venv/lib/python3.6/site-packages/django/db/backends/utils.py", line 68, in execute
        return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
    File "/home/joel/sampledjango/venv/lib/python3.6/site-packages/django/db/backends/utils.py", line 77, in _execute_with_wrappers
        return executor(sql, params, many, context)
    File "/home/joel/sampledjango/venv/lib/python3.6/site-packages/django/db/backends/utils.py", line 85, in _execute
        return self.cursor.execute(sql, params)
    File "/home/joel/sampledjango/venv/lib/python3.6/site-packages/django/db/utils.py", line 89, in __exit__
        raise dj_exc_value.with_traceback(traceback) from exc_value
    File "/home/joel/sampledjango/venv/lib/python3.6/site-packages/django/db/backends/utils.py", line 85, in _execute
        return self.cursor.execute(sql, params)
    django.db.utils.IntegrityError: null value in column "section" violates not-null constraint
    DETAIL:  Failing row contains (3, 1, Consultation OPD, 135, 135, null, 1).

I cant understand why this is happening. My print just before saving data shows that section is not null. However that seems to be the error I'm getting

Upvotes: 1

Views: 72

Answers (1)

shafikshaon
shafikshaon

Reputation: 6404

Change

it = CGHSRates(serial=sl, name=test, NABHrate=nabh, NonNrate=nonnabh, section=heading)

to

it = CGHSRates(serial=sl, name=test, NABHrate=nabh, NonNrate=nonnabh, section=heading.pk)

For foreign key field it aspect pk(integer value), so you need to assign a pk(integer value) for a foreign key (in your case: section). And 

Model.objects.get(someting=something)

return a object. You assign 

it = CGHSRates(serial=sl, name=test, NABHrate=nabh, NonNrate=nonnabh, section=heading)

section=heading that means you try to assign a object to a foreign key field. section aspect a pk(int value) but get a object so it's throw not null error as it can't assign a valid value for section which is not nullable.

In your code

heading = CGHSSection.objects.get(title = title)

heading get a single object. So you can just use heading.pk to get object pk value.

Upvotes: 2

Related Questions