Python psycopg2 loading csv file with quote and comma delimiter

Question

I have a data like this

id, Name, Remarks, status
1, Juan, "Hello, World", active
2, Pepito, "Howdy, how are you", inactive

i am trying to load it to postgres using python, psycopg2

import csv
import psycopg2

try:
    next(f)
    cursor.copy_from(f, table_name, sep=',', quotechar='"')
    conn.commit()
except (Exception, psycopg2.DatabaseError) as error:
#     os.remove(path)
    print("Error: %s" % error)
    conn.rollback()
    cursor.close()
    print(1)
print(f"Data successfully imported to {table_name}.")
cursor.close()

i am getting error of

Error: 'quotechar' is an invalid keyword argument for this function

my target output to table is

id|Name     |Remarks             |status
1 |Juan     |Hello, World        |active
2 |Pepito   |Howdy, how are you  |inactive

Answer 1

Use copy_expert from here Copy.

cat csv_test.csv
id,Name,Remarks,status
1,Juan,"Hello, World",active
2,Pepito,"Howdy, how are you",inactive

create table csv_test(id integer, name varchar, remarks varchar, status varchar);

import psycopg2

con = psycopg2.connect(dbname="test", host='localhost', user='postgres', port=5432)
cur = con.cursor()
with open('csv_test.csv') as csv_file:
    cur.copy_expert("copy csv_test from stdin with csv header", csv_file)
con.commit()

select * from csv_test ;
 id |  name  |      remarks       |  status  
----+--------+--------------------+----------
  1 | Juan   | Hello, World       | active
  2 | Pepito | Howdy, how are you | inactive

copy_to and copy_from use the text format as described here COPY Text Format. This format is different from the CSV format you have in your file. copy_expert allows you to use the COPY csv format as described in section CSV Format. This makes the whole process a lot easier.