Insert multiple items into PostgreSQL table

Question

I would like to insert multiple rows in PostgreSQL table. I attached the program. I used a list of items: new_record = ['one','two','three']. Actually, this list will be filled with 100000 items from Amazon products but now I fill with 3 items. I make a for loop to iterate but it filled with only one element. I would like to print all the items. For example:

"INSERT INTO products(products_amazon) VALUES ('one')"
"INSERT INTO products(products_amazon) VALUES ('two')"
"INSERT INTO products(products_amazon) VALUES ('three')"

I will appreciate any help.

import psycopg2
from pprint import pprint

database = "amazon_products"
host = "localhost"
user = "postgres"
password = "123QQsuccess"


class DatabaseConnection:
    def __init__(self):
        try:
            self.connection = psycopg2.connect(database=database, host=host, user=user, password=password)
            self.connection.autocommit = True
            self.cursor = self.connection.cursor()
        except:
            pprint('Cannot connect to database')

    def insert_new_record(self):
        new_record = ['one','two','three']
        for item in new_record:
            insert_command = "INSERT INTO products(products_amazon) VALUES ('" + item + "')"
        pprint(insert_command)
        self.cursor.execute(insert_command)

if __name__ == '__main__':
    database_connection = DatabaseConnection()
    database_connection.insert_new_record()

Jim Jones · Accepted Answer · 2018-02-02 10:09:23Z

1

If for whatever reason you really want to insert all statements at once, just concatenate all insert statements using ; and sent it as a single string to the database.

Like this:

"INSERT INTO products(products_amazon) VALUES ('one');
INSERT INTO products(products_amazon) VALUES ('two');
INSERT INTO products(products_amazon) VALUES ('three');"

answered Feb 2, 2018 at 10:09

Jim Jones

19.9k3 gold badges44 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ryy77 Over a year ago

I would like to insert about 100000 items from Amazon into a postgresql table and I have to insert items from a list returned by the products links

Jim Jones Over a year ago

Well, I'm not familiar with the products links from Amazon, but regarding inserting 100k items to a table you don't have many options: 1) COPY from a text file 2) the compact INSERT statement suggested by Jestin and 3) a chain of INSERT statements concatenated by semicolon as suggested above. All you have to do is to convert this list of yours (using a loop) into a huge string with INSERT statements and when leaving the loop you execute this string in the database. Sorry for not being able to help you with the python thing..

localroot · Accepted Answer · 2018-02-02 08:49:05Z

1

Did you tried like this.? I hope It'll work in your case

INSERT INTO products(products_amazon) VALUES ('one'), ('two'), ('three')....

answered Feb 2, 2018 at 8:49

localroot

5563 silver badges13 bronze badges

5 Comments

ryy77 Over a year ago

How can I use for 100000 items?

localroot Over a year ago

You can run a loop and append it to the prepared query.Since it's simple insert it'll be very fast. But for safer side chunk the items to 10,000 and make multiple queries.

ryy77 Over a year ago

Actually, as you can see in the code I use a for loop but it added only the last element. How can I use the for loop to insert all the items?

localroot Over a year ago

You can also try COPY FROM of postgress COPY myTable FROM '/path/to/file/on/server' ( FORMAT CSV, DELIMITER('|') );

localroot Over a year ago

You are running query for each item, Instead you shoul'd prepare a bulk query and finally run the query

Collectives™ on Stack Overflow

Insert multiple items into PostgreSQL table

2 Answers 2

2 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related