替换单引号后生成SQL元组时单引号变双引号的PostgreSQL问题

我有如下结构的pandas DataFrame：

orig_id modified_id original_term                   modified_term
1929    3668340 3578283     Advocate for clients'' needs.   Advocate for clients'' needs

为了让字符串适配PostgreSQL的单引号转义规则，我执行了以下代码：

matches['original_term'] = matches['original_term'].map(lambda x: x.replace("'", "''"))
matches['modified_term'] = matches['modified_term'].map(lambda x: x.replace("'", "''"))

接着我把列转为元组字符串，用来拼接UPDATE语句：

orig_mod_pairs = str(tuple(zip(matches['original_term'], matches['modified_term'])))[1:-1]

但转义后的字符串被Python自动用双引号包裹，生成的结果不符合PostgreSQL要求：

('Advocacy and self-empowerment.', 'Advocacy and self-empowerment'), ('Advocacy and support services.', 'Advocacy and support services'), ('Advocacy for accessible and equitable mental health services.', 'Advocacy for accessible and equitable mental health services'), ('Advocacy groups.', 'Advocacy groups'), ("Advocate for clients'' needs.", "Advocate for clients'' needs")

PostgreSQL要求所有字符串用单引号包裹，保留转义的''，正确格式应该是：

('Advocate for clients'' needs.', 'Advocate for clients'' needs')

否则执行以下UPDATE语句时会报错：

UPDATE associations_1
SET level_1_term = pairs.modified_term
FROM (VALUES {orig_mod_pairs}) as pairs (original_term, modified_term)
WHERE level_1_term = pairs.original_term;

解决方法 #

方法1：手动构造元组字符串 #

放弃依赖str(tuple(...))，手动遍历每一对值，用单引号包裹后拼接：

orig_mod_pairs = ', '.join(
    f"('{orig}', '{mod}')" 
    for orig, mod in zip(matches['original_term'], matches['modified_term'])
)

这种方式直接控制引号格式，确保所有字符串用单引号包裹，转义后的''也能完整保留。

方法2：用pandas to_sql创建临时表（更安全） #

直接拼接SQL存在注入风险，推荐通过临时表实现更新：

1. 将配对数据写入临时表：

matches[['original_term', 'modified_term']].to_sql(
    'temp_pairs', 
    con=你的数据库连接对象, 
    if_exists='replace', 
    index=False
)

2. 执行关联临时表的UPDATE语句：

UPDATE associations_1
SET level_1_term = temp_pairs.modified_term
FROM temp_pairs
WHERE associations_1.level_1_term = temp_pairs.original_term;

这种方法无需手动处理引号，还能避免SQL注入问题。

方法3：使用参数化查询（最安全） #

如果一定要用VALUES子句，用psycopg2的参数化查询自动处理转义：

from psycopg2 import sql

# 构造VALUES子句的占位符
values_clause = sql.SQL('VALUES {}').format(
    sql.SQL(', ').join(
        sql.Placeholder() * 2 for _ in range(len(matches))
    )
)

# 构造完整UPDATE语句
update_query = sql.SQL("""
    UPDATE associations_1
    SET level_1_term = pairs.modified_term
    FROM ({}) as pairs (original_term, modified_term)
    WHERE level_1_term = pairs.original_term;
""").format(values_clause)

# 执行查询
with 你的数据库连接对象.cursor() as cur:
    cur.execute(update_query, matches[['original_term', 'modified_term']].values.flatten().tolist())
    你的数据库连接对象.commit()

psycopg2会自动处理引号转义，彻底避免格式错误和注入风险。

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