psycopg2插入PostgreSQL报syntax error at or near错误如何修正

报错核心原因 #

• psycopg2的%s占位符仅用于传递数据值，会自动为字符串内容添加单引号。你使用%s传递列名，导致生成的SQL中列名被错误包裹单引号：INSERT INTO Data2_mcw('time')，不符合PostgreSQL语法要求。
• 额外存在两个逻辑错误：
  1. 建表时声明的表名为Data2，但插入语句中写的是Data2_mcw，表名不匹配
  2. 现有双层循环逐列逐行插入的逻辑，会导致每行的其他列均为空值，不符合写入需求

修正方案 #

使用psycopg2内置的sql模块处理表名、列名这类标识符的动态拼接，同时改为逐行插入的逻辑，代码如下：

前置导入 #

import psycopg2.sql as sql
import pickle
import numpy as np

修正后的写入代码 #

# 取DataFrame所有列名，需和建表的列名顺序一致
cols = Data2.columns.tolist()

# 构造安全的插入语句模板
insert_query = sql.SQL("INSERT INTO {} ({}) VALUES ({})").format(
    sql.Identifier('Data2'), # 对应你实际创建的表名
    sql.SQL(', ').join(map(sql.Identifier, cols)),
    sql.SQL(', ').join([sql.Placeholder()] * len(cols))
)

# 逐行遍历处理
for _, row in Data2.iterrows():
    processed_row = []
    for val in row:
        # numpy数组序列化为字节流匹配bytea字段类型
        if type(val).__module__ == np.__name__:
            processed_row.append(pickle.dumps(val))
        else:
            processed_row.append(val)
    cursor.execute(insert_query, tuple(processed_row))

额外注意事项 #

• 后续从PostgreSQL读取该表数据时，bytea类型的列需要用pickle.loads()反序列化才能还原为numpy数组
• 数据量较大时建议提前批量处理所有行数据，用cursor.executemany()批量插入，大幅提升写入效率
• 如果不需要保留numpy数组的形状、dtype等元信息，也可以用val.tobytes()替代pickle.dumps(val)，读取时用np.frombuffer()还原即可

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