Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Error when importing CSV to postgres with python and psycopg2

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?

like image 800
user1919 Avatar asked Mar 13 '23 01:03

user1919


1 Answers

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.

like image 100
Ilja Everilä Avatar answered Mar 23 '23 11:03

Ilja Everilä