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

I have a list and want to pass thru django raw sql.

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

Here is my list

这是我的列表

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

地区=(“我们”,“CA”,“英国”)

I am pasting a part of raw sql here.

我在这里粘贴了一部分原始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])

结果= MMCode.objects。raw('select assigner, assignee from mm_code，其中日期在%s和%s之间，国家代码在%s'，[fromdate,todate,region])

Now it gives the below error, when i execute it in django python shell

现在，当我在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")

I have tried by passing the tuple also but there is no use. Can some one help me.

我也试过通过元组，但没有用。有人能帮我吗?

Thanks Vikram

由于Vikram

5 个解决方案

#1

For PostgreSQL at least, a list/tuple parameter is converted into an array in SQL, e.g.

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

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

When this is inserted into the given query, it results in invalid SQL -

当它插入到给定的查询中时，将导致无效的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']

However, the 'in' clause in SQL is logically equivalent to -

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

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

... and when this query is filled with the parameters, the resulting SQL is valid and works -

…当这个查询被参数填满时，得到的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'])

I'm not sure if this works in the other databases though, and whether or not this changes how the query is planned.

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

#2

Casting the list to a tuple does work in Postgres, although the same code fails under sqlite3 with DatabaseError: near "?": syntax error so it seems this is backend-specific. Your line of code would become:

将列表强制转换为tuple在Postgres中是有效的，尽管在sqlite3下，使用DatabaseError: near "?:语法错误，所以这似乎是向后特定的。您的代码行将变成:

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)])

结果= MMCode.objects。raw('select assigner, mm_code中的assignee， %s和%s和country_code的日期在%s'，[fromdate,todate,tuple(region)])

I tested this on a clean Django 1.5.1 project with the following in bar/models.py:

我在一个干净的Django 1.5.1项目上测试了这一点，项目的bar/models是:

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)

then at the 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)
[]

(note that the query line is changed slightly here, to use the default table name created by Django, and to include the id column in the output so that the ORM doesn't complain)

(注意，这里的查询行稍微做了修改，使用Django创建的默认表名，并在输出中包含id列，以便ORM不抱怨)

#3

This is not a great solution, because you must make sure your "region" values are correctly escaped for SQL. However, this is the only thing I could get to work with Sqlite:

这不是一个很好的解决方案，因为您必须确保您的“区域”值为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])

#4

I ran into exactly this problem today. Django has changed (we now have RawSQL() and friends!), but the general solution is still the same.

我今天遇到了这个问题。Django已经改变了(我们现在有RawSQL()和friends了!)，但是一般的解决方案仍然是一样的。

According to https://*.com/a/283801/532513 the general idea is to explicitly add the same numbers of placeholders to your SQL string as there are elements in your region array.

根据https://*.com/a/283801/532513，一般的想法是显式地在SQL字符串中添加与区域数组中的元素相同的占位符数目。

Your code would then look like this:

你的代码会是这样的:

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)

Your sql string would then first become ... between %s and %s and country_code in (%s, %s, %s) ... and your params would be effectively [fromdate, todate, 'US', 'CA', 'UK']. This way, you allow the database backend to correctly escape and potentially encode each of the country codes.

您的sql字符串将首先成为……在%s和%s和country_code之间(%s， %s， %s)…你的params会有效[从日期，到现在，'US'， 'CA'， 'UK']。这样，就允许数据库后端正确地转义并可能对每个国家代码进行编码。

#5

-1

Well i'm not against raw sql but you can use: MMCode.objects.filter(country_code__in=region, date__range=[fromdate,todate])

我不是反对原始sql，但是你可以使用:MMCode.objects。过滤器(country_code__in =地区,date__range =[fromdate,迄今为止])

hope this helps.

希望这个有帮助。

#1