Django导入CSV数据时ForeignKey外键赋值报错解决方法

Django的ForeignKey外键字段在ORM层面有固定的赋值规则：

• 直接使用你定义的外键字段名作为参数赋值时，必须传入关联模型（此处为Matches）的实例对象，不能直接传主键的数字/字符串值
• Django会为每个外键字段自动生成一个附加参数，格式为<外键字段名>_id，这个参数可以直接接收关联记录的主键值，不需要查询实例

你的代码里直接把CSV读取到的字符串格式的match_id值（比如报错中的'1'）传给了match_id参数，不符合ORM的赋值要求，因此抛出ValueError，Deliveries表的写入流程直接中断，没有数据插入。

另外你的外键命名不符合Django惯例：把ForeignKey字段命名为match_id会导致Django自动生成的数据库列名变成match_id_id，语义重复，容易引发参数名混淆。

优先选择性能最高的直接传主键值的方式，不需要逐行查询Matches表，适合大批量数据导入场景：

1. 保留现有模型定义的前提下，实例化Deliveries时，把外键赋值参数从match_id改为match_id_id，同时把CSV读取到的字符串类型ID转为整数类型
2. 补充所有字段的类型转换、空值处理：CSV读取的所有值默认是字符串，IntegerField字段需要转int，允许为空的字段需要把空字符串转为None，避免类型错误、非空约束报错
3. 用bulk_create替代逐行save()，大幅提升批量导入速度，避免频繁连接数据库的性能损耗

修正后的导入代码示例 #

from django.core.management.base import BaseCommand
import csv
from match.models import Matches, Deliveries

class Command(BaseCommand):
    help = 'import match and delivery data'

    def handle(self, *args, **options):
        # 工具函数：处理CSV空值，空字符串转为None
        def clean_empty(v):
            v = v.strip()
            return v if v else None

        # 导入Matches表
        match_list = []
        with open('matches.csv', 'r', encoding='utf-8') as f:
            match_reader = csv.DictReader(f)
            for match in match_reader:
                match_list.append(Matches(
                    id=int(match['id']),
                    season=int(match['season']),
                    city=clean_empty(match['city']),
                    date=match['date'] if match['date'].strip() else None,
                    team1=match['team1'],
                    team2=match['team2'],
                    toss_winner=clean_empty(match['toss_winner']),
                    toss_decision=clean_empty(match['toss_decision']),
                    result=clean_empty(match['result']),
                    dl_applied=clean_empty(match['dl_applied']),
                    winner=clean_empty(match['winner']),
                    win_by_runs=int(match['win_by_runs']) if match['win_by_runs'].strip() else None,
                    win_by_wickets=int(match['win_by_wickets']) if match['win_by_wickets'].strip() else None,
                    player_of_match=clean_empty(match['player_of_match']),
                    venue=clean_empty(match['venue']),
                    umpire1=clean_empty(match['umpire1']),
                    umpire2=clean_empty(match['umpire2']),
                    umpire3=clean_empty(match['umpire3'])
                ))
                # 每1000条批量插入一次
                if len(match_list) >= 1000:
                    Matches.objects.bulk_create(match_list)
                    match_list = []
            if match_list:
                Matches.objects.bulk_create(match_list)

        # 导入Deliveries表
        delivery_list = []
        with open('deliveries.csv', 'r', encoding='utf-8') as file:
            delivery_reader = csv.DictReader(file)
            for delivery in delivery_reader:
                delivery_list.append(Deliveries(
                    # 外键字段名为match_id，直接传主键值用自动生成的match_id_id参数
                    match_id_id=int(delivery['match_id']),
                    inning=int(delivery['inning']),
                    batting_team=delivery['batting_team'],
                    bowling_team=delivery['bowling_team'],
                    over=int(delivery['over']),
                    ball=int(delivery['ball']),
                    batsman=delivery['batsman'],
                    non_striker=delivery['non_striker'],
                    bowler=delivery['bowler'],
                    is_super_over=int(delivery['is_super_over']),
                    wide_runs=int(delivery['wide_runs']),
                    bye_runs=int(delivery['bye_runs']),
                    legbye_runs=int(delivery['legbye_runs']),
                    noball_runs=int(delivery['noball_runs']),
                    penalty_runs=int(delivery['penalty_runs']),
                    batsman_runs=int(delivery['batsman_runs']),
                    extra_runs=int(delivery['extra_runs']),
                    total_runs=int(delivery['total_runs']),
                    player_dismissed=clean_empty(delivery['player_dismissed']),
                    dismissal_kind=clean_empty(delivery['dismissal_kind']),
                    fielder=clean_empty(delivery['fielder'])
                ))
                if len(delivery_list) >= 1000:
                    Deliveries.objects.bulk_create(delivery_list)
                    delivery_list = []
            if delivery_list:
                Deliveries.objects.bulk_create(delivery_list)

可选优化（推荐） #

将Deliveries模型中的外键字段重命名为match，符合Django命名惯例：

class Deliveries(models.Model):
    match = models.ForeignKey(Matches,on_delete=models.CASCADE)
    # 其余字段不变

重命名后，外键赋值可以用更符合直觉的写法：

• 传Matches实例：match=match_instance
• 直接传主键值：match_id=int(delivery['match_id'])

不会再出现match_id_id这类语义重复的参数名，代码可读性更高。注意重命名字段后需要生成并执行数据库迁移。

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