import multiple rows into SQL table with unique constraint using python

Question

I'm trying to import multiple rows into my SQL table which has 'id' as a unique constraint, which s supposed to get rejected by SQL insert due to constraint violation.

I'm using PostgreSQL

Assuming my data are

   records_to_insert = [ [2,'Jon','2018-01-11', 26] ,
                         [3,'Jane','2017-12-11', 27],
                         [4,'Bill','2018-03-23', 26] ]

and in the database I ALREADY HAVE

2,'Jon','2018-01-11', 26

So now if I do

   cur = db_conn.cursor()
    cur.execute("SAVEPOINT bulk_savepoint")
    try:
        cur.executemany("INSERT INTO  mytable VALUES (%s,%s,%s,%s);", records_to_insert )
    except:
        cur.execute("ROLLBACK TO SAVEPOINT bulk_savepoint")

    db_conn.commit()

Because the rows have one row that violates my constraint, it does not insert the rest of the rows which do not violate the id constraint and nothing gets inserted,

how can I insert the rows that do not violate the constraint without looping through the list? I have huge data that I'm going to import and looping through each element will take a long time.

Thanks.

Thanks, it's extremely slow as I'm using a cloud database. I'm sure there is a way to do it as I have done this before, — user9578149
– user9578149, Commented Feb 25, 2019 at 19:01
I think it's only for MySQL which I do not use, I'm working with PostgreSQL. — user9578149
– user9578149, Commented Feb 25, 2019 at 19:12
I just updated my answer for PostgreSQL. In the future, tag your answer with the database engine you're using and then people can curate their answers to your setup. — pistolpete
– pistolpete, Commented Feb 25, 2019 at 19:21
Is this id in the file relevant? Meaning that this of column often depends on insertion order so Jon (id=3) in one file might be at id=4 in another. If you dont know you could just leave out the id in the insert and it would just get autogenerated by postgres. Really depends on your data, your feeds and foreign key considerations. — JL Peyret
– JL Peyret, Commented Feb 25, 2019 at 22:19

pistolpete · Accepted Answer · 2019-02-25 19:14:41Z

3

You're looking for INSERT IGNORE

change

cur.executemany("INSERT INTO mytable VALUES (%s,%s,%s,%s);", records_to_insert )

to

cur.executemany("INSERT IGNORE INTO mytable VALUES (%s,%s,%s,%s);", records_to_insert )

For PostgreSQL use ON CONFLICT DO NOTHING

cur.executemany("""
INSERT INTO mytable VALUES (%s,%s,%s,%s)
ON CONFLICT DO NOTHING;""", records_to_insert )

edited Feb 25, 2019 at 19:14

answered Feb 25, 2019 at 19:00

pistolpete

1,02811 silver badges20 bronze badges

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

2 Comments

user9578149 Over a year ago

Thanks, I'm using PostgreSQL and the ignore case is not working there :(

pistolpete Over a year ago

On PostgreSQL you can use ON CONFLICT DO NOTHING postgresqltutorial.com/postgresql-upsert. I'm updating my answer to reflect that.

Collectives™ on Stack Overflow

import multiple rows into SQL table with unique constraint using python

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related