When should I commit with SQLAlchemy with a for loop?

Question

What is right? I know that the first will work, but I suspect that there is more work for the database than is possible. Will the second job be just as good, but with less work for the database? I am using MySQL FWIW.

for item in items: db.session.add(item) db.session.commit() 

or

 for item in items: db.session.add(item) db.session.commit() 

Answer 1

I think your second solution is better, but it depends on how you set up your session. In particular, autorun and autorun settings. You should also use a engine that has good support for transactions such as innodb.

Assuming you have autocommit and autoflush, you can delete your insert on the server, commit the previous transaction, and then create another transaction at each iteration, which creates a lot of unnecessary work in both SQLAlchemy and MySQL.

I would recommend using add_all if you have a simple list of items to add, as in your example, otherwise if you need a loop, then be sure to use commit outside the loop.

http://docs.sqlalchemy.org/en/latest/orm/session.html#sqlalchemy.orm.session.Session.add_all

 db.session.add_all(items) db.session.commit() 

Extra note: if something went wrong through the loop, your transaction will only roll back for your previous commit in the loop, which is probably not the one you want if using transactions. For example, only half of your items in a list can be written to the database if an error occurs halfway through your loop. While calling commit outside the loop ensures that your loop is finished and there will be an ALL, or ALL rollback.