Python使用psycopg2执行psql上传CSV至PostgreSQL报错解决

psycopg2的cursor.execute()方法仅会向PostgreSQL服务端发送标准SQL语法，你写的psql是部署在server1上的客户端命令行工具，不属于SQL语法范畴，数据库服务端无法识别该指令，因此抛出语法错误。
另外你原有代码还有两个隐藏问题：

• 文件路径使用了Windows风格的反斜杠\，在Linux系统下需要使用正斜杠/
• 没有执行conn.commit()提交事务，就算SQL逻辑正确，表操作也不会实际生效

这是最稳定的方案，不需要依赖server1上安装的psql客户端，copy_expert方法实现的是客户端侧导入，行为和你在终端执行的\COPY完全一致，不需要处理shell转义问题。
示例代码：

import psycopg2

# 建立数据库连接
conn = psycopg2.connect(
    host="server2.zzz.com", # 和你终端执行psql时用的host保持一致，若server2能正常解析可简写
    port=5432,
    database="db_name",
    user="hari1",
    password="xyz"
)
cursor = conn.cursor()

# 清空目标表
cursor.execute("TRUNCATE TABLE test_upload")

# 打开本地CSV文件执行导入
with open('/home/data/file_name.csv', 'r', encoding='utf-8') as csv_file:
    cursor.copy_expert(
        sql="COPY test_upload FROM STDIN WITH (FORMAT CSV, HEADER TRUE)",
        file=csv_file
    )

# 提交事务 这步不能漏
conn.commit()

# 关闭连接
cursor.close()
conn.close()

如果你确实要通过psql命令完成上传，不能把shell命令传给cursor.execute，需要用Python的subprocess模块在server1本地执行shell指令。
示例代码：

import os
import subprocess
import psycopg2

# 先执行清空表操作
conn = psycopg2.connect(
    host="server2.zzz.com",
    port=5432,
    database="db_name",
    user="hari1",
    password="xyz"
)
cursor = conn.cursor()
cursor.execute("TRUNCATE TABLE test_upload")
conn.commit()
cursor.close()
conn.close()

# 构造psql命令
psql_cmd = [
    "psql",
    "-h", "server2.zzz.com",
    "-p", "5432",
    "-d", "db_name",
    "-U", "hari1",
    "-c", "\\COPY test_upload FROM '/home/data/file_name.csv' CSV HEADER"
]

# 通过环境变量传递密码，避免明文出现在进程列表
run_env = os.environ.copy()
run_env["PGPASSWORD"] = "xyz"

# 执行命令
exec_result = subprocess.run(psql_cmd, env=run_env, capture_output=True, text=True)
if exec_result.returncode != 0:
    print(f"导入失败，错误：{exec_result.stderr}")
else:
    print("导入完成")

• 优先选择第一种原生方案，性能更好、依赖更少、稳定性更高
• 连接数据库的host参数要和你终端能正常连通的地址保持一致，避免出现连接失败问题
• 所有涉及表数据修改的操作（清空、导入）执行完后必须调用conn.commit()，否则操作会在连接关闭时自动回滚

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