When should I commit with SQLAlchemy using a for loop?

Question

Which is correct? I know the first will work but I suspect it's more work for the DB than is possibly necessary. Will the second work just as well but with less work for the DB? I'm 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 have configured your Session. Specifically the autoflush and autocommit settings. Also you should be using an engine that has good support for transactions, such as innodb.

Assuming you have autocommit and autoflush both off then you would be flushing your insert to the server, committing the prior transaction and then creating another transaction on every iteration which is creating a lot of unnecessary work both in 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 do need the loop then definitely apply the 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() 

An additional note, if something were to go wrong part way through the loop your transaction would rollback writes only to your prior commit in the loop which probably isn't what you want if you are using transactions. For example only half your items in the list might be written to the database if an error occurs half way through your loop. Whereas calling commit outside the loop guarantees that your loop has finished and will be ALL committed or ALL rolled back.

Answer 2

To add on the solution: This is how you can use add_all()

user1 = User(name='user1') user2 = User(name='user2') session.add(user1) session.add(user2)  session.commit()     # write changes to the database 

Instead of the two add lines use

session.add_all([user1, user2])