SQLite 参数替换和引号 [英] SQLite parameter substitution and quotes

问题描述

我有这条线可以正常工作:

I have this line that works OK:

c.execute('select cleanseq from cleanseqs WHERE newID="%s"'%name)

但我想使用 SQLite 参数替换而不是字符串替换(因为我看到 here 这样更安全).

But I want to use SQLite parameter substitution instead instead of string substitution (because I see here that this is safer).

这是我的(失败)尝试:

This is my (failed) try:

t = (name,)
c.execute('select cleanseq from cleanseqs WHERE newID="?"',t)

但这一行返回:

'绑定数量不正确提供.当前语句使用0，并且提供了 1 个.'

'Incorrect number of bindings supplied. The current statement uses 0, and there are 1 supplied.'

所以我声明的左边部分不起作用.我正在提供一个绑定(名称，在 t 中)，但似乎问号 (?) 没有被解析.如果我删除 ? 的引号，它会起作用.但我希望引号保留在那里，因为我记得有些情况下我需要它们.

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.

所以问题是:我如何转换这一行:

So the question is: How do I convert this line:

c.execute('select cleanseq from cleanseqs WHERE newID="%s"'%name)

推荐答案

about """如果我删除 ? 的引号，它会起作用.但我希望引号保留在那里，因为我记得有些情况下我需要他们."""

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."""

当你自己构建整个 SQL 语句时你记得什么是无关紧要的.

What you remember from when you were building the whole SQL statement yourself is irrelevant.

新故事是:用 ?SQL 语句中您想要替换值的每个位置，然后传入一个包含每个值的元组?——就这么简单；包装器将引用任何字符串以确保它们是可接受的 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.

这篇关于SQLite 参数替换和引号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！