Psycopg2 "copy_from" command, possible to ignore delimiter in quote (getting error)?

Question

I am trying to load rows of data into postgres in a csv-like structure using the copy_from command (function to utilize copy command in postgres). My data is delimited with commas(and unfortunately since I am not the data owner I cannot just change the delimiter). I run into a problem when I try to load a row that has a value in quotes containing a comma (ie. that comma should not be treated as a delimiter).

For example this row of data is fine:

",Madrid,SN,,SEN,,,SN,173,157"

This row of data is not fine:

","Dominican, Republic of",MC,,YUO,,,MC,65,162",

Some code:

    conn = get_psycopg_conn()
    cur = conn.cursor()

    _io_buffer.seek(0) #This buffer is holding the csv-like data
    cur.copy_from(_io_buffer, str(table_name), sep=',', null='', columns=column_names)
    conn.commit()

Answer 1

It looks like copy_from doesn't expose the csv mode or quote options, which are available form the underlying PostgreSQL COPY command. So you'll need to either patch psycopg2 to add them, or use copy_expert.

I haven't tried it, but something like

curs.copy_expert("""COPY mytable FROM STDIN WITH (FORMAT CSV)""", _io_buffer)

might be sufficient.