在Django Raw SQL中将列表或元组作为参数传递

Question

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

这是我的清单

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

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

results = MMCode.objects.raw('select assigner, assignee 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

将列表转换为元组在Postgres中可以正常工作，尽管在sqlite3下相同的代码DatabaseError: near "?": syntax error也会失败，因此看来这是后端特定的。您的代码行将变为：

results = MMCode.objects.raw('select assigner, assignee 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)

然后在外壳上：

>>> 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不会抱怨）