Commit multiple inserts with MySQLdb on Python

Question

I did a profiling of my python program and noticed that they were a lot of calls to commit and they took a lot of time.

Is it better performance wise to do :

db = MySQLcb.connect(...)
c = db.cursor(...)
c.execute('INSERT...)
c.commit()
c.execute('INSERT...)
c.commit()

or

db = MySQLcb.connect(...)
c = db.cursor(...)
c.execute('INSERT...)
c.execute('INSERT...)
c.commit()

Knowing that I am making a lot of inserts (thousands).

In MySQLdb, you can use autocommit=True in connect(). And for many inserts into same table, look into executemany. — Parfait
– Parfait, Commented Mar 7, 2018 at 18:39

holdenweb · Accepted Answer · 2018-03-07 17:04:08Z

1

As long as you are using InnoDB tables you might find the advice on this page useful.

Most databases have facilities for bulk insert, and MySQL is no exception.

If you find your insertions slow, make sure that you don't have autocommit set, as this forces a transaction around each individual operation.

answered Mar 7, 2018 at 17:04

holdenweb

37.8k7 gold badges62 silver badges80 bronze badges

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

5 Comments

flyingdutchman Over a year ago

Thank you for the link, it is really helpful ! But by question remains, do I need to do a db.commit after every c.executemany(...) I am doing ? Or is one at the end enough ?

Parfait Over a year ago

@flyingdutchman ... your code above does not use executemany() which you only have to call once on a list of values.

flyingdutchman Over a year ago

@Parfait Yes I know, but it is not the point here, I am trying to ask if I need to do a manual commit after each execute or executemany()

Parfait Over a year ago

@flyingdutchman ... See my comment above where you can set autocommit.

holdenweb Over a year ago

I do know that you can commit after each execute/executemany but you don't have to. You could, however, experience performance issues if you do this in an unthinking way.

Collectives™ on Stack Overflow

Commit multiple inserts with MySQLdb on Python

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related