current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Join the Stack Overflow Community
Stack Overflow is a community of 6.3 million programmers, just like you, helping each other.
Join them; it only takes a minute:
Sign up
up vote 0 down vote favorite

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?

share|improve this question
1  
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ä Jun 6 at 12:25
    
Thanks. Please see edit. – user1919 Jun 6 at 12:28
up vote 2 down vote accepted

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)

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.

share|improve this answer
    
Hmmm. I get a "Segmentation fault" error. – user1919 Jun 6 at 12:39
1  
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. – Ilja Everilä Jun 6 at 12:42
    
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. – user1919 Jun 6 at 12:47
1  
Remember to commit(). – Ilja Everilä Jun 6 at 12:47
    
Oh man! Thanks so much! Commit DID the trick! I always forget I need to commit the changes. – user1919 Jun 6 at 12:49

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.