使用Python中的Psycopg3将CSV复制到PostgreSQL数据库。

Question

我有些难以理解Python中psycopg3库的适当语法。我正在尝试将.csv文件的内容复制到我的数据库中。PostgreSQL文档指出应该按照以下方式编写copy：

COPY table_name [ ( column_name [, ...] ) ]
    FROM { 'filename' | PROGRAM 'command' | STDIN }
    [ [ WITH ] ( option [, ...] ) ]
    [ WHERE condition ]

所以我将我的Python语句写成了以下形式:

import psycopg


with psycopg.connect('dbname=ideatest user=postgres password=password') as conn: 
        with conn.cursor() as cur:
            mock_idea_info = open(r'C:\dir\filename.csv')
            cur.copy('public.ideastorage FROM C:\dir\filename.csv;')

print('Copy successful.')

问题在于该脚本会打印'复制成功'，但不会将数据插入到数据库中。没有生成错误消息。我已经复制了文件路径中的\字符，所以那不是问题。我一直在寻找解决方案和可能的故障排除方法，但尚未找到任何我能理解且相关的东西。

此外，是否有任何方法可以将mock_idea_info直接传递到复制语句中？

非常感谢任何帮助。

- Caritas

---

这个回答解决了你的问题吗？将Pandas数据框插入Postgres - itprorh66

嗯，我相信那个解决方案是可行的。我试图避免使用另一个库来管理SQL语句。在那个解决方案中，好像使用了SQLAlchemy。我特别寻找一个psycopg的解决方案。我可以使用一些其他功能更简单的库，但感觉那样编码有点粗糙。 - Caritas

请查看此处复制，其中包含“逐块复制”的示例。 - Adrian Klaver

我在写问题时打开了那个确切的页面，但我并不完全理解它。我的 Python 技能不是很强。使用 open('data', 'r') 语句似乎是基于文件名打开文件。我明白这一点。而 while 循环似乎会将值迭代地写入数据库，直到文档读取完成为止。但是这行代码：with cursor.copy("COPY data FROM STDIN") as copy 让我感到困惑。看起来整个文件被转换为一个对象，然后存储并（以某种方式）分解为列式存储在数据库中。这正确吗？ - Caritas

看看我的答案，里面有一些例子。我还在摸索 psycopg3，所以答案可能需要修改，但我认为它可以作为一个起点。 - Adrian Klaver

Answer 1

请参考Copy from：

cat data.out 
1       2
2       1

\d csv_test 
              Table "public.csv_test"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 col1   | integer |           |          | 
 col2   | integer |           |          | 


with open("data.out", "r") as f:
     with cur.copy("COPY csv_test FROM STDIN") as copy:
         while data := f.read(100):
            copy.write(data)
con.commit()

select * from csv_test ;
 col1 | col2 
------+------
    1 |    2
    2 |    1

--Add format options
cat data.out 
1,2
2,1
with open("data.out", "r") as f:
     with cur.copy("COPY csv_test FROM STDIN WITH (FORMAT CSV)" ) as copy:
         while data := f.read(100):
            copy.write(data)
con.commit()

select * from csv_test ;
 col1 | col2 
------+------
    1 |    2
    2 |    1
    1 |    2
    2 |    1

以上内容改编自链接中的示例。这个while data := f.read(100)使用了仅在Python 3.8+可用的海象运算符(:=)。

- Adrian Klaver

---

完美的回答。Psycopg3文档没有明确说明是否能够添加WITH部分，这也是我遇到麻烦的原因。谢谢Adrian。那个100的单位是什么？字节，字符？ - Ahmad

有关 read(size) 的详细信息，请参见7.2. 读写文件：“...调用 f.read(size)，它会读取一定量的数据…” [...] “否则，在文本模式下最多读取 size 个字符，在二进制模式下最多读取 size 个字节…” - Adrian Klaver

Answer 2

我没有看到你在输入后提交数据以将其保存到数据库中。请尝试添加以下代码：

conn.commit()

- Thomas Kutsch

---

如果我没记错的话，当使用with关键字作为上下文时，conn.commit()会在关闭时自动执行。但是我还是尝试了一下，结果并没有影响输出。 - Caritas

你能解释一下这个做什么吗？ - Ahmed Sbai

Answer 3

你可能应该包含 with (format csv) 子句（参见 https://www.postgresql.org/docs/current/sql-copy.html），或者明确指定引号和分隔符字符。

- rd_nielsen

---

psycopg3文档明确指出要保持这些子句不变。我想起来了。我认为psycopg3有一种独特的做事方式。 - Caritas

当像复制文件一样逐块复制时，psycopg3文档指出：“在这种情况下，只要输入数据兼容，您可以使用任何COPY选项和格式。” - rd_nielsen

当逐行编写/读取时，您不能使用附加子句。正如在按块编写/读取时所指出的那样，您可以使用它们。 - Adrian Klaver