在 django 原始 sql 中传递列表或元组作为参数

Question

我有一个列表，想通过 django 原始 sql 传递。

这是我的列表

region = ['US','CA','UK']

我在这里粘贴了原始 sql 的一部分。

results = MMCode.objects.raw('select 分配者，受让人 from mm_code where date between %s and %s and Country_code in %s',[fromdate,todate,region])

现在给出以下内容错误，当我在 django python shell 中执行它时，

Traceback (most recent call last):
File "<console>", line 1, in <module>
File "/usr/local/lib/python2.6/dist-packages/django/db/models/query.py", line 1412, in __iter__
query = iter(self.query)
File "/usr/local/lib/python2.6/dist-packages/django/db/models/sql/query.py", line 73, in __iter__
self._execute_query()
File "/usr/local/lib/python2.6/dist-packages/django/db/models/sql/query.py", line 87, in _execute_query
self.cursor.execute(self.sql, self.params)
File "/usr/local/lib/python2.6/dist-packages/django/db/backends/util.py", line 15, in execute
return self.cursor.execute(sql, params)
File "/usr/local/lib/python2.6/dist-packages/django/db/backends/mysql/base.py", line 86, in execute
return self.cursor.execute(query, args)
File "/usr/lib/pymodules/python2.6/MySQLdb/cursors.py", line 166, in execute
self.errorhandler(self, exc, value)
File "/usr/lib/pymodules/python2.6/MySQLdb/connections.py", line 35, in defaulterrorhandler
raise errorclass, errorvalue
DatabaseError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1")

我也尝试传递元组，但没有用。有人可以帮助我吗？

谢谢 维克拉姆

Answer 1

至少对于 PostgreSQL，列表/元组参数在 SQL 中被转换为数组，例如

ARRAY['US', 'CA', 'UK']

，当将其插入到给定查询中时，它会导致无效的 SQL -

SELECT assigner, assignee FROM mm_code
WHERE date BETWEEN '2014-02-01' AND '2014-02-05'
AND country_code IN ARRAY['US', 'CA', 'UK']

但是，SQL 中的“in”子句在逻辑上等同于 -

SELECT assigner, assignee FROM mm_code
WHERE date BETWEEN %s AND %s
AND country_code = ANY(%s)

..当这个查询填满参数时，生成的 SQL 是有效的并且有效 -

SELECT assigner, assignee FROM mm_code
WHERE date BETWEEN '2014-02-01' AND '2014-02-05'
AND country_code = ANY(ARRAY['US', 'CA', 'UK'])

我不确定这是否在其他数据库中有效，以及这是否会改变查询的计划方式。

Answer 2

将列表转换为元组在 Postgres 中确实有效，尽管相同的代码在 sqlite3 下失败，并出现 DatabaseError: close "?": syntax error ，所以看起来这是特定于后端的。您的代码行将变为：

results = MMCode.objects.raw('select 分配者，受让人 from mm_code where date between %s and %s and Country_code in %s',[fromdate,todate,tuple(region)] ）

我在一个干净的 Django 1.5.1 项目上进行了测试，在 bar/models.py 中使用以下内容：

from django.db import models

class MMCode(models.Model):
    assigner = models.CharField(max_length=100)
    assignee = models.CharField(max_length=100)
    date = models.DateField()
    country_code = models.CharField(max_length=2)

然后在 shell 中：（

>>> from datetime import date
>>> from bar.models import MMCode
>>> 
>>> regions = ['US', 'CA', 'UK']
>>> fromdate = date.today()
>>> todate = date.today()
>>> 
>>> results = MMCode.objects.raw('select id, assigner, assignee from bar_mmcode where date between %s and %s and country_code in %s',[fromdate,todate,tuple(regions)])
>>> list(results)
[]

请注意，此处的查询行略有更改，以使用默认值由 Django 创建的表名，并在输出中包含 id 列，这样 ORM 就不会抱怨）

Answer 3

我今天正好遇到了这个问题。 Django 已经改变（我们现在有 RawSQL() 和朋友！），但一般解决方案仍然是相同的。

根据https://stackoverflow.com/a/283801/532513，总体思路是显式添加相同数量的SQL 字符串的占位符，因为 region 数组中有元素。

然后，您的代码将如下所示：

sql = 'select assigner, assignee from mm_code where date between %s and %s and country_code in ({0})'\
      .format(','.join(['%s'] * len(region)))
results = MMCode.objects.raw(sql, [fromdate,todate] + region)

您的 sql 字符串将首先变为 ... Between %s and %s 和country_code in (%s, %s, %s) ... 以及您的参数实际上将是[fromdate, todate, 'US', 'CA', 'UK']。这样，您就允许数据库后端正确转义并可能对每个国家/地区代码进行编码。

Answer 4

这不是一个很好的解决方案，因为您必须确保您的“区域”值正确 转义为 SQL。然而，这是我可以使用 Sqlite 的唯一方法：

sql = ('select assigner, assignee from mm_code '
    'where date between %%s and %%s and country_code in %s' % (tuple(region),))
results = MMCode.objects.raw(sql, [fromdate,todate])

Answer 5

好吧，我不反对原始 sql，但你可以使用：
MMCode.objects.filter(country_code__in=region, date__range=[fromdate,todate])

希望这会有所帮助。