I am trying to import the following table to my Postgres server using cursor.copy_from() in psycopg2 because the file is too large.

Here is my code:

import psycopg2
import os

conn = psycopg2.connect(
    dbname = name,
    user = username,
    password = pwd,
    host = hst,
    port = 5432
)

cur = conn.cursor()

path = os.getcwd()   '\users.csv'

file = open(path, 'r')

cur.copy_from(file, table_name, sep=',')
conn.commit()

conn.close()

This inserts the data to the table but there is double quotes in the third column like below.

Later I found out that the problem lies in the open() itself. Because if I print the first line by doing file.readline(), I get:

1,[email protected],"John Stokes"

I don't want these double quotes in my table. I tried using cursor.execute() with COPY FROM query but it says that I am not a superuser even if I am.

CodePudding user response：

Use copy_expert. Then you are not working as the server user but as the client user. Also you can use WITH CSV which will take care of the quoting. copy_from and copy_to work using the text format as described here COPY.

cat test.csv                                                                                                                                                            
1,[email protected],"John Stokes"
2,[email protected],"Emily Ray"

create table test_csv (id integer, mail varchar, name varchar);

import psycopg2

con = psycopg2.connect(dbname="test", host='localhost', user='postgres', port=5432)
cur = con.cursor()


with open('test.csv') as f:
    cur.copy_expert('COPY test_csv FROM stdin WITH CSV', f)

con.commit()

select * from test_csv ;
 id |        mail        |    name     
---- -------------------- -------------
  1 | [email protected]  | John Stokes
  2 | [email protected] | Emily Ray

FYI, in psycopg3(psycopg) this behavior has changed substantially. See here psycopg3 COPY for how to handle in that case.