Django ORM生成SQL时误将模型Choice值识别为列的问题排查

当注释掉library_level相关的if判断代码时，查询能正常执行；但添加新的Q表达式后，Q(field=value)中的value会被当作字段处理，导致SQL报错“列不存在”。

问题代码 #

query = (
    (Q(domains=[]) | Q(domains__contains=domain))  # rule matches domain
    & (Q(klass=[]) | Q(klass__contains=dom_class))  # rule matches class
    & (Q(standards__contains=cdisc_version))  # rule matches standards
    & (~Q(exclusions__contains=domain))  # domain is not in excluded list
    & (~Q(exclusions__contains=dom_class))  # dom_class is not in excluded list
)

if not library_level:
    query &= ~Q(rule_type=DataCheckRuleType.DATASET_CHECK)

required_checks = DataCheckRule.objects.filter(query).values(
    "rule_id",
    "domains",
    "message",
    "description",
    "exclusions",
    "klass",
    "standards",
    "detail_string",
    "ex_domains",
    "rule_type",
    "params",
)
sql_query = str(required_checks.query)

生成的异常SQL #

SELECT "datacheck_rules"."rule_id", "datacheck_rules"."domains", "datacheck_rules"."message", "datacheck_rules"."description", "datacheck_rules"."exclusions", "datacheck_rules"."klass", "datacheck_rules"."standards", "datacheck_rules"."detail_string", "datacheck_rules"."ex_domains", "datacheck_rules"."rule_type", "datacheck_rules"."params" FROM "datacheck_rules" WHERE ((("datacheck_rules"."domains" = '[]' OR "datacheck_rules"."domains" @> '"DATA"') AND ("datacheck_rules"."klass" = '[]' OR "datacheck_rules"."klass" @> '"UNKNOWN"') AND "datacheck_rules"."standards" @> '"SDTMIG 3.2"' AND NOT ("datacheck_rules"."exclusions" @> '"DATA"') AND NOT ("datacheck_rules"."exclusions" @> '"UNKNOWN"')) AND NOT ("datacheck_rules"."rule_type" = dataset_check))

可以看到dataset_check未被添加引号，被SQL解析器当作列名而非字符串值。

后续执行代码 #

relevant_checks = pd.read_sql(sql_query, connections["default"])

相关定义 #

选项类定义 #

class DataCheckRuleType(models.Choices):
    DATASET_CHECK = "dataset_check"

模型定义 #

class BaseModel(models.Model):
    class Meta:
        abstract = True

    rule_type = models.CharField(max_length=20, choices=DataCheckRuleType.choices)


class DataCheckRule(BaseModel):
    domains = models.JSONField(default=list)
    exclusions = models.JSONField(default=list)
    klass = models.JSONField(default=list)
    standards = models.JSONField(default=list)
    ex_domains = models.JSONField(default=list)

问题原因与解决方法 #

问题核心是Django的models.Choices枚举值的引用方式错误：
直接使用DataCheckRuleType.DATASET_CHECK返回的是一个Choice对象（包含value和label属性），而非字符串值。Django ORM处理Q表达式时，会将这个对象识别为字段引用，导致生成的SQL中没有给值添加引号。

解决方法：直接调用枚举值的.value属性获取字符串值

if not library_level:
    query &= ~Q(rule_type=DataCheckRuleType.DATASET_CHECK.value)

也可以直接使用字符串字面量"dataset_check"，但使用枚举的.value更符合代码规范，避免硬编码带来的维护问题。

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