Python DB-Api when to commit on multiple inserts/updates

Question

I have a function which updates a single row in db.

def update_one_row(conn, condition, value):
    with conn.cursor() as curr:  
        curr.execute("""UPDATE persons p 
                        SET p.age=%s
                        WHERE p.name=%s;""",
                     (value, condition))

Is it ok to use this function multiple (couple thousand) times and do conn.commit() afterwards, like this:

from pymysql import Connect
connect_args = {...}
conn = Connect(**connect_args)
for condition, value in iterable_of_conditions_values:
    update_one_row(conn, condition, value)
# Here I visually inspect in jupyter notebook if things went as expected and I accidentaly did not screw up
conn.commit()

Or should I pass curr instead of conn to update_one_row?

I am aware of curr.executemany(), but i prefer explicit loop. Is there a performance difference?

Overall I am quite lost on the usage of cursors and when to commit.

score 0 · Accepted Answer · answered Dec 06 '16 at 09:49

You have to commit when you want to apply a bunch of changes and want to have the ability to rollback / not commiting if you encourter a problem somewhere. In the most common case, this is used when changes only makes sense if applied together.

In your case, that would makes sense to commit after a few thousands. To not overcomplicate your system, the best way would be to commit only one time after your loop. Else, you would have to keep track of which rows where updated or not.

Python DB-Api when to commit on multiple inserts/updates

1 Answers1