2

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
RocketSocks22
  • 391
  • 1
  • 4
  • 20
  • 1
    You need one parameter placeholder in the SQL query for each list item. You can do something like: `",".join(["%s"]*len(arr))` to generate the sequence of placeholders. In a separate step before you call `.execute()` you would then use string-formatting to put that into your query. Also the creation of `arr2` that you're doing is unnecessary. You can pass `arr` as the 2nd argument to `.execute()` and it'll work just fine. – mechanical_meat Nov 21 '21 at 22:27
  • `execute_batch` is probably what you need - see also the list of 'fast' helpers: https://www.psycopg.org/docs/extras.html#fast-exec – match Nov 21 '21 at 22:29
  • 1
    @mechanical_meat Thank you, that is what I was missing. The final solution I ended up going with was along the lines of `arr3=','.join([cur.mogrify("(%s)", x).decode("utf-8") for x in arr2])` and then `cur.execute("INSERT INTO my_table (my_value) VALUES (my_values) " + arr3`. Feel free to add an answer if you would like me to give you a checkmark – RocketSocks22 Nov 22 '21 at 01:42
  • a related, but more general question: https://stackoverflow.com/questions/8134602/psycopg2-insert-multiple-rows-with-one-query – Jasen Nov 22 '21 at 03:57

2 Answers2

1

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!
klin
  • 112,967
  • 15
  • 204
  • 232
  • 2
    With this solution I am running into an eror `INSERT has more expressions than target columns` – RocketSocks22 Nov 22 '21 at 01:09
  • The code is tested and should work after copy & paste. Are you sure you did not modify it? What is the psycopg2 version? `print(psycopg2.__version__)` – klin Nov 22 '21 at 09:38
0

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 [].

Belayer
  • 13,578
  • 2
  • 11
  • 22