Error when importing CSV to postgres with python a

问题:

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.

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)

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?

EDITED

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

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

I get an error:

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

How do I give these methods?

回答1:

Try using 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()

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.

回答2:

You can also use copy_from. See the code below

with open('/tmp/tmpJopiUG/downloaded_xls.csv') as f:
 cur.copy_from(f, table_name,sep=',')
conn.commit()