如何在Django中关联多模型表以实现数据导入导出？

一、先把模型关联起来 #

选Musician_1做主表，另外两个表用OneToOneField关联到主表，这样三个表的行就能对应同一个完整对象。修改后的模型代码：

from django.db import models

class Musician_1(models.Model):
    first_name_1 = models.CharField(max_length=50)
    last_name_1 = models.CharField(max_length=50)
    instrument_1 = models.CharField(max_length=100)

class Musician_2(models.Model):
    # 关联主表，删除主表时子表也跟着删除
    musician = models.OneToOneField(Musician_1, on_delete=models.CASCADE, related_name='musician_2')
    first_name_2 = models.CharField(max_length=50)
    last_name_2 = models.CharField(max_length=50)
    instrument_2 = models.CharField(max_length=100)

class Musician_3(models.Model):
    musician = models.OneToOneField(Musician_1, on_delete=models.CASCADE, related_name='musician_3')
    first_name_3 = models.CharField(max_length=50)
    last_name_3 = models.CharField(max_length=50)
    instrument_3 = models.CharField(max_length=100)

二、做一个合并表单 #

把三个模型的字段整合到一个表单里，提交时一次性保存三个表的数据：

from django import forms
from .models import Musician_1, Musician_2, Musician_3

class MusicianCombinedForm(forms.Form):
    # 整合三个表的所有字段
    first_name_1 = forms.CharField(max_length=50)
    last_name_1 = forms.CharField(max_length=50)
    instrument_1 = forms.CharField(max_length=100)
    
    first_name_2 = forms.CharField(max_length=50)
    last_name_2 = forms.CharField(max_length=50)
    instrument_2 = forms.CharField(max_length=100)
    
    first_name_3 = forms.CharField(max_length=50)
    last_name_3 = forms.CharField(max_length=50)
    instrument_3 = forms.CharField(max_length=100)

    def save(self):
        # 先保存主表数据
        musician1 = Musician_1.objects.create(
            first_name_1=self.cleaned_data['first_name_1'],
            last_name_1=self.cleaned_data['last_name_1'],
            instrument_1=self.cleaned_data['instrument_1']
        )
        # 再保存两个子表，绑定到主表实例
        Musician_2.objects.create(
            musician=musician1,
            first_name_2=self.cleaned_data['first_name_2'],
            last_name_2=self.cleaned_data['last_name_2'],
            instrument_2=self.cleaned_data['instrument_2']
        )
        Musician_3.objects.create(
            musician=musician1,
            first_name_3=self.cleaned_data['first_name_3'],
            last_name_3=self.cleaned_data['last_name_3'],
            instrument_3=self.cleaned_data['instrument_3']
        )
        return musician1

三、视图处理表单提交 #

写个视图处理表单的GET和POST请求：

from django.shortcuts import render, redirect
from .forms import MusicianCombinedForm

def add_musician(request):
    if request.method == 'POST':
        form = MusicianCombinedForm(request.POST)
        if form.is_valid():
            form.save()
            return redirect('musician_list')  # 跳转到你的数据列表页
    else:
        form = MusicianCombinedForm()
    return render(request, 'add_musician.html', {'form': form})

对应的模板add_musician.html用常规表单写法即可：

<form method="post">
    {% csrf_token %}
    {{ form.as_p }}
    <button type="submit">保存</button>
</form>

四、数据导入导出 #

用django-import-export库实现，比自己写代码高效。

第一步：安装并注册库 #

pip install django-import-export

然后在settings.py的INSTALLED_APPS中添加'import_export'。

第二步：自定义资源类 #

把三个表的字段合并成一个导入导出资源：

from import_export import resources
from import_export.fields import Field
from .models import Musician_1, Musician_2, Musician_3

class MusicianCombinedResource(resources.ModelResource):
    # 声明子表的字段
    first_name_2 = Field()
    last_name_2 = Field()
    instrument_2 = Field()
    first_name_3 = Field()
    last_name_3 = Field()
    instrument_3 = Field()

    class Meta:
        model = Musician_1
        # 指定要导入导出的所有字段
        fields = ('first_name_1', 'last_name_1', 'instrument_1', 
                  'first_name_2', 'last_name_2', 'instrument_2',
                  'first_name_3', 'last_name_3', 'instrument_3')

    # 导出时从子表提取数据
    def dehydrate_first_name_2(self, obj):
        return obj.musician_2.first_name_2 if hasattr(obj, 'musician_2') else ''

    def dehydrate_last_name_2(self, obj):
        return obj.musician_2.last_name_2 if hasattr(obj, 'musician_2') else ''

    def dehydrate_instrument_2(self, obj):
        return obj.musician_2.instrument_2 if hasattr(obj, 'musician_2') else ''

    def dehydrate_first_name_3(self, obj):
        return obj.musician_3.first_name_3 if hasattr(obj, 'musician_3') else ''

    def dehydrate_last_name_3(self, obj):
        return obj.musician_3.last_name_3 if hasattr(obj, 'musician_3') else ''

    def dehydrate_instrument_3(self, obj):
        return obj.musician_3.instrument_3 if hasattr(obj, 'musician_3') else ''

    # 导入时同时保存子表数据
    def before_save_instance(self, instance, using_transactions, dry_run):
        data = self.data[self.get_row_index(instance)]
        instance.save()
        # 用update_or_create避免重复创建数据
        Musician_2.objects.update_or_create(
            musician=instance,
            defaults={
                'first_name_2': data.get('first_name_2'),
                'last_name_2': data.get('last_name_2'),
                'instrument_2': data.get('instrument_2')
            }
        )
        Musician_3.objects.update_or_create(
            musician=instance,
            defaults={
                'first_name_3': data.get('first_name_3'),
                'last_name_3': data.get('last_name_3'),
                'instrument_3': data.get('instrument_3')
            }
        )

第三步：在admin中注册（或自定义视图） #

如果用admin管理数据，直接注册主表并绑定资源类：

from django.contrib import admin
from import_export.admin import ImportExportModelAdmin
from .models import Musician_1
from .resources import MusicianCombinedResource

@admin.register(Musician_1)
class MusicianAdmin(ImportExportModelAdmin):
    resource_class = MusicianCombinedResource

这样在admin后台就能看到导入导出按钮，操作和单表完全一致。

如果不想用admin，自定义导出视图也很简单：

from django.http import HttpResponse
from .resources import MusicianCombinedResource

def export_musicians(request):
    resource = MusicianCombinedResource()
    dataset = resource.export()
    response = HttpResponse(dataset.csv, content_type='text/csv')
    response['Content-Disposition'] = 'attachment; filename="musicians.csv"'
    return response

五、查询完整对象 #

要获取一个完整对象的所有参数，用select_related一次性关联查询，避免多次数据库请求：

musician1 = Musician_1.objects.select_related('musician_2', 'musician_3').get(id=1)
# 把所有字段整合到一个字典中
full_data = {
    'first_name_1': musician1.first_name_1,
    'last_name_1': musician1.last_name_1,
    'instrument_1': musician1.instrument_1,
    'first_name_2': musician1.musician_2.first_name_2,
    'last_name_2': musician1.musician_2.last_name_2,
    'instrument_2': musician1.musician_2.instrument_2,
    'first_name_3': musician1.musician_3.first_name_3,
    'last_name_3': musician1.musician_3.last_name_3,
    'instrument_3': musician1.musician_3.instrument_3,
}

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