python - Psycopg2 Postgres Unnesting a very long array to insert it into a tables column

Question

My database is on postgres and is local

I have an array that is in the form of:

[1,2,3,...2600]

As you can see it is a very long array so I cant type the elements one by one to insert them So I wanted to use unnest() function to make it like this:

1

2

3

|

2600

and maybe go from there however I still need to write the unnest like unnest(array [1,...,2600]) to work but ofcourse that didnt work

So how do I insert an array as rows of the same column at the same time?

标签： pythonsqlpostgresqlpsycopg2

Answer 1

您可以使用execute_values将所有数据批量放入表中：

import psycopg2
from psycopg2.extras import execute_values

conn = psycopg2.connect(conn_string)
cursor = conn.cursor()
insert_query = "insert into table_name (col_name) values %s"

# create payload as list of tuples
data = [(i,) for i in range(1, 2601)] 

execute_values(cursor, insert_query, data)
conn.commit()