python-psycopg2批量插入数据时列值含单引号导致SQL构建错误如何解决？

问题根源 #

你当前的报错和潜在风险都来自手动拼接SQL字符串的操作：

1. PostgreSQL中双引号仅用于标识表名、列名等标识符，单引号才是字符串的包裹符，你手动转义后用双引号包裹带单引号的字符串，会被数据库误认为是查找对应名称的列，因此报「列不存在」错误。
2. 手动拼接不仅要处理各种特殊字符转义问题，还存在严重的SQL注入风险。
3. 手动替换None为null的操作也是不必要的，psycopg2原生支持Python类型到PostgreSQL类型的映射。

最优解决方案 #

使用psycopg2官方提供的execute_values批量参数化接口，完全不需要手动处理转义、类型转换，性能也比手动拼接更高。

修改后代码 #

import psycopg2
# 导入批量插入工具
from psycopg2.extras import execute_values

# 连接数据库部分保持不变
connection = psycopg2.connect(
    host = hostname, 
    user = username, 
    password = password, 
    dbname = db_name, 
    port = port,
    connect_timeout=50
)  
cursor = connection.cursor()

# 仅构造SQL模板，用%s做占位符
transaction_SQL = '''
INSERT INTO app_banking (
    "id", "transactionBankDetail_id", "date", "text", "amount", "balance", 
    "typeTransaction", "tags", "oldCategory", "modified_by_id", "exclude_loan"
) VALUES %s 
ON CONFLICT ("id") DO UPDATE SET 
    ("tags", "oldCategory", "modified_by_id", "exclude_loan") = 
    (EXCLUDED."tags", EXCLUDED."oldCategory", EXCLUDED."modified_by_id", EXCLUDED."exclude_loan");
'''

# 直接构造参数列表，不需要做任何转义、替换操作
params = []
for x in recordLST:
    params.append((
        x["id"], x["transactionBankDetail_id"], x["date"], x["text"], 
        x["amount"], x["balance"], x["typeTransaction"], x["tags"], 
        x["oldCategory"], x["modified_by_id"], x["exclude_loan"]
    ))

try:
    # 传入游标、SQL模板、参数列表即可
    execute_values(cursor, transaction_SQL, params)
    # 不要忘记提交事务
    connection.commit()
except Exception as error:
    # 出错回滚事务
    connection.rollback()
    return str(error)

方案优势 #

• 自动处理所有特殊字符转义，不需要修改原始字符串内容，完美保留text、tags等字段的原始值
• 完全避免SQL注入风险，符合数据库操作安全规范
• 内置批量优化，插入性能远高于手动拼接SQL后单次执行的方式
• 自动完成Python类型到PostgreSQL类型的映射，比如Python的None会自动转为PostgreSQL的null，不需要手动替换

内容的提问来源于stack exchange，提问作者Manza