Error when importing CSV to postgres with python and psycopg2

Question

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?

Have a look at initd.org/psycopg/docs/cursor.html#cursor.copy_from or initd.org/psycopg/docs/cursor.html#cursor.copy_expert, which seem to employ stdin and stdout, which would allow ordinary DB user to run it. — Ilja Everilä
– Ilja Everilä, Commented Jun 6, 2016 at 12:25

Backlin · Accepted Answer · 2017-03-07 13:47:25Z

6

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.

edited Mar 7, 2017 at 13:47

Backlin

15k5 gold badges53 silver badges81 bronze badges

answered Jun 6, 2016 at 12:30

Ilja Everilä

53.4k9 gold badges137 silver badges141 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Ilja Everilä Over a year ago

Oh. That sure wasn't meant to happen. I hope you noticed my edit in between where I fixed the call to execute() to copy_expert() (had a little copy&paste error there). But still, it really should not segfault.

user1919 Over a year ago

Yup. After your edit I don't get the segmentation fault error. But nothing actually happens. No error or warning but still the csv is not uploaded.

Ilja Everilä Over a year ago

Remember to commit().

user1919 Over a year ago

Oh man! Thanks so much! Commit DID the trick! I always forget I need to commit the changes.

Ilja Everilä Over a year ago

I highly recommend using with-statements with connection objects for transaction handling, and you have to write a lot less boilerplate in the future.

|

buqing · Accepted Answer · 2018-08-06 20:25:44Z

3

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

answered Aug 6, 2018 at 20:25

buqing

1,05510 silver badges29 bronze badges

Collectives™ on Stack Overflow

Error when importing CSV to postgres with python and psycopg2

2 Answers 2

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related