python 在带有参数的游标/查询中使用“like”（django）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/643930/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using "like" in a cursor/query with a parameter in python (django)
提问by Juan129
I know this may be something stupid but I decided to ask any way.
我知道这可能很愚蠢，但我决定以任何方式询问。
I've been trying to query something like:
我一直在尝试查询类似的内容：
cursor.execute("select col1, col2 \
from my_tablem \
where afield like '%%s%'
and secondfield = %s
order by 1 desc " % (var1, var2) )
But I get an error in the like sentence. It doesn't like the extra % which I need to get all the results that contains the first %s value.
但是我在类似句子中遇到错误。它不喜欢额外的 %，我需要获得包含第一个 %s 值的所有结果。
Ideas?
想法？
TIA!
蒂亚！
回答by S.Lott
First, why aren't you using the Django ORM for this?
首先，您为什么不为此使用 Django ORM？
MyClass.objects.filter( aField__contains=var1, secondField__exact=var2 )
Second, be sure you're getting the SQL you expect.
其次，确保您获得了您期望的 SQL。
stmt= "select... afield like '%%%s%%' and secondfield = '%s'..." % ( var1, var2 )
print stmt
cursor.execute( stmt )
Third, your method has a security hole called a SQL Injection Attack. You really should not be doing SQL like this.
第三，您的方法有一个安全漏洞，称为 SQL 注入攻击。你真的不应该像这样执行 SQL。
If you absolutely must do things outside Django's ORM, you have to use bind variables in your query, not string substitution. See http://docs.djangoproject.com/en/dev/topics/db/sql/#performing-raw-sql-queries.
如果你绝对必须在 Django 的 ORM 之外做一些事情，你必须在你的查询中使用绑定变量，而不是字符串替换。请参阅http://docs.djangoproject.com/en/dev/topics/db/sql/#performing-raw-sql-queries。
回答by brobas
can hack string '%' into search string?
可以将字符串 '%' 破解为搜索字符串吗？
var1 = '%' + var1 + '%'
then query normally:
cursor.execute("select col1, col2
from my_tablem where afield like %s
and secondfield = %s
order by 1 desc " , [var1, var2] )
回答by Seth Gottlieb
I had a similar issue. I was trying to search among concatenated name fields. My query was something like:
我有一个类似的问题。我试图在串联的名称字段中进行搜索。我的查询是这样的：
sql = """SELECT * from auth_user WHERE lower(first_name) || ' ' || lower(last_name) = '%%%s%%'"""
User.objects.raw(sql, [q])
The problem was that the %% were breaking my query. The solution I wound up with was:
问题是 %% 破坏了我的查询。我最终的解决方案是：
q = '%' + q + '%'
sql = """SELECT * from auth_user WHERE lower(first_name) || ' ' || lower(last_name) = %s"""
User.objects.raw(sql, [q])
回答by Edu Gasser
Persona.objects.raw("**SELECT** id,concat_ws(' ',nombre,apellido) **AS** nombre_completo **FROM** persona **GROUP BY** id **HAVING** concat_ws(' ',nombre,apellido) **ILIKE** '%s' " % ('%%' + query + '%%'))
(Postgresql 9.1)
(PostgreSQL 9.1)
