如何在Django中为“ in” SQL子句传递值列表 [英] How to pass a list of values for an `in` SQL clause in Django

问题描述

我需要在我正在编写的Django应用中使用原始SQL查询。 SQL查询在其中语句的子句中包含子句：

I need to use a raw SQL query in a Django app I am writing. The SQL query contains an in clause in the where statement:

select *
from abc_mymodel
where some_fk in (1,2,3)
and some_status = 'foo'

我非常支持将SQL参数作为参数传递。对于单值值很容易做到这一点。但是，我不确定在子句中如何（或是否）完成此操作。理想情况下，我想执行以下操作：

I am a big proponent of passing SQL params as parameters. This is easily done for the single value ones. However, I am not sure how (or if) this can be done for in clauses. Ideally I would like to do something like:

sql = """
    select *
    from abc_mymodel
    where some_fk in %s
    and some_status = %s
"""
my_list = [1,2,3]
my_status = 'foo'
trinkets = MyModel.objects.raw(
    sql,
    params=(my_list, my_status)
)

我知道我可以使用字符串组合在子句中编写子句，并对其余值使用params。但是，我很好奇是否可以在子句中对使用参数。

I know I can use string composition to write the in clause and use params for the remaining values. However, I am curious if it is possible to use params for in clauses as well.

推荐答案

根据 Django raw（） ，

params 是参数的列表或字典。您将在查询字符串中使用％s 占位符作为列表...此类占位符将被params参数中的参数替换。

params is a list or dictionary of parameters. You’ll use %s placeholders in the query string for a list... Such placeholders will be replaced with parameters from the params argument.

方便地，原始查询在django shell中非常容易测试。如果您只输入 raw（）查询，它将显示带有结果查询的 RawQuerySet ：

Conveniently, raw queries are very easy to test in a django shell. If you simply input your raw() query, it will print a RawQuerySet with your resulting query:

>>> from django.contrib.auth.models import User
>>> pk_list = (1, 3, 6)
>>> User.objects.raw('SELECT * FROM auth_user WHERE id IN %s', params=[pk_list])
<RawQuerySet: 'SELECT * FROM auth_user WHERE id IN (1, 3, 6)'>

如您所见， params 放在原始查询中。在您的示例中，您需要将 my_list 更改为元组（否则它将看起来像 IN [1,2,3] ）并将您的 params 输入更改为列表（ params = [my_list，my_status] ）。

As you can see, whatever is in params is placed into the raw query. In your example, you need to change my_list to a tuple (or else it will look like "IN [1,2,3]") and change your params input to a list (params=[my_list, my_status]).

这篇关于如何在Django中为“ in” SQL子句传递值列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！