使用python和psycopg2将CSV导入Postgres时出错 [英] Error when importing CSV to postgres with python and psycopg2

问题描述

我尝试使用python和psycopg2将CSV文件从文件夹复制到Postgres表，并且出现以下错误：

I try to COPY a CSV file from a folder to a postgres table using python and psycopg2 and I get the following error:

 Traceback (most recent call last):
 File "<stdin>", line 1, in <module>
 psycopg2.ProgrammingError: must be superuser to COPY to or from a file
HINT:  Anyone can COPY to stdout or from stdin. psql's \copy command also works for anyone.

我也尝试通过python环境运行它，如下所示：

I also tried to run it through the python environment as:

  constr = "dbname='db_name' user='user' host='localhost' password='pass'"
  conn = psycopg2.connect(constr)
  cur = conn.cursor()
  sqlstr = "COPY test_2 FROM '/tmp/tmpJopiUG/downloaded_xls.csv' DELIMITER ',' CSV;"
  cur.execute(sqlstr)

我仍然收到上述错误。我尝试了\copy命令，但这仅在psql中有效。为了能够通过我的python脚本执行此操作，有什么替代方案？

I still get the above error. I tried \copy command but this works only in psql. What is the alternative in order to be able to execute this through my python script?

已编辑

在查看@IljaEverilä提供的链接后，我尝试了以下操作：

After having a look in the link provided by @Ilja Everilä I tried this:

cur.copy_from('/tmp/tmpJopiUG/downloaded_xls.csv', 'test_copy')

我得到一个错误：

Traceback (most recent call last):
File "<stdin>", line 1, in <module>
TypeError: argument 1 must have both .read() and .readline() methods

如何提供这些方法？

推荐答案

尝试使用 cursor.copy_expert（） ：

constr = "dbname='db_name' user='user' host='localhost' password='pass'"
conn = psycopg2.connect(constr)
cur = conn.cursor()
sqlstr = "COPY test_2 FROM STDIN DELIMITER ',' CSV"
with open('/tmp/tmpJopiUG/downloaded_xls.csv') as f:
    cur.copy_expert(sqlstr, f)
conn.commit()

您有在python中打开文件 并将其传递给psycopg，然后将其转发到postgres的stdin。由于您在 COPY 中使用 CSV 自变量，因此必须使用通过COPY的专家版本声明自己。

You have to open the file in python and pass it to psycopg, which then forwards it to postgres' stdin. Since you're using the CSV argument to COPY, you have to use the expert version in which you pass the COPY statement yourself.

这篇关于使用python和psycopg2将CSV导入Postgres时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！