PostgreSQL psycopg2 奇怪的行为

在本文中，我们将介绍 PostgreSQL 数据库连接工具 psycopg2 的一些奇怪行为，并提供示例说明。

问题描述

psycopg2 是 Python 语言中最常用的 PostgreSQL 数据库连接工具之一。虽然它功能强大且易于使用，但在一些特定情况下，它可能会表现出一些奇怪的行为。下面我们将探讨两个常见的问题。

问题一：自动提交

在默认情况下，psycopg2 的连接是自动提交的。这意味着每次执行一条 SQL 语句后，数据将立即被提交到数据库中。然而，在某些情况下，我们希望将多个 SQL 语句放在同一个事务中进行处理，以确保数据的一致性和完整性。然而，由于默认设置的自动提交特性，这种期望可能无法满足。

下面是一个示例代码，说明了这个问题：

import psycopg2

conn = psycopg2.connect(database="mydb", user="myuser", password="mypassword", host="localhost")

# 禁用自动提交
conn.autocommit = False

cur = conn.cursor()

cur.execute("INSERT INTO employees (name, age) VALUES ('Alice', 30)")

cur.execute("SELECT COUNT(*) FROM employees")
result1 = cur.fetchone()
print("查询结果1:", result1)

# 没有调用 conn.commit()，数据不会被提交到数据库

cur.execute("INSERT INTO employees (name, age) VALUES ('Bob', 35)")

cur.execute("SELECT COUNT(*) FROM employees")
result2 = cur.fetchone()
print("查询结果2:", result2)

# 再次没有调用 conn.commit()，数据仍然不会被提交到数据库

# 提交事务
conn.commit()

cur.execute("SELECT COUNT(*) FROM employees")
result3 = cur.fetchone()
print("查询结果3:", result3)

# 数据已保存到数据库

在上述示例中，我们使用 conn.autocommit = False 来禁用自动提交，然后执行了两条插入语句，但没有调用 conn.commit()。只有在调用 conn.commit() 之后，数据才会被提交到数据库中。这个问题在初始使用 psycopg2 时经常让人困惑。

问题二：参数化查询

在编写数据库应用程序时，为了避免 SQL 注入漏洞，我们通常会使用参数化查询。然而，在 psycopg2 中，参数化查询稍有不同，可能会导致一些奇怪的行为。让我们看一个示例：

import psycopg2

conn = psycopg2.connect(database="mydb", user="myuser", password="mypassword", host="localhost")
cur = conn.cursor()

# 错误示例
cur.execute("SELECT * FROM employees WHERE age = %s" % 30)
rows = cur.fetchall()
print(rows)

# 正确示例
cur.execute("SELECT * FROM employees WHERE age = %s", (30,))
rows = cur.fetchall()
print(rows)

上述示例中，我们尝试查询年龄等于 30 的员工记录。在错误示例中，我们使用了字符串拼接的方式将参数传递给 SQL 查询语句。然而，这样做会导致 SQL 注入漏洞。正确的示例是使用参数化查询的方式，将参数作为一个元组传递给 cur.execute() 方法。