python SQLite 参数替换和引号
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1005552/
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
SQLite parameter substitution and quotes
提问by BlogueroConnor
I have this line that works OK:
我有这条线可以正常工作：
c.execute('select cleanseq from cleanseqs WHERE newID="%s"'%name)
But I want to use SQLite parameter substitution instead instead of string substitution (because I see herethat this is safer).
但我想使用 SQLite 参数替换而不是字符串替换（因为我在这里看到这更安全）。
This is my (failed) try:
这是我的（失败）尝试：
t = (name,)
c.execute('select cleanseq from cleanseqs WHERE newID="?"',t)
But this line returns:
但这一行返回：
'Incorrect number of bindings supplied. The current statement uses 0, and there are 1 supplied.'
'提供的绑定数量不正确。当前语句使用 0，并且提供了 1。
So the left part of my statement doesn't work. I am supplying one binding (name, in t) but seems that the question mark (?) is not being parsed. If I delete the quotes sourronding the ?, it works. But I want the quotes to remain there since I remember that there are cases where I need them.
所以我声明的左边部分不起作用。我正在提供一个绑定（名称，在 t 中），但似乎问号 (?) 没有被解析。如果我删除 ? 的引号，它会起作用。但我希望引号保留在那里，因为我记得有些情况下我需要它们。
So the question is: How do I convert this line:
所以问题是：我如何转换这一行：
c.execute('select cleanseq from cleanseqs WHERE newID="%s"'%name)
采纳答案by John Machin
about """If I delete the quotes sourronding the ?, it works. But I want the quotes to remain there since I remember that there are cases where I need them."""
about """如果我删除 ? 的引号，它会起作用。但我希望引号保留在那里，因为我记得有些情况下我需要它们。"""
What you remember from when you were building the whole SQL statement yourself is irrelevant.
您在自己构建整个 SQL 语句时所记得的内容是无关紧要的。
The new story is: mark with a ? each place in the SQL statement where you want a value substituted then pass in a tuple containing one value per ? -- it's that simple; the wrapper will quote any strings to make sure that they are acceptable SQL constants.
新故事是：用 ? SQL 语句中您想要替换值的每个位置，然后传入一个包含一个值的元组？——就这么简单；包装器将引用任何字符串以确保它们是可接受的 SQL 常量。
回答by anonymous
To anyone who like me found this thread and got really frustrated by people ignoring the fact that sometimes you can't just ignore the quotes (because you're using say a LIKE command) you can fix this by doing something to the effect of:
对于像我这样的人发现这个线程并且因为人们忽略了一个事实而感到非常沮丧，因为有时您不能忽略引号（因为您使用的是 LIKE 命令），您可以通过执行以下操作来解决此问题：
var = name + "%"
c.execute('SELECT foo FROM bar WHERE name LIKE ?',(var,))
This will allow you to substitute in wildcards in this situation.
这将允许您在这种情况下替换通配符。
回答by Alex Martelli
I find the named-parameter binding style much more readable -- and sqlite3supports it:
我发现命名参数绑定样式更具可读性——并sqlite3支持它：
c.execute('SELECT cleanseq FROM cleanseqs WHERE newID=:t', locals())
Note: passing {'t': t}or dict(t=t)instead of locals()would be more punctiliously correct, but in my opinion it would interfere with readability when there are several parameters and/or longer names. In any case, I do find the :tbetter than the ?;-).
注意：传递{'t': t}ordict(t=t)而不是locals()会更准确，但在我看来，当有多个参数和/或更长的名称时，它会干扰可读性。无论如何，我确实发现:t比?;-)更好。
回答by UncleO
Lose the quotes around ?
丢失周围的引号？
c.execute('select cleanseq from cleanseqs WHERE newID=?',(t,))
It's treating it as the string "?".
它将它视为字符串“？”。
Do you need to use double quotes around the whole expression, instead of singles?
您是否需要在整个表达式周围使用双引号，而不是单引号？
回答by Alex Morega
The library will handle quoting and escaping for you. Simply write your query like this:
图书馆将为您处理引用和转义。只需像这样编写您的查询：
c.execute('SELECT cleanseq FROM cleanseqs WHERE newID=?', (name,))
回答by Dean Van Greunen
Regular User
普通用户
just noticed that you'll have to do this manual by using the unsecure method of sql_string = "other sql surger here.. fieldname=\""+value+"\";"
刚刚注意到你必须使用不安全的方法 sql_string = "other sql sumger here.. fieldname=\""+value+"\";"
its the only way you'll get it to parse correctly. using SQLite for win ce. and well left me with no other alternative, just escape your values before putting them in else you'll most likely end up with a very sad database from sql injections :'( lol
这是您让它正确解析的唯一方法。使用 SQLite 进行 win ce。并且让我别无选择，只需在将它们放入之前转义您的值，否则您很可能最终会从 sql 注入中得到一个非常悲伤的数据库:'(lol
