How to handle exception with executemany (MySQL and Python)

Question

I have a python script using executemany to bulk insert rows into a MySQL table. The data is retrieved from different APIs, so every now and then there is unexpected data which leads to a row causing exception.

If I understand correctly - when calling executemany with 1,000 rows and one of them is problematic - the entire bulk is not inserted.

I want to find a way to submit 1,000 records and successfully load the ones that are not problematic. So for example - if one of a thousand is problematic it will not be loaded, but all other 999 will be loaded.

What's the best practice on that? I'm thinking of catching an exception and creating a fallback to re-submit all 1000 one by one - but it seems like there must be a better way to achieve the same outcome.

Advice?

Answer 1

Doing an "INSERT OR IGNORE" statement at the beginning of your "executemany" query will let you do exactly this - it will only add the values that don't bring an error.

The only downside is that you no longer can see what error is happening. For example,

Original database:

('kaushik', 3)
('maria', 4)
('shreya', 38)

Query: (in python)

listofnames = [
('kaushik', 3),
('maria', 4),
('jane', 56)
]

c.executemany("INSERT OR IGNORE INTO bob (name, number) VALUES (?,?)", 
listofnames)

Final db:

('kaushik', 3)
('maria', 4)
('shreya', 38)
('jane', 56)