本教程详细介绍了在python中使用`psycopg2`库与postgresql数据库交互时，如何安全且正确地将python变量嵌入到sql查询语句中。文章重点解释了直接拼接字符串或错误使用`execute()`函数参数的常见错误，并提供了使用`%s`占位符结合参数元组（或列表）的标准化方法，以避免类型错误和潜在的sql注入风险。
在使用Python与PostgreSQL数据库进行交互时，psycopg2是一个广泛使用的库。然而，对于初学者来说，如何安全且正确地将Python变量嵌入到SQL查询语句中，是一个常见的困惑点。直接将变量拼接进SQL字符串不仅容易出错，更会带来严重的安全隐患——SQL注入。本节将详细阐述正确的做法，并分析错误方法的原因。
错误示范与原因分析
许多开发者在尝试将变量引入SQL查询时，可能会直观地尝试以下方式：
import psycopg2
# 假设 inputed_email 是一个从用户输入获取的变量
inputed_email = "test@example.com"
conn = psycopg2.connect("dbname=postgres user=postgres password=postgres")
cur = conn.cursor()
try:
# 错误示范：直接将变量作为 execute() 的第三个参数
cur.execute("SELECT password FROM user WHERE email = ", inputed_email, ";")
print(cur.fetchone())
except TypeError as e:
print(f"发生错误: {e}")
finally:
cur.close()
conn.close()执行上述代码，通常会遇到 TypeError: function takes at most 2 arguments (3 given) 这样的错误。这个错误明确指出，cur.execute() 方法最多接受两个参数，而我们却提供了三个。execute() 方法的设计并非像 print() 函数那样可以接受任意数量的分隔参数。
此外，即使没有 TypeError，如果尝试通过字符串拼接的方式将变量直接插入SQL语句：
立即学习“Python免费学习笔记（深入）”；
# 错误示范：通过字符串拼接引入变量 # query = "SELECT password FROM user WHERE email = '" + inputed_email + "';" # cur.execute(query)
这种方法虽然在语法上可能不会立即报错（取决于变量内容），但它极易受到SQL注入攻击。如果 inputed_email 变量包含恶意SQL代码（例如 '; DROP TABLE user; --），拼接后的查询将执行意想不到的操作，对数据库造成破坏。
正确方法：使用占位符 (%s)
psycopg2 提供了一种安全且推荐的方式来处理SQL查询中的变量：使用占位符。在SQL查询字符串中，任何需要替换为变量值的地方，都应使用 %s 作为占位符。然后，将所有变量值作为一个元组或列表传递给 execute() 方法的第二个参数。
以下是修正后的代码示例：
import psycopg2
# 假设 inputed_email 是一个从用户输入获取的变量
inputed_email = "test@example.com"
conn = psycopg2.connect("dbname=postgres user=postgres password=postgres")
cur = conn.cursor()
try:
# 正确方法：使用 %s 占位符和参数元组
# 注意：即使只有一个参数，第二个参数也必须是可迭代对象（如元组或列表）
cur.execute("SELECT password FROM user WHERE email = %s", (inputed_email,))
# 获取查询结果
result = cur.fetchone()
if result:
print(f"用户密码: {result[0]}")
else:
print("未找到匹配用户。")
except psycopg2.Error as e:
print(f"数据库操作错误: {e}")
finally:
# 确保关闭游标和连接以释放资源
cur.close()
conn.close()在这个示例中：
- SQL查询字符串中，email = 后面的值被替换为 %s。
- execute() 方法的第二个参数是一个元组 (inputed_email,)。即使只有一个变量，也必须将其包装在一个元组（或列表）中。元组末尾的逗号 (inputed_email,) 是为了明确表示这是一个包含单个元素的元组，而不是一个简单的括号表达式。
当 execute() 方法被调用时，psycopg2 库会负责将元组中的值安全地插入到SQL查询中的相应 %s 占位符位置，并进行适当的类型转换和转义，从而有效防止SQL注入。
处理多个变量的查询
如果SQL查询中需要使用多个变量，只需在查询字符串中使用多个 %s 占位符，并将所有变量按照它们在查询中出现的顺序，作为元组（或列表）的元素传递给 execute() 方法。
import psycopg2
# 假设有两个变量
user_email = "jane.doe@example.com"
user_lastname = "Doe"
conn = psycopg2.connect("dbname=postgres user=postgres password=postgres")
cur = conn.cursor()
try:
# 示例：查询同时匹配 email 和 lastname 的用户
sql_query = "SELECT password FROM user WHERE email = %s AND lastname = %s"
cur.execute(sql_query, (user_email, user_lastname)) # 多个参数按顺序放入元组
result = cur.fetchone()
if result:
print(f"找到匹配用户，密码: {result[0]}")
else:
print("未找到匹配用户。")
except psycopg2.Error as e:
print(f"数据库操作错误: {e}")
finally:
cur.close()
conn.close()核心优势与注意事项
安全性：使用占位符是防止SQL注入攻击的黄金法则。psycopg2 会自动对参数进行适当的转义，确保它们被视为数据值而不是可执行的SQL代码。
可读性与维护性：将SQL查询逻辑与变量值分离，使代码更清晰，易于阅读和维护。
性能优化：数据库服务器能够更好地缓存和重用带有占位符的查询计划，从而提高查询执行效率。
数据类型处理：psycopg2 会根据Python变量的类型自动将其转换为PostgreSQL兼容的类型。
-
资源管理：无论操作成功与否，始终确保关闭游标 (cur.close()) 和数据库连接 (conn.close())，以释放系统资源。在实际应用中，推荐使用 with 语句来自动管理连接和游标的生命周期，如下所示：
import psycopg2 inputed_email = "test@example.com" try: with psycopg2.connect("dbname=postgres user=postgres password=postgres") as conn: with conn.cursor() as cur: cur.execute("SELECT password FROM user WHERE email = %s", (inputed_email,)) result = cur.fetchone() if result: print(f"用户密码: {result[0]}") else: print("未找到匹配用户。") # 如果是 INSERT/UPDATE/DELETE 操作，需要提交事务 # conn.commit() except psycopg2.Error as e: print(f"数据库操作错误: {e}") 事务管理：对于 INSERT、UPDATE、DELETE 等修改数据库的操作，在 execute() 之后需要调用 conn.commit() 来提交事务，使更改永久生效。对于 SELECT 查询，通常不需要 commit。
通过遵循这些最佳实践，开发者可以确保在Python中与PostgreSQL数据库进行安全、高效且可靠的交互。
