Why the Need to Commit Explicitly When Doing an Update?
Here's My Code: Import Cx_Oracle Conn = Cx_Oracle. Connect(Usr, Pwd, Url) Cursor = Conn. Cursor() Cursor. Execute("Update So Set Status='Pe' Where Id='100'")...
Here's my code:
import cx_Oracle
conn = cx_Oracle.connect(usr, pwd, url)
cursor = conn.cursor()
cursor.execute("UPDATE SO SET STATUS='PE' WHERE ID='100'")
conn.commit()
If I remove the conn.commit(), the table isn't updated. But for select statements, I don't need that conn.commit(). I'm curious why?
3 Answers
The DB-API spec requires that connecting to the database begins a new transaction, by default. You must commit to confirm any changes you make, or rollback to discard them.
Note that if the database supports an auto-commit feature, this must be initially off.
Pure SELECT statements, since they never make any changes to the database, don't have to have their changes committed.
commit is used to tell the database to save all the changes in the current transaction.
Select does not change any data so there is nothing to save and thus nothing to commit
See wikipedia for transactions
Others have explained why a commit is not necessary on a SELECT statement. I just wanted to point out you could utilize the autocommit property of the Connection object to avoid having to manually execute commit yourself:
import cx_Oracle
with cx_Oracle.connect(usr, pwd, url) as conn:
conn.autocommit = True
cursor = conn.cursor()
cursor.execute("UPDATE SO SET STATUS='PE' WHERE ID='100'")
cursor.close()
This is especially useful when you have multiple INSERT, UPDATE, and DELETE statements within the same connection.
Robert Thorne covers electric vehicle innovations, autonomous driving systems, global mobility trends, and automotive engineering developments.
