python-3.x - 来自 psycopg2 的 ExecuteMany 函数出错

Question

我正在尝试从 excel 中提取数据，然后在 python 中使用 psycopg2 将数据插入到 postgres 数据库中。我正在使用 executemany 插入数据库并将 insert sql 语句及其值作为参数传递给它。它抛出错误'字符串索引超出范围'

import pandas as pd
import numpy as np
import psycopg2 as psycopg2
import csv


'''
sample values is: 
('123924902','ABC_DEF_GHI_NG',current_user,now() at time zone 'utc',current_user,now() at time zone 'utc')
'''

def extract_from_file(excel_file, sheet_index, row_for_header, output_file):
    df = pd.read_excel(excel_file,sheet_name=sheet_index, header=None)
    new_header = df.iloc[row_for_header]
    ##print(new_header)
    df = df[row_for_header+1:]
    df.columns=new_header
    rows_to_insert = []
    
    for index,row in df.iterrows():
        ##print(row['Sector'])
        if pd.isnull(row['ENTITY_ID']):
            continue##or break if we 
            ##are confident no intervening empty rows
        if  not pd.isnull(row['ENTITY_ID']):
            row_to_insert = ["('"+str(row['ENTITY_ID'])+"'",
            "'"+str(row['SECT_CD'])+"'),"]
            
            row_to_insert = ','.join(row_to_insert)
            rows_to_insert.append(row_to_insert)

    rows_to_insert[-1] = rows_to_insert[-1][:-1]    
    with open(output_file,'w') as out:
        for row in rows_to_insert:
            out.write(row+'\n')


extract_from_file('EntityTableImportTest.xlsx', 0,0, 'EntityTableImportTest.txt')

def insert_entity_list():
    """ insert multiple entities into the entity table  """
    sql = "INSERT into esg.entity(entity_id,sector_key,creat_by,creat_dtm,last_upd_by,last_upd_dtm) VALUES(%s,(select sector_key from esg.sector where sector_cd=%s),current_user,now() at time zone 'utc',current_user,now() at time zone 'utc')"
    conn = None
    with open('EntityTableImportTest.txt','r') as out:
        entity_list = out.read()
        print(entity_list)
    try:
        conn = psycopg2.connect(host="localhost",database="abc",user="abcde",password="abcdefg")
        # create a new cursor
        print(conn)
        cur = conn.cursor()
        
        cur.executemany(sql,entity_list)
        # commit the changes to the database
        conn.commit()
        # close communication with the database
        cur.close()
    except (Exception, psycopg2.DatabaseError) as error:
        print(error)
    finally:
        if conn is not None:
            conn.close()


insert_entity_list()

有人可以帮忙吗？

标签： python-3.xpostgresqlpsycopg2