How to insert a list in postgres through python

Question

I have a list

A=[1,2,3,3,4,5,6,8,90,8,6,5]

I want to put this list into a postgres table

After making a cursor and connection

I tried this

for i in A:
   cusror.execute("Insert into schema1.table1 Values (%s)" ,i)
   connection.commit()

But getting an error TypeError: Not all arguments converted during string formatting. Can someone help me out please

score 0 · Answer 1 · answered Jul 25 '22 at 19:03

Use this function I will provide, just make sure to change TABLE_NAME and the columns for the ones you will be inserting:

import psycopg2

def bulkInsert(records):
    try:
        connection = psycopg2.connect(user="sysadmin",
                                      password="pynative@#29",
                                      host="127.0.0.1",
                                      port="5432",
                                      database="postgres_db")
        cursor = connection.cursor()
        # Here replace the table and the columns
        sql_insert_query = """ INSERT INTO TABLE_NAME (id, model, price) 
                           VALUES (%s,%s,%s) """

        # executemany() to insert multiple rows
        result = cursor.executemany(sql_insert_query, records)
        connection.commit()
        print(cursor.rowcount, "Record inserted successfully into mobile table")

    except (Exception, psycopg2.Error) as error:
        print("Failed inserting record into mobile table {}".format(error))

    finally:
        # closing database connection.
        if connection:
            cursor.close()
            connection.close()
            print("PostgreSQL connection is closed")


# Example of how to use the function
records_to_insert = [(4, 'LG', 800), (5, 'One Plus 6', 950)]
bulkInsert(records_to_insert)

score 0 · Answer 2 · answered Jul 25 '22 at 19:12

0

The 2nd argument to cursor.execute() should be a tuple. Try:

for i in A:
   cursor.execute("Insert into schema1.table1 Values (%s)", (i,))
   connection.commit()

answered Jul 25 '22 at 19:12

Tim Tisdall

9,914
3
52
82

score 0 · Answer 3 · answered Jul 25 '22 at 19:26

Noting down a point from documentation -

For positional variables binding, the second argument must always be a sequence, even if it contains a single variable (remember that Python requires a comma to create a single element tuple):

>>> cur.execute("INSERT INTO foo VALUES (%s)", "bar")    # WRONG
>>> cur.execute("INSERT INTO foo VALUES (%s)", ("bar"))  # WRONG
>>> cur.execute("INSERT INTO foo VALUES (%s)", ("bar",)) # correct
>>> cur.execute("INSERT INTO foo VALUES (%s)", ["bar"])  # correct

Correct answer for your issue would be

for i in A:
   cusror.execute("Insert into schema1.table1 Values (%s)" ,(i,))
   connection.commit()

score 0 · Answer 4 · answered Jul 26 '22 at 18:16

Using psycopg2 Fast execution helpers:

import psycopg2
from psycopg2.extras import execute_batch,execute_values
con = psycopg2.connect("dbname=test user=postgres host=localhost port=5432")
cur = con.cursor

A=[1,2,3,3,4,5,6,8,90,8,6,5]
param_list = [[id] for id in A]


#Using execute_batch
sql = "Insert into public.table1 values (%s)"
execute_batch(cur, sql, param_list)
con.commit()

#Using execute_values
sql = "Insert into public.table1 values %s"
execute_values(cur, sql, param_list)
con.commit()

How to insert a list in postgres through python

4 Answers4