Psycopg2 copy_from csv ignore commas in row-CodePudding

I am trying to copy a csv file into a postgres table (phsyical) using the psycopg2's copy_from method.

columns = (
    'upc',
    'date',
    'type',
    'country_code',
    'customer'
    )

def insert_csv(f, columns):
    connection = get_postgres_connection()
    cursor = connection.cursor()
    cursor.copy_from(f, 'physical', sep=',', columns=columns)
    connection.commmit()

I have an auto generated id for each row that I'm not including in the columns.

However, some of the rows have commas within the cells. When troubleshooting I can see the rows that have errors:

1111111,2021-02-28 00:00:00,,US,"Name, The"

I found this stackoverflow question and tried:

cursor.copy_expert("COPY physical FROM STDIN WITH (FORMAT CSV)", f)

But this causes an error I was running into earlier when not specifying the columns I wanted.

value "1111111" is out of range for type integer
CONTEXT:  COPY physical, line 1, column id: "1111111"

Does anyone know of a solution to this?

CodePudding user response：

Specify the columns names in the copy command, e.g:

column_names = ','.join(columns)
copy_cmd = f"copy physical({column_names}) from stdout (format csv)"
cursor.copy_expert(copy_cmd, f)