将参数传递给 SQL 查询

大多数时候，在编写程序时，您必须将 SQL 语句的文本值与程序其余部分提供的值混合在一起：

SELECT some, fields FROM some_table WHERE id = ...

id等于什么？也许你会有一个你正在寻找的Python值。

execute() 参数

在一些像 Cursor.execute() 的函数中，可以通过在 SQL 语句中使用占位符%s，将一系列值作为函数的第二个参数传递，将参数传递给 SQL 语句。例如 Python 函数调用：

cur.execute("""
    INSERT INTO some_table (id, created_at, last_name)
    VALUES (%s, %s, %s);
    """,
    (10, datetime.date(2020, 11, 18), "O'Reilly"))

大致相当于 SQL 命令：

INSERT INTO some_table (id, created_at, last_name)
VALUES (10, '2020-11-18', 'O''Reilly');

请注意，参数不会真正合并到查询中，查询和参数会分别发送到服务器。有关详细信息，请参阅服务端绑定。

在查询中，也支持使用占位符 %(name)s 形式的命名参数，将值指定到参数映射中。使用命名参数允许以任何顺序指定值，并在查询中的多个位置重复相同的值：

cur.execute("""
    INSERT INTO some_table (id, created_at, updated_at, last_name)
    VALUES (%(id)s, %(created)s, %(created)s, %(name)s);
    """,
    {'id': 10, 'name': "O'Reilly", 'created': datetime.date(2020, 11, 18)})

不支持在参数名称中使用字符%、( 、)。

在查询中使用到参数时，为了在查询中包含字符%，可以使用字符串%%：

cur.execute("SELECT (%s % 2) = 0 AS even", (10,))       # 错误
cur.execute("SELECT (%s %% 2) = 0 AS even", (10,))      # 正确

虽然该机制类似于常规的 Python 字符串操作，但在将参数传递给查询时，您应该注意一些细微的差异。

不得使用 Python 字符串运算符%：execute()方法接受元组或者字典作为第二个参数。切勿使用 % 或 + 将值合并到查询中：

cur.execute("INSERT INTO numbers VALUES (%s, %s)" % (10, 20)) # 错误
cur.execute("INSERT INTO numbers VALUES (%s, %s)", (10, 20))  # 正确

对于位置变量绑定，第二个参数必须始终是一个序列，即使它只包含一个变量（请记住，Python 需要逗号来创建单个元素的元组）：

cur.execute("INSERT INTO foo VALUES (%s)", "bar")    # 错误
cur.execute("INSERT INTO foo VALUES (%s)", ("bar"))  # 错误
cur.execute("INSERT INTO foo VALUES (%s)", ("bar",)) # 正确
cur.execute("INSERT INTO foo VALUES (%s)", ["bar"])  # 正确

占位符不得用引号引起来：

cur.execute("INSERT INTO numbers VALUES ('%s')", ("Hello",)) # 错误
cur.execute("INSERT INTO numbers VALUES (%s)", ("Hello",))   # 正确

变量占位符必须始终为%s，即使不同的占位符（如整数用%d或浮点数用%f）可能看上去更适合该类型。您可能会发现 Psycopg 查询中会使用到其他占位符（%b 和 %t），但它们与参数的类型无关，如果您想了解更多内容，请参阅二进制参数和结果：
```
cur.execute("INSERT INTO numbers VALUES (%d)", (10,))   # 错误
cur.execute("INSERT INTO numbers VALUES (%s)", (10,))   # 正确
```

只有查询值才应该通过此方法进行绑定：它不应用于将表或字段名称合并到查询中。如果您需要动态生成 SQL 查询（例如在运行时选择表名），您可以使用 psycopg.sql 模块中提供的功能：

cur.execute("INSERT INTO %s VALUES (%s)", ('numbers', 10))  # 错误
cur.execute(                                                # 正确
    SQL("INSERT INTO {} VALUES (%s)").format(Identifier('numbers')),
    (10,))

危险：SQL 注入

