本文介绍了如何在 PostgreSQL 中使用循环语句向数据库表中插入数据，重点讲解了循环计数器的正确使用方法，以及如何避免 SQL 注入风险，推荐使用参数化查询来构建安全的数据库操作。通过本文，你将学会如何高效且安全地向 PostgreSQL 数据库表中批量插入数据。
使用循环批量插入数据
在 PostgreSQL 中，我们经常需要使用循环来批量插入数据。一个常见的场景是，我们有一个包含多个数据的列表，需要将这些数据逐条插入到数据库表中。以下是一个使用 Python 和 psycopg2 库实现此功能的示例：
import psycopg2
# 假设已建立数据库连接 conn
conn = psycopg2.connect(database="your_database", user="your_user", password="your_password", host="your_host", port="your_port")
artist_name = ['Madonna', 'Slayer', 'Disturbed', 'Michael Jackson', 'Katty Parry']
with conn.cursor() as cur:
id_num = 0
for artists in artist_name:
id_num += 1
cur.execute(f"""INSERT INTO Artist (Id, Name)
VALUES ('{id_num}', '{artists}')
ON CONFLICT DO NOTHING""");
conn.commit() # 提交事务
conn.close() # 关闭连接上述代码尝试将 artist_name 列表中的艺术家姓名插入到 Artist 表中。然而，这段代码存在一个潜在的问题：每次循环迭代时，id_num 都会被重置为 0，导致每次插入的 Id 都为 1，触发了 ON CONFLICT DO NOTHING 子句，最终只有第一个元素被插入。
修复计数器问题
要解决这个问题，我们需要将计数器 id_num 的初始化移到循环之外：
import psycopg2
# 假设已建立数据库连接 conn
conn = psycopg2.connect(database="your_database", user="your_user", password="your_password", host="your_host", port="your_port")
artist_name = ['Madonna', 'Slayer', 'Disturbed', 'Michael Jackson', 'Katty Parry']
with conn.cursor() as cur:
id_num = 0
for artists in artist_name:
id_num += 1
cur.execute(f"""INSERT INTO Artist (Id, Name)
VALUES ('{id_num}', '{artists}')
ON CONFLICT DO NOTHING""");
conn.commit() # 提交事务
conn.close() # 关闭连接这样，id_num 就会在每次循环迭代时递增，确保每个艺术家都有唯一的 Id。
避免 SQL 注入：使用参数化查询
虽然上述代码解决了计数器问题，但它仍然存在一个安全隐患：使用了字符串插值（f-strings）来构建 SQL 查询。这种方法容易受到 SQL 注入攻击。即使在本例中数据来源可信，养成使用参数化查询的习惯仍然很重要。
以下是使用参数化查询的示例：
import psycopg2
# 假设已建立数据库连接 conn
conn = psycopg2.connect(database="your_database", user="your_user", password="your_password", host="your_host", port="your_port")
artist_name = ['Madonna', 'Slayer', 'Disturbed', 'Michael Jackson', 'Katty Parry']
with conn.cursor() as cur:
id_num = 0
for artists in artist_name:
id_num += 1
cur.execute(
"""
INSERT INTO Artist (Id, Name)
VALUES (%s, %s)
ON CONFLICT DO NOTHING
""",
(id_num, artists)
)
conn.commit() # 提交事务
conn.close() # 关闭连接在这个版本中，我们使用 %s 作为占位符，并将实际的值作为元组传递给 cur.execute() 方法。psycopg2 库会自动处理值的转义和引用，从而避免 SQL 注入风险。
总结
- 确保循环计数器在循环外部初始化，避免重复重置。
- 始终使用参数化查询来构建 SQL 查询，以防止 SQL 注入攻击。
- 在完成数据库操作后，务必提交事务并关闭连接。
通过遵循这些最佳实践，你可以安全有效地使用循环在 PostgreSQL 中批量插入数据。
