python - Python中准备好的语句和参数化查询之间的混淆

Question

据我了解，准备好的语句(主要)是一种数据库功能，允许您将参数与使用此类参数的代码分开。示例:

PREPARE fooplan (int, text, bool, numeric) AS
    INSERT INTO foo VALUES($1, $2, $3, $4);
EXECUTE fooplan(1, 'Hunter Valley', 't', 200.00);

参数化查询代替手动字符串插值，所以不是这样做

cursor.execute("SELECT FROM tablename WHERE fieldname = %s" % value)

我们可以的

cursor.execute("SELECT FROM tablename WHERE fieldname = %s", [value])

现在，似乎准备好的语句大部分用于数据库语言，参数化查询主要用于连接数据库的编程语言，尽管我看到了这条规则的异常(exception)情况。

问题是询问prepared statement和parameterized query的区别会带来很多困惑。诚然，它们的目的是相同的，但它们的方法似乎截然不同。然而，有sources表示两者相同。 MySQLdb 和 Psycopg2 似乎支持参数化查询，但不支持准备好的语句(例如 here 用于 MySQLdb 和 TODO list for postgres drivers 或 this answer 在 sqlalchemy 组中)。其实有一个gist实现支持预准备语句的 psycopg2 游标和最小 explanation关于它。还有一个suggestion在 psycopg2 中对游标对象进行子类化以手动提供准备好的语句。

我想得到以下问题的权威答案:

• 准备好的语句和参数化查询之间是否存在有意义的区别？这在实践中重要吗？如果使用参数化查询，是否需要担心预处理语句？

• 如果有区别，Python生态系统中prepared statements的现状如何？哪些数据库适配器支持预处理语句？

Answer 1

• Prepared statement:对数据库上预解释查询例程的引用，准备接受参数

• 参数化查询:由您的代码以这样一种方式进行的查询，您在 alongside 一些具有占位符值的 SQL 中传递值，通常是 ? 或%s 或类似的东西。

这里的混淆似乎源于(明显)缺乏直接获取准备好的语句对象的能力和将值传递到非常像一个“参数化查询”方法的能力之间的区别......因为它是一个，或者至少为你做一个。

例如:SQLite3库的C接口(interface)有很多工具可以使用prepared statement objects ，但 Python api几乎没有提到他们。您不能随时准备一份声明并多次使用它。相反，您可以使用 sqlite3.executemany(sql, params) 获取 SQL 代码，internally 创建一个准备好的语句，然后在循环中使用该语句来处理每个你给的迭代中的参数元组。

Python 中的许多其他 SQL 库的行为方式相同。使用准备好的语句对象可能会很痛苦，并且可能导致歧义，并且在像 Python 这样的语言中，它对原始执行速度具有如此的清晰性和易用性，它们并不是真正的最佳选择。从本质上讲，如果您发现自己不得不对每次都重新解释的复杂 SQL 查询进行数十万或数百万次调用，那么您可能应该以不同的方式做事。无论如何，有时人们希望他们可以直接访问这些对象，因为如果您在数据库服务器周围保留相同的预处理语句，就不必一遍又一遍地解释相同的 SQL 代码；大多数情况下，这会从错误的方向解决问题，您将在其他地方或通过重组代码获得更多的节省。*

也许更重要的是，准备好的语句和参数化查询使您的数据保持卫生并与 SQL 代码分开。 这比字符串格式更可取!您应该将参数化查询和准备好的语句以一种或另一种形式视为将变量数据从应用程序传递到数据库的唯一方法强>。如果您尝试以其他方式构建 SQL 语句，它不仅运行速度会明显变慢，而且您将容易受到 other problems 的攻击。 .

*例如，通过生成要在生成器函数中输入数据库的数据，然后使用 executemany() 从生成器中一次性插入所有数据，而不是调用execute() 每次循环。

tl;博士

参数化查询是单个操作，它在内部生成准备好的语句，然后传入您的参数并执行。

编辑:很多人看到这个答案!我还想澄清一下，许多数据库引擎也有预准备语句的概念，可以使用纯文本查询语法显式构建，然后在客户端 session 的生命周期内重用(例如在 postgres 中)。有时您可以控制是否缓存查询计划以节省更多时间。一些框架会自动使用这些(我已经看到 rails 的 ORM 积极地这样做了)，有时很有用，有时在准备查询的形式排列时会损害它们。

此外，如果您想挑剔，参数化查询不会总是在后台使用准备好的语句；如果可能，他们应该这样做，但有时它只是在参数值中格式化。这里的“准备好的语句”和“参数化查询”之间的真正区别实际上只是您使用的 API 的形状。