Django call 'id' expected a number but got string

Question

Django errors with django-import-export libraries.

I want to import data from excel to db via django admin. I use for it django-import-export, but i got Field 'id' expected a number but got 'HPI'.

Excel file contains

I found answer, that I have to add exclude = ('id',), but it didn't help. Also i did migrations, it didn't help too. How to fix it and have ability to import 6 columns data from excel to db via django admin?

models.py

    from django_mysql.models import JSONField, Model
    from django.db import models



    class Category(Model):
        title = models.CharField(max_length=100)

        class Meta:
            ordering = ('-id',)
            verbose_name = 'Category'
            verbose_name_plural = 'Categories'

        def __str__(self):
            return self.title


    class Tag(Model):
        title = models.CharField(max_length=100)

        class Meta:
            ordering = ('-id',)

        def __str__(self):
            return self.title


    class Type(Model):
        title = models.CharField(max_length=100)

        class Meta:
            ordering = ('-id',)
            verbose_name = 'Type'
            verbose_name_plural = 'Types'

        def __str__(self):
            return self.title


    class Macro(Model):
        type = models.ForeignKey(
            Type,
            max_length=100,
            null=True,
            blank=True,
            on_delete=models.SET_NULL)
        tags = models.ManyToManyField(Tag, blank=True)
        category = models.ForeignKey(
            Category, null=True, blank=True, on_delete=models.SET_NULL)
        abbreviation = models.CharField(max_length=100, unique=True)
        title = models.CharField(max_length=100, verbose_name='Title')
        content = models.TextField(max_length=1000, null=True, blank=True)

        class Meta:
            ordering = ('-id',)

        def __str__(self):
            return self.title

admin.py

    from django.contrib import admin

    from import_export import resources
    from import_export.admin import ImportExportModelAdmin

    from .models import Category, Tag, Type, Macro


    class MacroResource(resources.ModelResource):

        class Meta:
            model = Macro
            skip_unchanged = True
            report_skipped = True
            exclude = ('id', )
            export_order = ('type', 'tags', 'category', 'abbreviation', 'title', 'content')


    @admin.register(Macro)
    class MacroAdmin(ImportExportModelAdmin):
        resource_class = MacroResource
        list_display = ('id', 'type', 'tags_list', 'category', 'abbreviation', 'title', 'content')
        search_fields = ('title', 'category__title', 'type__title', 'abbreviation', 'content', )

        def tags_list(self, obj):
            tags = [t for t in obj.tags.all()]
            return ' '.join(str(tags)) if tags else '-'


    @admin.register(Category)
    class CategoryAdmin(admin.ModelAdmin):
        list_display = ('id', 'title')


    @admin.register(Tag)
    class TagAdmin(admin.ModelAdmin):
        list_display = ('id', 'title')

        def __str__(self):
            return self.title


    @admin.register(Type)
    class TypeAdmin(admin.ModelAdmin):
        list_display = ('id', 'title')

Answer 1

The problem was with ForeignKey and ManyToMany fields of a database model. So django-import-export library need to get widgets for this fields.

More about it here: https://django-import-export.readthedocs.io/en/latest/api_widgets.html#import_export.widgets.ForeignKeyWidget

Solution: admin.py

        class MacroResource(resources.ModelResource):

            type = fields.Field(
                column_name='type',
                attribute='type',
                widget=ForeignKeyWidget(Type, 'title'))

            category = fields.Field(
                column_name='category',
                attribute='category',
                widget=ForeignKeyWidget(Category, 'title'))

            tags = fields.Field(
                column_name='tags',
                attribute='tags',
                widget=ManyToManyWidget(Tag, field='title'))

            class Meta:
                model = Macro
                skip_unchanged = True
                report_skipped = True
                exclude = ('id', )
                import_id_fields = ('title',)

                fields = ('type', 'tags', 'category', 'abbreviation', 'title', 'content')

instead of

        class MacroResource(resources.ModelResource):

            class Meta:
                model = Macro
                skip_unchanged = True
                report_skipped = True
                exclude = ('id', )
                export_order = ('type', 'tags', 'category', 'abbreviation', 'title', 'content')

Answer 2

Django-import-export expects the first column to be id.

If these are new objects, simply leave the id column blank. Otherwise, put the database id of the object in that field.

If you're not able to modify the file, or don't want to, and you will always be adding new rows to the database (not modifying existing ones), you can create an id field dynamically in your resource class by overriding the method before_import and forcing get_instance to always return False.

class MacroResource(resources.ModelResource):

    def before_import(self, dataset, using_transactions, dry_run, **kwargs):
        dataset.insert_col(0, col=["",]*dataset.height, header="id")

    def get_instance(self, instance_loader, row):
        return False

    class Meta:
        model = Macro
        skip_unchanged = True
        report_skipped = True
        export_order = ('type', 'tags', 'category', 'abbreviation', 'title', 'content')

Answer 3

The attributes where you are using a foreign key in the model, you need to specify the id of the parent model and not the value in the xlsx/csv file.