Django导入XLSX至数据库遇KeyError: 'id'，请求修正代码

问题详情 #

在Django中导入XLSX文件到数据库时触发以下错误：

Line number: 1 - 'id'
1, 28799772, 306509594, "GOLDEN NILE AAA" MChJ, 1726294, Toshkent shahri, 
Chilonzor tumani, X.TURSUNQULOV KOʻCHASI, 38-UY, 974079981, makhmudjon.khakimjonov@mail.ru, 47190

Traceback (most recent call last):
File "C:\Users\AbdulAziz\AppData\Local\Programs\Python\Python38\lib\site-packages\import_export\resources.py", line 708, in import_row
instance, new = self.get_or_init_instance(instance_loader, row)
File "C:\Users\AbdulAziz\AppData\Local\Programs\Python\Python38\lib\site-packages\import_export\resources.py", line 378, in get_or_init_instance
instance = self.get_instance(instance_loader, row)
File "C:\Users\AbdulAziz\AppData\Local\Programs\Python\Python38\lib\site-packages\import_export\resources.py", line 365, in get_instance
import_id_fields = [
File "C:\Users\AbdulAziz\AppData\Local\Programs\Python\Python38\lib\site-packages\import_export\resources.py", line 366, in <listcomp>
self.fields[f] for f in self.get_import_id_fields()
KeyError: 'id'

相关代码 #

models.py #

class Company(models.Model):
    
    id = models.AutoField(primary_key=True)
    okpo = models.PositiveIntegerField(null=True,blank=True)
    inn = models.PositiveIntegerField(null=True,blank=False)
    name = models.CharField(max_length=25,blank=False,default='Tashkilot Nomi')
    soato = models.PositiveIntegerField(null=True,blank=False)
    address = models.CharField(max_length=50,blank=True,null=True,default='Tashkilot Adresi')
    street = models.CharField(max_length=50,blank=True,null=True,default='Tashkilot Street')
    phonenumber = models.CharField(max_length=13, blank=True, null=True,default='+998')
    email = models.EmailField(max_length=25,default='tashkilot@email.com')
    oked = models.PositiveIntegerField(null=True,blank=False)

views.py #

def import_file(request):
    if request.method == 'POST' :
        dataset = Dataset()
        new_employee = request.FILES['myfile']
        data_import = dataset.load(new_employee.read())
        result = CompanyResources.import_data(dataset,dry_run=True)
        if not result.has_errors():
            CompanyResources.import_data(dataset,dry_run=False)        
    return render(request, 'home.html',{})

admin.py #

class CompanyResources(resources.ModelResource):
    class Meta:
        models = Company
        fields = [field.name for field in Company._meta.fields if field.name != 'id']
        exclude = ['id',]
        import_id_fields = ['id',]

class CompanyAdmin(ImportExportModelAdmin):
    resource_class = CompanyResources
    list_display = [field.name for field in Company._meta.fields]
    
admin.site.register(Company,CompanyAdmin)

错误原因 #

CompanyResources的配置存在矛盾：

• 通过exclude = ['id',]和fields列表排除了id字段，资源中不包含该字段
• 但同时设置import_id_fields = ['id',]，要求用id作为导入时的唯一标识字段
• 这导致系统查找id字段时找不到，触发KeyError
• 另外Meta类里的models是笔误，应为model（单数）

解决方案 #

根据需求选择以下两种修正方式：

方式1：导入时包含id字段（XLSX文件第一列是id） #

如果XLSX文件中包含id列，需要保留该字段在资源中：

class CompanyResources(resources.ModelResource):
    class Meta:
        model = Company  # 修正models为model
        fields = [field.name for field in Company._meta.fields]  # 包含所有字段，包括id
        import_id_fields = ['id',]  # 用id作为唯一标识

方式2：不导入id，自动生成（用inn作为唯一标识） #

如果不需要手动指定id，让Django自动生成AutoField，可以用必填字段inn作为导入时的匹配标识：

class CompanyResources(resources.ModelResource):
    class Meta:
        model = Company  # 修正models为model
        fields = [field.name for field in Company._meta.fields if field.name != 'id']
        exclude = ['id',]
        import_id_fields = ['inn',]  # 用inn作为唯一匹配字段

额外优化：指定导入格式 #

views.py中导入XLSX时，明确指定格式避免默认识别错误：

def import_file(request):
    if request.method == 'POST' :
        dataset = Dataset()
        new_employee = request.FILES['myfile']
        # 明确指定格式为xlsx
        data_import = dataset.load(new_employee.read(), format='xlsx')
        result = CompanyResources.import_data(dataset,dry_run=True)
        if not result.has_errors():
            CompanyResources.import_data(dataset,dry_run=False)        
    return render(request, 'home.html',{})

内容的提问来源于stack exchange，提问作者AbdulAziz Umarov