在python中使用psycopg3实现安全的sql批量插入,核心做法是调用 execute_batch() 方法并配合参数化查询。该方法会自动将参数安全地转义并分批执行,有效防止sql注入且提升性能。使用时需确保连接已开启事务,并通过 page_size 参数控制每批提交的记录数。

如何配置批量插入的参数与批次大小
配置批量插入的关键在于设置 page_size 参数以控制每批处理的行数,同时使用参数化元组列表作为数据源。通过调整 page_size 可以平衡内存占用与网络往返次数,默认值为100。数据源必须是包含元组的序列,每个元组对应一行数据,这样 execute_batch 才能正确解析并绑定参数。

import psycopg
from psycopg import sql
# 假设 conn 是一个已建立的 psycopg.Connection 对象
def batch_insert_with_config(conn, data, page_size=50):
query = "INSERT INTO users (name, email) VALUES (%s, %s)"
# execute_batch 会自动处理分批和参数转义
conn.execute_batch(query, data, page_size=page_size)
# 示例数据:列表中包含元组
sample_data = [
("Alice", "alice@example.com"),
("Bob", "bob@example.com"),
("Charlie", "charlie@example.com")
]
with psycopg.connect("dbname=test user=postgres") as conn:
with conn.cursor() as cur:
# 创建表(如果不存在)
cur.execute("CREATE TABLE IF NOT EXISTS users (id SERIAL PRIMARY KEY, name TEXT, email TEXT)")
# 执行批量插入
batch_insert_with_config(conn, sample_data, page_size=2)
conn.commit()
print(f"Inserted {len(sample_data)} rows in batches of 2")
运行上述代码后,预期结果为数据库 users 表中新增3条记录,且控制台打印出 “Inserted 3 rows in batches of 2”。由于设置了 page_size=2,实际执行时会分为两批:第一批插入前两条,第二批插入最后一条。所有参数均通过 %s 占位符安全绑定,无SQL注入风险。
如何处理批量插入中的异常与回滚
处理批量插入异常的关键是将 execute_batch 包裹在 try-except 块中,并在捕获异常后调用 conn.rollback() 以确保数据一致性。psycopg3 的 execute_batch 默认采用“全有或全无”策略:若某一批次失败,整个批次未提交的数据不会写入数据库,但已提交的前面批次可能已生效。因此,建议在事务中显式管理回滚逻辑,或在应用层捕获异常后重新组织数据重试。
Camoufox 是一个基于 Firefox 构建的开源反检测浏览器,为 Python 自动化工作流程提供强大的指纹注入和反机器人检测规避功能。它与 Playwright 集成,以提供能够绕过现代机器人检测系统的隐身浏览能力。

import psycopg
from psycopg import OperationalError
def safe_batch_insert(conn, data, page_size=100):
query = "INSERT INTO orders (order_id, amount) VALUES (%s, %s)"
try:
conn.execute_batch(query, data, page_size=page_size)
conn.commit()
return True
except Exception as e:
conn.rollback()
print(f"Batch insert failed: {e}")
return False
# 示例数据,其中包含一个可能导致类型错误的值
sample_orders = [
(1001, 99.99),
(1002, "invalid_amount"), # 这将导致错误
(1003, 45.50)
]
with psycopg.connect("dbname=test user=postgres") as conn:
with conn.cursor() as cur:
cur.execute("CREATE TABLE IF NOT EXISTS orders (id SERIAL PRIMARY KEY, order_id INT, amount NUMERIC)")
success = safe_batch_insert(conn, sample_orders, page_size=2)
if not success:
print("Data was rolled back due to error.")
else:
print("All data inserted successfully.")
运行上述代码时,由于第二行数据 (1002, "invalid_amount") 的类型不匹配,execute_batch 会在处理该批次时抛出异常。程序捕获异常后调用 rollback(),确保任何部分数据都不会被持久化。控制台将打印 “Batch insert failed: ...” 和 “Data was rolled back due to error.”。这验证了异常处理机制能有效防止部分写入导致的数据不一致。
当输入数据包含空值时批量插入会返回什么结果
当输入数据包含空值(None)时,execute_batch 会将这些空值安全地转换为 SQL 中的 NULL 并插入数据库,不会引发错误或跳过该行。psycopg3 自动处理 Python None 到 SQL NULL 的映射,只要目标列允许 NULL 值,插入即可成功。若目标列定义为 NOT NULL,则会抛出约束违反异常,触发前述的回滚逻辑。
import psycopg
def batch_insert_with_nulls(conn, data):
query = "INSERT INTO products (name, description) VALUES (%s, %s)"
conn.execute_batch(query, data)
conn.commit()
# 示例数据,包含空字符串和 None
sample_products = [
("Widget", "A useful widget"),
("Gadget", None), # description 为 NULL
("Doohickey", "") # description 为空字符串
]
with psycopg.connect("dbname=test user=postgres") as conn:
with conn.cursor() as cur:
cur.execute("CREATE TABLE IF NOT EXISTS products (id SERIAL PRIMARY KEY, name TEXT, description TEXT)")
batch_insert_with_nulls(conn, sample_products)
# 验证插入结果
cur.execute("SELECT name, description FROM products")
rows = cur.fetchall()
for row in rows:
print(row)
运行上述代码后,预期输出为三行记录:第一行 ('Widget', 'A useful widget'),第二行 ('Gadget', None),第三行 ('Doohickey', '')。这表明空值(None)被正确转换为 SQL NULL,而空字符串仍保留为空字符串,两者在数据库中行为不同但均能成功插入。此行为符合 PostgreSQL 标准,无需额外处理。
Python免费学习笔记(深入):立即使用
在学习笔记中,你将探索 Python 的核心概念和高级技巧!










