sql - 在 django raw sql 中传递列表或元组作为参数

Question

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

这是我的 list
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

至少对于 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'])

我不确定这是否适用于其他数据库，以及这是否会改变查询的计划方式。