Django filter(icontains).extra生成错误的SQL语法（无引号）
我正在尝试构建一个自定义查询，并希望在使用filter()之后应用.extra()。这个语句看起来是这样的：
V.objects.filter(v_id__product__icontains=name)
现在它生成了有效的SQL语句，但在name周围没有引号：
WHERE `v_id`.`product` LIKE %xxx%
但是当我添加.extra()语句后：
V.objects.filter(id__product__icontains=name).extra(where=[concat_str],params=[version,'%','%'])
查询变得无效，因为%xxx%周围没有引号：
WHERE `v_id`.`product` LIKE %xxx% AND 'yyy' LIKE CONCAT('%',version,'%')
我只需要在%xxx%周围加上单引号，这样就可以使它有效：
WHERE `vulnerabilities_cpeid`.`product` LIKE '%xxx%' AND 'yyy' LIKE CONCAT('%',version,'%')
不过，我不知道怎么让Django在使用icontains时把%xxx%放进单引号里。希望能得到一些帮助。谢谢！
完整的错误追踪信息：
INFO 2014-07-21 11:33:55,515 views: SELECT `vulnerabilities_vulnerability`.`identifier` FROM `vulnerabilities_vulnerability` INNER JOIN `vulnerabilities_vulnerability_cpe_id` ON (`vulnerabilities_vulnerability`.`id` = `vulnerabilities_vulnerability_cpe_id`.`vulnerability_id`) INNER JOIN `vulnerabilities_cpeid` ON (`vulnerabilities_vulnerability_cpe_id`.`cpeid_id` = `vulnerabilities_cpeid`.`id`) WHERE (`vulnerabilities_cpeid`.`product` LIKE %accountsservice% AND '0.6.15-2ubuntu9.7' LIKE CONCAT('%',version,'%'))
ERROR 2014-07-21 11:33:55,517 django.request: Internal Server Error: /vulndb/inventory/
Traceback (most recent call last):
File "/home/sapegin/vulndb_mercurial/vulndb/HANA/PYTHON/Python/lib/python2.6/site-packages/django/core/handlers/base.py", line 111, in get_response
response = callback(request, *callback_args, **callback_kwargs)
File "/home/sapegin/vulndb_mercurial/vulndb/vulndb/vulnerabilities/views.py", line 1650, in inventory
if ((vulnerabilities is not None) and (vulnerabilities.count() > 0)):
File "/home/sapegin/vulndb_mercurial/vulndb/HANA/PYTHON/Python/lib/python2.6/site-packages/django/db/models/query.py", line 351, in count
return self.query.get_count(using=self.db)
File "/home/sapegin/vulndb_mercurial/vulndb/HANA/PYTHON/Python/lib/python2.6/site-packages/django/db/models/sql/query.py", line 418, in get_count
number = obj.get_aggregation(using=using)[None]
File "/home/sapegin/vulndb_mercurial/vulndb/HANA/PYTHON/Python/lib/python2.6/site-packages/django/db/models/sql/query.py", line 384, in get_aggregation
result = query.get_compiler(using).execute_sql(SINGLE)
File "/home/sapegin/vulndb_mercurial/vulndb/HANA/PYTHON/Python/lib/python2.6/site-packages/django/db/models/sql/compiler.py", line 818, in execute_sql
cursor.execute(sql, params)
File "/home/sapegin/vulndb_mercurial/vulndb/HANA/PYTHON/Python/lib/python2.6/site-packages/django/db/backends/util.py", line 40, in execute
return self.cursor.execute(sql, params)
File "/home/sapegin/vulndb_mercurial/vulndb/HANA/PYTHON/Python/lib/python2.6/site-packages/django/db/backends/mysql/base.py", line 119, in execute
return self.cursor.execute(query, args)
File "/home/sapegin/vulndb_mercurial/vulndb/HANA/PYTHON/Python/lib/python2.6/site-packages/MySQL_python-1.2.4-py2.6-linux-x86_64.egg/MySQLdb/cursors.py", line 201, in execute
self.errorhandler(self, exc, value)
File "/home/sapegin/vulndb_mercurial/vulndb/HANA/PYTHON/Python/lib/python2.6/site-packages/MySQL_python-1.2.4-py2.6-linux-x86_64.egg/MySQLdb/connections.py", line 36, 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 '0.6.15-2ubuntu9.7'' LIKE CONCAT(''%'',version,''%''))' at line 1")
ERROR 2014-07-21 11:33:55,517 django.request: Internal Server Error: /vulndb/inventory/
2 个回答
1
在Django中，数据库的包装器会自动加上引号。比如说，'icontains'这个操作符会被转换成 'icontains': 'LIKE %s'，其中 %s 会被你要搜索的内容加上百分号后再放进去。
Django内部会用 quote_name 这个函数来处理引号的事情。
def quote_name(self, name):
if name.startswith("`") and name.endswith("`"):
return name # Quoting once is enough.
return "`%s`" % name
也许对于你的情况，使用原始查询会是个更好的选择：
Model.objects.raw('Select .... FROM .... WHERE ....', params=None, translations=None)
可以看看这个链接: https://docs.djangoproject.com/en/dev/topics/db/sql/
我觉得alecxe说得对，.query中的调试信息不够用。
1
我敢打赌，你的错误是因为在.extra调用的参数中多了引号。试着把%s周围的引号去掉，看看这样是否能解决问题。