使用Python的Psycopg3库将CSV文件内容导入PostgreSQL数据库问题咨询

核心问题原因 #

1. 你使用的是PostgreSQL服务端COPY语法，COPY ... FROM '文件路径'要求路径是PostgreSQL服务所在机器的本地路径，且运行PostgreSQL服务的操作系统用户（通常Linux下为postgres，Windows下为NT SERVICE\postgres）必须拥有该文件的读取权限，若数据库和Python脚本不在同一台机器，该方式默认无法使用。
2. 你编写的COPY语句存在语法错误：文件路径没有用单引号包裹，属于语法不规范，正常会触发数据库报错。
3. psycopg3的cursor.copy()方法设计初衷是配合COPY ... FROM STDIN语法实现客户端数据流导入，直接传入带本地路径的COPY语句不符合该方法的正确使用规范。

排查思路 #

• 查看PostgreSQL服务端日志，可直接定位COPY命令的执行错误、导入行数等关键信息。
• 执行COPY命令后追加查询SELECT count(*) FROM public.ideastorage，确认表内实际数据行数。
• 核对CSV文件的字段顺序、数据类型是否与目标表结构完全匹配，检查CSV是否存在表头行需要跳过。

推荐解决方案（支持直接传入本地文件对象） #

直接使用客户端COPY模式，不需要服务端拥有文件访问权限，跨机器也可正常使用，是最推荐的导入方式，示例代码如下：

import psycopg

with psycopg.connect('dbname=ideatest user=postgres password=password') as conn: 
    with conn.cursor() as cur:
        # 打开本地CSV文件，可根据实际编码修改encoding参数
        with open(r'C:\dir\filename.csv', 'r', encoding='utf-8') as mock_idea_info:
            # COPY语句使用FROM STDIN，WITH参数根据CSV实际规则调整
            # HEADER true表示CSV第一行是表头需要跳过，不需要则改为false
            # DELIMITER为CSV分隔符，默认逗号，制表符可改为'\t'
            with cur.copy("""
                COPY public.ideastorage FROM STDIN 
                WITH (FORMAT csv, DELIMITER ',', HEADER true)
            """) as copy_context:
                # 小文件可直接一次性写入，大文件建议逐行迭代避免内存溢出
                copy_context.write(mock_idea_info.read())
                # 大文件逐行写入写法：
                # for line in mock_idea_info:
                #     copy_context.write(line)
            # 可通过rowcount查看实际导入行数
            print(f"实际导入行数：{copy_context.rowcount}")

print('Copy successful.')

该方式可以直接使用你已经打开的mock_idea_info文件对象，完全符合你的需求。

可选：服务端COPY的正确写法 #

如果你确认数据库和脚本在同一台机器，且服务用户有文件读取权限，可使用修正后的服务端COPY语法：

cur.execute("""
    COPY public.ideastorage FROM 'C:\\dir\\filename.csv'
    WITH (FORMAT csv, DELIMITER ',', HEADER true)
""")
# 注意路径必须用单引号包裹，反斜杠需要双转义，或者使用r字符串包裹路径

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