psycopg2 - Inserting multiple rows that have multiple columns faster

Question

I'm trying to insert multiple rows into my database, and currently I do not know a way to insert them all at the same time or any other method which will help save time (sequentially it takes about ~30s for around 300 rows).

My 'rows' are are tuples in a list of tuples (converted into tuple of tuples), e.g. [(col0, col1, col2), (col0, col1, col2), (.., .., ..), ..]

def commit(self, tuple):
    cursor = self.conn.cursor()
    for tup in tuple:
        try:
            sql = """insert into "SSENSE_Output" ("productID", "brand", "categoryID", "productName", "price", "sizeInfo", "SKU", "URL", "dateInserted", "dateUpdated")
              values (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"""

            cursor.execute(sql, tup)
            self.conn.commit()
        except psycopg2.IntegrityError:
            self.conn.rollback()
            sql = 'insert into "SSENSE_Output" ' \
                  '("productID", "brand", "categoryID", "productName", "price", "sizeInfo", "SKU", "URL", "dateInserted", "dateUpdated")' \
                  'values (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s) on conflict ("productID") do update set "dateUpdated" = EXCLUDED."dateUpdated"'
            cursor.execute(sql, tup)
            self.conn.commit()
        except Exception as e:
            print(e)

I have also tried commiting after the for loop is done, but still results in the same amount of time. Are there any ways to make this insert significantly faster?

You should build just one large insert statement instead of many of them, that will improve your query — Gustavo Topete
– Gustavo Topete, Commented Mar 5, 2018 at 14:54

J. Bakker · Accepted Answer · 2018-03-05 15:08:30Z

1

In postgres you can use a format like:

INSERT INTO films (code, title, did, date_prod, kind) VALUES
('B6717', 'Tampopo', 110, '1985-02-10', 'Comedy'),
('HG120', 'The Dinner Game', 140, DEFAULT, 'Comedy');

Due to your record base exception handling you can better first resolve the duplicates before generating this query as the whole query might fail when an integrity error occurs.

answered Mar 5, 2018 at 15:08

J. Bakker

3661 silver badge6 bronze badges

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

Comments

Gustavo Topete · Accepted Answer · 2018-03-05 15:00:02Z

0

Building one large INSERT statement instead of many of them will considerably improve the execution time, you should take a look here. It is for mysql, but I think a similar approach apply for postgreSQL

answered Mar 5, 2018 at 15:00

Gustavo Topete

1,3261 gold badge9 silver badges15 bronze badges

Collectives™ on Stack Overflow

psycopg2 - Inserting multiple rows that have multiple columns faster

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related