Python生成PostgreSQL INSERT语句存文件时的字符串解析问题

问题根源 #

手动拼接SQL字符串时，不仅容易出现文本字段引号格式错误（如用双引号代替单引号），还会面临SQL注入风险，同时手动处理JSON等复杂类型容易出错。

最优解决方案 #

使用PostgreSQL官方推荐的Python驱动psycopg2的类型适配功能，它能自动处理不同数据类型的格式转换，包括文本转义、JSON序列化等，完全符合PostgreSQL的语法要求。

方案1：直接执行参数化查询（推荐，安全高效） #

如果你的需求是直接向数据库执行插入操作，参数化查询是最安全的方式，驱动会自动处理所有类型转换：

import psycopg2

# 连接数据库（替换为你的数据库信息）
conn = psycopg2.connect("dbname=your_db user=your_user password=your_pwd host=your_host")
cur = conn.cursor()

data = {
    'id': 45,
    'col1': "foo's",
    'col2': {'dict_key': 5}
}

columns = ', '.join(data.keys())
# 生成对应数量的占位符
placeholders = ', '.join(['%s'] * len(data))

# 执行参数化查询，驱动自动处理类型转换
cur.execute(f"INSERT INTO table1({columns}) VALUES ({placeholders});", tuple(data.values()))

# 提交事务并关闭连接
conn.commit()
cur.close()
conn.close()

方案2：生成符合规范的SQL文件 #

如果需要生成可执行的SQL文件，使用psycopg2的adapt工具来转换每个值，确保格式正确：

import psycopg2
from psycopg2.extensions import adapt, register_adapter, AsIs
import json

# 注册字典适配器，自动将字典转为PostgreSQL JSON类型
def adapt_dict(d):
    return AsIs(f"'{json.dumps(d)}'::json")

register_adapter(dict, adapt_dict)

data = {
    'id': 45,
    'col1': "foo's",
    'col2': {'dict_key': 5}
}

columns = ', '.join(data.keys())
# 用adapt处理每个值，自动转义引号、适配类型
values = ', '.join([adapt(v).getquoted().decode('utf-8') for v in data.values()])

# 生成最终SQL命令
command = f"INSERT INTO table1({columns}) VALUES ({values});"

# 写入文件
with open("file.sql", "w") as f:
    f.write(command)

生成的SQL结果示例：

INSERT INTO table1(id, col1, col2) VALUES (45, 'foo''s', '{"dict_key": 5}'::json);

关键优势 #

• 自动处理文本字段的单引号转义（如foo's转为foo''s），完全符合PostgreSQL语法要求
• 自动适配JSON等复杂类型，无需手动拼接格式
• 彻底避免SQL注入风险
• 代码更简洁易维护

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