如何用Python将大型列表导入SQL单行列？解决psycopg2类型不匹配错误

问题核心：你传入的Python列表被psycopg2自动解析为PostgreSQL的text[]数组类型，但目标列word_exclusion是varchar（单值字符串类型），导致类型不匹配。::varchar转换无效是因为数组转字符串需要专用处理逻辑，以下是两种可行解决方案：

方案1：Python端将列表转为逗号分隔字符串 #

直接在Python代码中把列表拼接成符合varchar类型要求的字符串：

eng_stopwords =['further', 'then', 'once', 'here', 'there', 'when', 'where', 'why', 'how', 'all', 'any', 'both', 'each', 'few', 'more', 'most', 'other', 'some', 'such', 'no', 'nor', 'not', 'only', 'own', 'same', 'so', 'than', 'too', 'very', 's', 't', 'can', 'will', 'just', 'don', "don't", 'should', "should've", 'now', 'd', 'll', 'm', 'o', 're', 've', 'y', 'ain', 'aren', "aren't", 'couldn', "couldn't", 'didn', "didn't", 'doesn', "doesn't", 'hadn', "hadn't", 'hasn', "hasn't", 'haven', "haven't", 'isn', "isn't", 'ma', 'mightn', "mightn't", 'mustn', "mustn't", 'needn', "needn't", 'shan', "shan't", 'shouldn', "shouldn't", 'wasn', "wasn't", 'weren', "weren't", 'won', "won't", 'wouldn', "wouldn't"]
cur = conn.cursor ()
today = date.today()
# 将列表转为逗号分隔的字符串
stopwords_str = ','.join(eng_stopwords)
sql = "INSERT INTO oey_stg.shc_spit_wod_exsions (word_exclusion, load_date) VALUES (%s,%s)"
val =  (stopwords_str, today)
cur.execute(sql, val)
conn.commit()  # 必须提交事务才能写入数据

方案2：SQL端用函数转换数组为字符串 #

如果不想修改Python逻辑，可在SQL语句中使用array_to_string函数将传入的数组转为字符串：

eng_stopwords =['further', 'then', 'once', 'here', 'there', 'when', 'where', 'why', 'how', 'all', 'any', 'both', 'each', 'few', 'more', 'most', 'other', 'some', 'such', 'no', 'nor', 'not', 'only', 'own', 'same', 'so', 'than', 'too', 'very', 's', 't', 'can', 'will', 'just', 'don', "don't", 'should', "should've", 'now', 'd', 'll', 'm', 'o', 're', 've', 'y', 'ain', 'aren', "aren't", 'couldn', "couldn't", 'didn', "didn't", 'doesn', "doesn't", 'hadn', "hadn't", 'hasn', "hasn't", 'haven', "haven't", 'isn', "isn't", 'ma', 'mightn', "mightn't", 'mustn', "mustn't", 'needn', "needn't", 'shan', "shan't", 'shouldn', "shouldn't", 'wasn', "wasn't", 'weren', "weren't", 'won', "won't", 'wouldn', "wouldn't"]
cur = conn.cursor ()
today = date.today()
# 使用array_to_string将数组转为逗号分隔字符串
sql = "INSERT INTO oey_stg.shc_spit_wod_exsions (word_exclusion, load_date) VALUES (array_to_string(%s, ','), %s)"
val =  (eng_stopwords, today)
cur.execute(sql, val)
conn.commit()

备选：修改列类型为数组（如果实际需求是存储数组） #

如果你的业务逻辑需要保留数组结构而非字符串，可修改表列类型为数组类型：

ALTER TABLE oey_stg.shc_spit_wod_exsions ALTER COLUMN word_exclusion TYPE varchar[];

修改后你的原Python代码无需改动，可直接插入列表数据。

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