SQLAlchemy - bulk insert ignore: "Duplicate entry"

Question

I have a table named user_data, the column id and user_id as the unique key. I want to import some history data to this table. I use bulk_insert_mappings method to batch insert data. But there are errors as below:

IntegrityError: (pymysql.err.IntegrityError) (1062, u"Duplicate entry '1-1234' for key 'idx_on_id_and_user_id'")

How to ignore this error and discard duplicate data when batch insert?

please check your models, you might have been entering the similar value in the primary key or unique key field that is causing the problem — Chitrank Dixit
I know there are duplicate value, but I cannot change the history data, I just want to ignore the error and insert operation continue, just like mysql insert ignore... statement. — pangpang
Does this answer your question? Dealing with duplicate primary keys on insert in SQLAlchemy (declarative style) — ospider

Philliproso Philliproso · Accepted Answer · 2016-04-08T08:07:13

You should handle every error. But if you really want to just ignore all errors, you can't really do a bulk insert. Sometimes there will be integrity errors in the actual data you are importing. You have to insert one by one and ignore. I would only use this in once off scripts.

for item in dict_list:
    try:
        session.merge(orm(**item))
        session.commit()
    except Exception as e:
        session.rollback()

SQLAlchemy - bulk insert ignore: "Duplicate entry"

1 Answers