copy data from csv to postgresql using python

Question

I am on windows 7 64 bit. I have a csv file 'data.csv'. I want to import data to a postgresql table 'temp_unicommerce_status' via a python script.

My Script is:

import psycopg2
conn = psycopg2.connect("host='localhost' port='5432' dbname='Ekodev' user='bn_openerp' password='fa05844d'")
cur = conn.cursor()
cur.execute("""truncate table "meta".temp_unicommerce_status;""")
cur.execute("""Copy temp_unicommerce_status from 'C:\Users\n\Desktop\data.csv';""")
conn.commit()
conn.close()

I am getting this error

Traceback (most recent call last):
  File "C:\Users\n\Documents\NetBeansProjects\Unicommerce_Status_Update\src\unicommerce_status_update.py", line 5, in <module>
cur.execute("""Copy temp_unicommerce_status from     'C:\\Users\\n\\Desktop\\data.csv';""")
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.

Answer 1

Use the copy_from cursor method

f = open(r'C:\Users\n\Desktop\data.csv', 'r') cur.copy_from(f, temp_unicommerce_status, sep=',') f.close() 

The file must be passed as an object.

Since you are coping from a csv file it is necessary to specify the separator as the default is a tab character

Answer 2

The way I solved this problem particular to use psychopg2 cursor class function copy_expert (Docs: http://initd.org/psycopg/docs/cursor.html). copy_expert allows you to use STDIN therefore bypassing the need to issue a superuser privilege for the postgres user. Your access to the file then depends on the client (linux/windows/mac) user's access to the file

From Postgres COPY Docs (https://www.postgresql.org/docs/current/static/sql-copy.html):

Do not confuse COPY with the psql instruction \copy. \copy invokes COPY FROM STDIN or COPY TO STDOUT, and then fetches/stores the data in a file accessible to the psql client. Thus, file accessibility and access rights depend on the client rather than the server when \copy is used.

You can also leave the permissions set strictly for access to the development_user home folder and the App folder.

csv_file_name = '/home/user/some_file.csv'
sql = "COPY table_name FROM STDIN DELIMITER '|' CSV HEADER"
cursor.copy_expert(sql, open(csv_file_name, "r"))