How to insert a list in postgres through python-CodePudding

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

CodePudding user response：

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)

CodePudding user response：

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()

CodePudding user response：

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()

CodePudding user response：

The correct way to insert your list A in a performant way in a pg DB is as follows

cur.executemany(sql,[[a] for a in A])
con.commit()

You do not want to use loops if you can insert in one pass, but as explained here the executemany does not accept plain list, so you must transform it to a sequence of sequences