Python sqlite3 字符串格式化 [英] Python sqlite3 string formatting

问题描述

我有一个这样的功能:

def func(self, id):
    self.cursor.execute("SELECT * FROM my_table WHERE id=?", (id,))

当我在 id 中传递一个整数值时，这有效:

This works when I pass an integer value in id:

obj.func(id=55)

现在我可能想像这样重用这个函数:

Now I might want to reuse this function like so:

obj.func(id="associated_id")

其中 associated_id 实际上是 my_table 中的第二列.但是，这不起作用(即使有一行 id== associated_id，它也找不到结果).

Where associated_id is actually a second column in my_table. However this doesn't work (it finds no results even though there is a row where id==associated_id).

如果我像这样更改函数，它会起作用:

It works if I change the function like so:

def func(self, id):
    self.cursor.execute("SELECT * FROM my_table WHERE id=%s" % str(id))

为什么第一个版本不起作用?有没有办法使用 sqlite3.execute 参数而不是原生 python 字符串格式来解决这个问题?

Why does the first version not work? Is there any way to solve this problem using sqlite3.execute parameters instead of native python string formatting?

推荐答案

在第一种情况下，您正在进行参数绑定.这是正确的，sqlite 库会将值 55 绑定到查询调用.在第二种情况下，您不应该使用参数绑定，因为 sql 将等同于:

In the first case you are doing parameter binding. Which is correct, sqlite libraries will bind the value 55 to the query call. In the second case you should not use parameter binding because the sql will be something equivalent to:

SELECT * FROM my_table WHERE id='associated_id'

因为绑定调用检测到您正在传递一个字符串，然后它在内部将其视为字符串.

Because the binding call detects that you are passing a string and then it treats it internally like a string.

当你这样做

"SELECT * FROM my_table WHERE id=%s" % str(id)

您不进行参数绑定，而只是将 SQL 原样传递给 sqlite.

You don't do parameter binding but simply pass the SQL as it is to sqlite.

关于参数绑定的一些文档:

Some documentation on parameter binding in:

http://www.sqlite.org/c3ref/bind_blob.html

这篇关于Python sqlite3 字符串格式化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！