.extra生成错误的SQL语法(没有引号)
.extra生成错误的SQL语法(没有引号)
提问于 2014-07-21 16:29:36
我正在尝试构建一个自定义查询，并希望在filter()之后应用.extra()。该声明看上去如下：
V.objects.filter(v_id__product__icontains=name)现在，它生成了有效的SQL，但是，它的名字周围没有引号：
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将%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
Stack Overflow用户
回答已采纳
发布于 2014-07-21 17:28:26
我敢打赌，错误在于您的.extra调用参数中有额外的引号。删除%s周围的引号，看看这是否解决了问题。
Stack Overflow用户
发布于 2014-07-22 06:53:55
在Django中，DatabaseWrapper将自动添加引号。将“图标”运算符转换为'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中的调试是不够的。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/24870065
复制相关文章
相似问题
腾讯云开发者
