data isn't importing in mysqldb using django import export

Question

I am new in Django. I am trying to use Django import-export to import excel sheet into MySQL dB table. I followed the documentation on import. while trying to Test the data import it gives error.

Here are my views:

from django.shortcuts import render
from pathlib import Path
import os
from .resources import ProductsResource
from tablib import Dataset



def home(requests):
    dataimport()
    return render(requests,'dbapp/index.html')

def dataimport():

        products_resource = ProductsResource()
        dataset = Dataset()

        dirname = Path(__file__).resolve().parent.parent
        file_name = 'Price.xlsx'
        file_location = os.path.join(dirname, file_name)
        df = pd.read_excel(file_location, header=1, usecols='A:F')
        
        dataset.load(df)
        result = product_resource.import_data(dataset, dry_run=True, raise_errors=True) # Test the data import
        print(result.has_errors())
        if not result.has_errors():
           products_resource.import_data(dataset, dry_run=False)  # Actually import now

Resource.py:

from import_export import resources
from .models import  Products

class ProductsResource(resources.ModelResource):
    class Meta:
        model = Products

Models.py:

from django.db import models
from django.db.models.fields import DateField

class Products(models.Model):
    date = models.DateField(auto_now_add=False,auto_now=False)
    salt = models.FloatField()
    oil = models.FloatField()
    honey = models.FloatField()
    butter = models.FloatField()
    milk = models.FloatField()

My excel file looks like this:

    Date    Salt    Oil     Honey   Butter  Milk
2020-1-1    26.5    106.5   281     387.5   83
2020-1-2    26.2    106.2   279.8   386     82.4
2020-1-3    26      106     279     385     82
2020-1-4    25      105     275     380     80
2020-1-5    26.2    106.2   279.8   386     82.4
2020-1-6    26.1    106.1   279.4   385.5   82.2

Error message:

IntegrityError at /
(1048, "Column 'date' cannot be null")
Request Method: GET
Request URL:    http://127.0.0.1:8000/
Django Version: 3.1.7
Exception Type: IntegrityError
Exception Value:    
(1048, "Column 'date' cannot be null")
Exception Location: C:\Users\shash\.virtualenvs\DBProjectWorkspace-IwiT3tz3\lib\site-packages\django\db\backends\mysql\base.py, line 78, in execute
Python Executable:  C:\Users\shash\.virtualenvs\DBProjectWorkspace-IwiT3tz3\Scripts\python.exe
Python Version: 3.9.2

Answer 1

Please can you run again with raise_errors=True and post back with the error message. You can call change your code as follows.

result = products_resource.import_data(dataset, dry_run=True, raise_errors=True)

You can edit your existing question to include the error message, rather than replying in a new post.

My guess is that the field names in your csv don't match the model field names, and then the error is raised when a record cannot be created with null values.

If this is the case, then you can either change the csv header names so that they match the model field names: Salt becomes salt, or declare each field in the ModelResource so that the column and csv field match, e.g.

name = Field(attribute="salt", column_name="Salt")

However, this is only a guess and there could be some other issue.

If you are new to Django, then I strongly recommend using an IDE like PyCharm, and then setting up the debugger. This means that you can step through the program line by line, and understand exactly what the problem is. You learn a lot by doing this, and it is a lot quicker than asking questions on SO.

Also, I suggest naming your Django model in singular form rather than plural, so Product not Products.