Getting value error for INT when posting DataFrame values to psycopg2-CodePudding

I'm trying to insert a data to postgresql from a dataframe like this

df["code"] = df["code"].astype(int)
insert_sql = '''
            INSERT INTO cs_houmon_ (code, name, city, add, phone)
            VALUES (%s, %s, %s, %s, %s)
            ON CONFLICT ON CONSTRAINT code DO NOTHING;
            '''
try:
    with conn.cursor() as cur:
        cur.execute(insert_sql, (df.code.to_list(), df.name.to_list(), df.city.to_list(), df.add.to_list(), df.phone.to_list()))
        conn.commit()
        cursor.close()

except Exception as e:
        log(str(e))

But getting this error code

code is of type integer but expression is of type integer[]
LINE 3: VALUES (ARRAY[1,2,3,4,5,6,7,8,9,10,11,12,13,14,1...

My sql table was created like this

CREATE TABLE cs_houmon_ (code integer, ...

CodePudding user response：

The error says what's the problem - the program expects integer and you pass list of integers there.

It's because execute is used to insert single row and as second parameter you should have single tuple of lenght 5. If you want to insert many parameters, use executemany and pass list of tuples

cur.execute(insert_sql, list(zip(l1, l2, l3, l4, l5))