copy data from csv to postgresql using python

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

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

#sample of code that worked for meimport psycopg2 #import the postgres library#connect to the databaseconn = psycopg2.connect(host='localhost',                       dbname='database1',                       user='postgres',                       password='****',                       port='****')  #create a cursor object #cursor object is used to interact with the databasecur = conn.cursor()#create table with same headers as csv filecur.execute("CREATE TABLE IF NOT EXISTS test(**** text, **** float, **** float, **** text)")#open the csv file using python standard file I/O#copy file into the table just created with open('******.csv', 'r') as f:next(f) # Skip the header row.    #f , <database name>, Comma-Seperated    cur.copy_from(f, '****', sep=',')    #Commit Changes    conn.commit()    #Close connection    conn.close()f.close()