Django REST Framework存JSON数据报错：api_ingredient.meal_id列不存在

错误信息 #

File "C:\Users\OSMAN MERT\AppData\Local\Programs\Python\Python310\lib\site-packages\django\db\backends\utils.py", line 84, in _execute   
    with self.db.wrap_database_errors:
File "C:\Users\OSMAN MERT\AppData\Local\Programs\Python\Python310\lib\site-packages\django\db\utils.py", line 91, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
File "C:\Users\OSMAN MERT\AppData\Local\Programs\Python\Python310\lib\site-packages\django\db\backends\utils.py", line 89, in _execute   
    return self.cursor.execute(sql, params)
File "C:\Users\OSMAN MERT\AppData\Local\Programs\Python\Python310\lib\site-packages\django\db\backends\sqlite3\base.py", line 477, in execute
    return Database.Cursor.execute(self, query, params)
django.db.utils.OperationalError: no such column: api_ingredient.meal_id

问题根源 #

1. 模型设计冲突 #

Ingredient模型中定义了meal = models.ForeignKey(Meal, on_delete=models.CASCADE)（一对多关联），同时又创建了MealIngredient中间表实现多对多关联，两者逻辑矛盾，导致数据库字段定义冲突。

2. 数据导入缺失必填字段 #

Ingredient的meal外键是必填项，但导入代码中执行Ingredient.objects.get_or_create(name=name_in)时未传入meal参数，ORM尝试创建数据时无法填充meal_id字段，触发数据库报错。

修复方案 #

第一步：修正模型设计 #

根据MealIngredient中间表的存在，Meal和Ingredient应为多对多关系，需删除Ingredient中的meal外键，二选一以下方案：

方案A：使用Django自动生成的多对多中间表

class Meal(models.Model):
    id = models.AutoField(primary_key=True)
    name = models.CharField(max_length=100)
    is_vegetarian = models.BooleanField(default=False)
    is_vegan = models.BooleanField(default=False)
    # 直接定义多对多关系，Django自动生成中间表
    ingredients = models.ManyToManyField('Ingredient', related_name='meals')

    def __str__(self):
        return self.name

class Ingredient(models.Model):
    id = models.AutoField(primary_key=True)
    name = models.CharField(max_length=100)
    groups = models.CharField(max_length=100)
    # 删除原meal外键

    def __str__(self):
        return self.name

class Option(models.Model):
    id = models.AutoField(primary_key=True)
    ingredient = models.ForeignKey(Ingredient, on_delete=models.CASCADE)
    name = models.CharField(max_length=100)
    quality = models.CharField(max_length=100)
    price = models.FloatField()
    per_amount = models.CharField(max_length=100)

    def __str__(self):
        return self.name

# 删除原MealIngredient模型

方案B：保留自定义中间表（需添加额外字段时用）

class Meal(models.Model):
    id = models.AutoField(primary_key=True)
    name = models.CharField(max_length=100)
    is_vegetarian = models.BooleanField(default=False)
    is_vegan = models.BooleanField(default=False)
    # 通过through指定自定义中间表
    ingredients = models.ManyToManyField('Ingredient', through='MealIngredient', related_name='meals')

    def __str__(self):
        return self.name

class Ingredient(models.Model):
    id = models.AutoField(primary_key=True)
    name = models.CharField(max_length=100)
    groups = models.CharField(max_length=100)
    # 删除原meal外键

    def __str__(self):
        return self.name

class Option(models.Model):
    id = models.AutoField(primary_key=True)
    ingredient = models.ForeignKey(Ingredient, on_delete=models.CASCADE)
    name = models.CharField(max_length=100)
    quality = models.CharField(max_length=100)
    price = models.FloatField()
    per_amount = models.CharField(max_length=100)

    def __str__(self):
        return self.name

class MealIngredient(models.Model):
    id = models.AutoField(primary_key=True)
    meal = models.ForeignKey(Meal, on_delete=models.CASCADE)
    ingredient = models.ForeignKey(Ingredient, on_delete=models.CASCADE)
    # 可添加额外字段，如created_at = models.DateTimeField(auto_now_add=True)

第二步：重新同步数据库 #

1. 删除旧数据库文件（如db.sqlite3）
2. 删除app下migrations目录中除__init__.py外的所有文件
3. 执行命令：

python manage.py makemigrations
python manage.py migrate

第三步：修正数据导入代码 #

对应方案A的导入代码

import json
from .models import Meal, Ingredient, Option

path = r"C:\Users\OSMAN MERT\Desktop\menu\menu_core\menu.json"

with open(path) as f:
  data = json.loads(f.read())

# 先导入所有Ingredient并填充groups字段
for ingre in data["ingredients"]:
    ingre_name = ingre["name"]
    ingre_groups = ingre["groups"]
    ingre_obj, created = Ingredient.objects.get_or_create(
        name=ingre_name,
        defaults={'groups': ingre_groups}
    )

    # 导入Option
    for opti in ingre["options"]:
        Option.objects.get_or_create(
            ingredient=ingre_obj,
            name=opti["name"],
            quality=opti["quality"],
            price=opti["price"],
            per_amount=opti["per_amount"]
        )

# 导入Meal并关联Ingredient
for meal in data["meals"]:
    name = meal["name"]
    meal_obj, created = Meal.objects.get_or_create(
        name=name,
        defaults={
            'is_vegetarian': meal.get('is_vegetarian', False),
            'is_vegan': meal.get('is_vegan', False)
        }
    )
    for ingredient in meal["ingredients"]:
        ingre_obj = Ingredient.objects.get(name=ingredient["name"])
        meal_obj.ingredients.add(ingre_obj)

对应方案B的导入代码

仅修改Meal与Ingredient的关联部分：

# 替换方案A中导入Meal的代码块
for meal in data["meals"]:
    name = meal["name"]
    meal_obj, created = Meal.objects.get_or_create(
        name=name,
        defaults={
            'is_vegetarian': meal.get('is_vegetarian', False),
            'is_vegan': meal.get('is_vegan', False)
        }
    )
    for ingredient in meal["ingredients"]:
        ingre_obj = Ingredient.objects.get(name=ingredient["name"])
        MealIngredient.objects.get_or_create(meal=meal_obj, ingredient=ingre_obj)

额外注意事项 #

• 确保menu.json字段与模型字段对应，比如Ingredient的groups字段需从json中读取赋值
• 使用defaults参数设置非唯一字段，避免重复创建数据
• 若json中Meal包含唯一id字段，可将其作为get_or_create的查询条件

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