Insert list of integers into postgres table with psycopg2-CodePudding

Given a list of integers, I would like to insert every integer into a new row in a Postgres table, ideally in a very efficient way (i.e. not looping through and inserting 1-by-1). arr = [1,2,3,4,5]. What I've tried doing is converting this to a list of tuples, arr2 = [(i,) for i in arr], and then feeding this into postgres with cur.execute("INSERT INTO my_table (my_value) VALUES (%s)", arr2, but I am receiving an error: Not all arguments converted during string formatting`. What exactly am I doing wrong here?

Full code

import psycopg2

conn = psycopg2.connect(host="myhost", database="mydb", user="postgres", password="password", port="5432")
cur = conn.cursor()
arr = [1,2,3,4,5]
arr2 = [(i,) for i in arr]
cur.execute("INSERT INTO my_table (my_value) VALUES (%s)", arr2

CodePudding user response：

What exactly am I doing wrong here?

You are trying to insert a list of integers into a single row.

Instead, use execute_values() to insert many rows in a single query. Do not forget to commit the insert:

#...

cur = conn.cursor()
arr = [1,2,3,4,5]
arr2 = [(i,) for i in arr]

from psycopg2.extras import execute_values

execute_values(cur, "INSERT INTO my_table (my_value) VALUES %s", arr2)
conn.commit()   # important!

CodePudding user response：

I am not familiar yet with psycopg2, working on it, but a ways to go. So I'll give the pure sql version. Postgres has a a pretty good set of built in array functions, one being UNNEST(). That function takes a array as a parameter and returns the individual entries. So you just need to provide an array to the query. (see demo).

insert into my_table(my_column) 
    select unnest( array [1,2,3,4,5] );

Borrowing (ie copying) your code perhaps:

import psycopg2
conn = psycopg2.connect(host="myhost", database="mydb", user="postgres", password="password", port="5432")
cur = conn.cursor()
arr = [1,2,3,4,5]
cur.execute("insert into my_table (my_column) select unnest (array [%s])", arr

But I am not sure if that gets the Postgres Array structure; it neede the [].