CODEWITHSUNDEEP
mysqlsqlpostgresql
Boky
Boky

Reputation: 12074

Proper way to use sql commit

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?

Upvotes: 0

Views: 62

Answers (1)

jwg
jwg

Reputation: 5837

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.

Upvotes: 1

Related Questions