python - 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?

标签： pythonmysqlexceptionexecutemany

Answer 1

在“executemany”查询的开头执行“INSERT OR IGNORE”语句将使您完全做到这一点 - 它只会添加不会带来错误的值。

唯一的缺点是您再也看不到发生了什么错误。例如，

原始数据库：

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

查询：（在python中）

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

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

最终分贝：

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