How to avoid data loss in python postgresql bulk insertion

Question

I want to insert my data into PostgreSQL 1000 by 1000 records ( Bulk Insertion ) to make it quick and low load on the DBMS, So my code for now is :

cursor.execute("INSERT INTO bar(first_name,last_name) VALUES ('David', 'Bar')")
cursor.execute("INSERT INTO bar(first_name,last_name) VALUES ('David2', 'Bar2')")
cursor.execute("INSERT INTO bar(first_name,last_name) VALUES ('David3', 'Bar3')")
.... etc
connection.commit()

And as you can see i committed the changes at the end and that's saving a lot of time for me instated of committing the changes after every insert query. The problem is if any query crashed for any reason ( invalid data ), all the quires will fail to execute and i will lose the data. Is there anyway to save the time of the insertion and avoid the data loss at the same time??

See [this answer][1] to your question [1]: http://stackoverflow.com/questions/12206600/how-to-speed-up-insertion-performance-in-postgresql — Mohammad Tayseer, Apr 22 '15 at 15:04

score 0 · Answer 1 · answered Apr 22 '15 at 14:45

It depends on your requirements of course, depending on your transaction needs I would recommend one of the following options:

1. Using `SAVEPOINT`'s (subtransactions):

BEGIN;
    SAVEPOINT savepoint;
    INSERT ...;
    RELEASE savepoint;
    SAVEPOINT savepoint;
    INSERT ...;
    /* If you're getting an error */
    ROLLBACK TO SAVEPOINT savepoint;
COMMIT;

2. Using `AUTOCOMMIT`

SET AUTOCOMMIT TO ON;
INSERT ...
INSERT ...
INSERT ...

How to avoid data loss in python postgresql bulk insertion

1 Answers1

1. Using SAVEPOINT's (subtransactions):

2. Using AUTOCOMMIT

1. Using `SAVEPOINT`'s (subtransactions):

2. Using `AUTOCOMMIT`