Proper way to use sql commit

Question

I have code as follows:

try:
    conn = db_connection()
    cur = conn.cursor()

    if is_address_changed(vehicle, conn, cur):
        update_vehicle_address(vehicle, conn, cur)
        conn.commit()

    if is_price_changed(vehicle, conn, cur):
        update_vehicle(vehicle, conn, cur)
        insert_vehicle_price(vehicle, conn, cur)
        conn.commit()

    conn.close()
except Exception as e:
    capture_error(str(e))

Thus, in my code I have conn.commit() in both if statements.

Is this correct approach? Or could I add only one above conn.close()

What would happen in one and in other case?

one above close

danblack
– danblack

2018-09-20 08:30:11 +00:00
Commented Sep 20, 2018 at 8:30 — danblack
– danblack, Commented Sep 20, 2018 at 8:30

jwg · Accepted Answer · 2018-09-20 08:34:24Z

1

The way that the above code works is that the behavior would be exactly the same if you had a single call to commit() above the call to close(). So to make the code more readable it would be better to have a single call. This would also make it easier to modify the code without forgetting the call to commit().

In terms of how database code should be written, it's usually best to commit as late as possible - right before closing if possible. This means that if something goes wrong you can roll back all the data easily. Exceptions would be if you know explicitly that you want some part of the update to go through even if a later part fails.

answered Sep 20, 2018 at 8:34

jwg

5,8873 gold badges46 silver badges59 bronze badges

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

Collectives™ on Stack Overflow

Proper way to use sql commit

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related