Psycopg2 copy_from导入CSV时单元格内逗号的处理方案

尝试使用psycopg2的copy_from方法将CSV文件导入PostgreSQL的physical表，初始代码如下：

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

physical表设置了自增id字段，未将其纳入columns指定的导入字段列表中。

运行时报错，排查发现部分行的单元格内容本身包含逗号，出错行示例如下：

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

改用copy_expert方法处理CSV格式的逗号问题：

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

此时出现新报错，错误信息如下：

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

报错原因：未指定导入字段的情况下，PostgreSQL会按照表定义的字段顺序匹配CSV列，第一列自增id被匹配到了CSV的第一列upc值，导致类型不匹配超出范围。

核心逻辑是使用copy_expert时显式指定导入的字段列表，既保留CSV格式解析能力处理带引号的逗号内容，又跳过自增id字段让其自动生成。

最终可用代码 #

def insert_csv(f, table, columns):
    connection = get_postgres_connection()
    cursor = connection.cursor()
    try:
        column_names = ','.join(columns)
        query = f'''
            COPY {table}({column_names})
            FROM STDIN (FORMAT CSV)
        '''
        cursor.copy_expert(query, f)
        connection.commit()
        return True
    except (psycopg2.Error) as e:
        print(e)
        return False
    finally:
        cursor.close()
        connection.close()

注：原代码笔误将STDIN写为STDOUT，已修正

导入字段配置 #

columns = (
        "upc",
        "date_thru",
        "transaction_type",
        "transaction_type_subtype",
        "country_code",
        "customer",
        "quantity",
        "income_gross",
        "fm_serial",
        "date_usage"
    )

调用示例 #

with open(dump_file, 'r', newline='', encoding="ISO-8859-1") as f:
        inserted = insert_csv(f, 'physical', columns)

内容的提问来源于stack exchange，提问作者Kevin G