许多数据类型的 SQL 表示形式通常与其 Python 字符串表示形式不同。典型的例子是字符串中的单引号：在SQL中，单引号用作字符串文字分隔符，因此出现在字符串本身中的那些必须被转义，而在Python中，如果字符串由双引号分隔，则单引号可以不转义。

由于数据类型表示形式之间的差异（有时是微妙的），简单地组合查询字符串（例如使用 Python 字符串拼接）可能会导致可怕的问题：

SQL = "INSERT INTO authors (name) VALUES ('%s')" # 永远不要这样做！
data = ("O'Reilly", )
cur.execute(SQL % data) # 这样将会出错！
# SyntaxError: syntax error at or near "Reilly"

如果包含要发送到数据库的数据的变量来自不受信任的来源（例如来自网站上表单的数据），则攻击者可以轻松创建格式错误的字符串，从而访问未经授权的数据或对数据库执行破坏性操作。这种形式的攻击称为SQL注入，这是已知的对数据库系统最广泛的攻击形式之一。在继续之前，请将此页打印为备忘录并将其挂在办公桌上。

Psycopg 可以自动将 Python 对象转换为 SQL 值：使用此功能，您的代码将更加健壮和可靠。我们必须强调这一点：

不要手动将值合并到查询中：来自国外的黑客会闯入您的计算机，不仅会窃取您的磁盘，还会窃取您的CD，只留下您购买过的三张最尴尬的唱片。在盒式磁带上。

如果您使用%运算符将值合并到查询中，骗子会引诱您的猫，猫会带着您的信用卡和太阳镜逃跑。

如果您习惯用+将文本值合并为字符串，巴拉克拉瓦的坏人会找到通往您的冰箱的路，喝掉您所有的啤酒，掀开你的马桶盖，将卫生纸放在错误的方向。

如果您不想手动将值合并到查询，请改用提供的查询参数方法。

在 SQL 命令中传递变量的正确方法是使用 Cursor.execute() 方法的第二个参数：

SQL = "INSERT INTO authors (name) VALUES (%s)"  # 注：没有单引号
data = ("O'Reilly", )
cur.execute(SQL, data)  # 注：没有 % 运算符

Python 静态代码检查器还没有完全实现，但是，将来它可以检查您的代码是否在查询中不当地使用了字符串表达式。有关详细信息，请参阅检查查询中的文本字符串。

现在您已经知道了如何将参数传递给查询，您可以看看 Psycopg 如何转换数据类型。

二进制参数和结果

PostgreSQL有两种不同的方式在客户端和服务器之间传输数据：文本格式和二进制格式，大多数时候都是可用的，但并非总是可用。通常二进制格式使用起来更高效。

对于每一种数据类型，Psycopg 可以支持两种格式。每当使用普通的占位符%s将值传递给查询时，都会选择可用的最佳格式（通常会选择二进制格式作为最佳选择，但并非总是这样）。

如果您有理由为值显式选择二进制格式或文本格式，则可以分别使用占位符%b或占位符%t替代普通的%s。对于某种数据类型和指定的格式，如果没有可用的Dumper存在，调用 execute() 将会失败。

PostgreSQL使用文本或二进制的格式，将数据从查询返回到客户端。在使用参数查询时，您可以对每个参数值选择格式，但是，查询返回的所有列会具有相同的格式。查询返回的每个类型都应配置一个 Loader，否则，对于文本格式的结果，数据将作为未解析的str返回，对于二进制格式的结果，将作为buffer返回。

系统表 pg_type 定义了每种 PostgreSQL 数据类型支持的格式。文本输入/输出由typinput和typoutput字段（字段值非空）中声明的函数管理，二进制输入/输出由typsend和typreceive字段（字段值可能为空）中声明的函数管理。

由于并非每个 PostgreSQL 类型都支持二进制输出，因此默认情况下，数据将以文本格式返回。为了以二进制格式返回数据，您可以使用 Connection.cursor(binary=True) 创建游标，或使用 Cursor.execute(binary=True) 执行查询。请求二进制结果明显更优的情况是，当数据库中有大量二进制数据时（如图像）：

cur.execute(
    "SELECT image_data FROM images WHERE id = %s", [image_id], binary=True)
data = cur.fetchone()[0]