Django集成MongoDB查询数据触发DatabaseError问题求助

错误详情 #

DatabaseError at /foods/41007428/65bffaaab212b521886f0e47/
未提供异常消息
请求方法: GET
请求URL: http://127.0.0.1:8000/foods/41007428/65bffaaab212b521886f0e47/
Django版本: 4.1.13
异常类型: DatabaseError
异常位置: C:\Users\omuya\projects\food_backend\foodenv\Lib\site-packages\djongo\cursor.py, 第59行, execute方法中
触发视图: foods.views.foodByCategoryandCode
Python可执行文件: C:\Users\omuya\projects\food_backend\foodenv\Scripts\python.exe
Python版本: 3.12.1
Python路径:
['C:\Users\omuya\projects\food_backend\food_api_project',
'C:\Users\omuya\AppData\Local\Programs\Python\Python312\python312.zip',
'C:\Users\omuya\AppData\Local\Programs\Python\Python312\DLLs',
'C:\Users\omuya\AppData\Local\Programs\Python\Python312\Lib',
'C:\Users\omuya\AppData\Local\Programs\Python\Python312',
'C:\Users\omuya\projects\food_backend\foodenv',
'C:\Users\omuya\projects\food_backend\foodenv\Lib\site-packages']
服务器时间: Mon, 05 Feb 2024 23:09:11 +0000

异常直接原因：执行失败的SQL
SELECT "foods_product"."_id", "foods_product"."title", "foods_product"."foodTags", "foods_product"."foodType", "foods_product"."code", "foods_product"."isAvailable", "foods_product"."restaurant_id", "foods_product"."rating", "foods_product"."ratingCount", "foods_product"."description", "foods_product"."price", "foods_product"."additives", "foods_product"."imageUrl", "foods_product"."category_id", "foods_product"."time", "foods_product"."created_at", "foods_product"."updated_at" FROM "foods_product" WHERE ("foods_product"."category_id" = %(0)s AND "foods_product"."code" = %(1)s AND "foods_product"."isAvailable")
参数: (('65bffaaab212b521886f0e47', '41007428'),)
Djongo版本: 1.3.6

需求 #

根据code和category对Product数据过滤查询，仅返回isAvailable=True的条目。

代码实现 #

视图函数 #

@api_view(['GET'])
def foodByCategoryandCode(request, catid, code):
    foods = Product.objects.filter(category__pk = catid, code = code,isAvailable = True)
    serializer = ProductSerializer(foods, many=True)
    return Response(serializer.data)

URL配置 #

path('foods/<str:code>/<str:catid>/', foodByCategoryandCode, name='random-by-code-and-category'),

Product模型 #

from django.db import models
from bson.objectid import ObjectId
from django.core.validators import MinValueValidator, MaxValueValidator

class Product(models.Model):
    _id = models.CharField(primary_key=True, editable=False,max_length=255)
    title = models.CharField(max_length=100, unique=True)
    foodTags = models.JSONField(default = [])
    foodType = models.JSONField(default = [])
    code = models.CharField(max_length=10)
    isAvailable = models.BooleanField()
    restaurant = models.ForeignKey(Restaurant, on_delete=models.CASCADE)
    rating = models.DecimalField(max_digits=3, decimal_places=1,validators=[MinValueValidator(1), MaxValueValidator(5)])
    ratingCount = models.CharField(max_length=10)
    description = models.TextField()
    price = models.DecimalField(max_digits=5, decimal_places=2)
    additives = models.JSONField(default = [])
    imageUrl = models.JSONField(default = [])
    category = models.ForeignKey(Category, on_delete=models.CASCADE)
    time = models.CharField(max_length=10)
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)

    def __str__(self):
        return self.title
    
    def save(self, *args, **kwargs):
        if not self._id:
            self._id = str(ObjectId())
        super().save(*args, **kwargs)

问题排查与修复方案 #

1. 参数顺序不匹配 #

URL定义的参数顺序是<str:code>/<str:catid>/，但视图函数接收顺序为catid, code，导致传入的参数值完全颠倒（请求URL中41007428是code，65bffaaab212b521886f0e47是category ID，但视图中会把41007428当作category ID查询）。

修复方式二选一：

• 调整视图函数参数顺序：

  @api_view(['GET'])
  def foodByCategoryandCode(request, code, catid):
      foods = Product.objects.filter(category__pk=catid, code=code, isAvailable=True)
      serializer = ProductSerializer(foods, many=True)
      return Response(serializer.data)
  

• 调整URL路径顺序：

  path('foods/<str:catid>/<str:code>/', foodByCategoryandCode, name='random-by-code-and-category'),
  

2. SQL语法错误 #

生成的SQL中AND "foods_product"."isAvailable"缺少布尔值比较，属于Djongo兼容性问题。

修复方式：
显式指定布尔值比较方式，避免生成无效SQL：

foods = Product.objects.filter(category__pk=catid, code=code, isAvailable__exact=True)

3. 依赖版本不兼容 #

Djongo 1.3.6与Django 4.1.13存在兼容性问题，建议升级Djongo到适配版本：

pip install --upgrade djongo

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