24

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?

tshepang
  • 12,111
  • 21
  • 91
  • 136
  • 12
    i spent 6 hours and 25 minutes trying to figure out why data doesn't get updated in the database just so i can see this post and realize i forgot to add `conn.commit()`. – busuu Sep 17 '18 at 16:31

3 Answers3

30

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.

tshepang
  • 12,111
  • 21
  • 91
  • 136
bobince
  • 528,062
  • 107
  • 651
  • 834
  • While weird enough, I could not get SELECTs to return new data unless using autocommit = True. So was looking why would Python + Mysql would require commit when running SELECT. Any ideas? – ACV Jun 09 '21 at 21:37
  • Ha. Found the answer to my question above: "When the autocommit is turned off, you must commit transactions when using transactional storage engines such as InnoDB or NDBCluster." https://dev.mysql.com/doc/connectors/en/connector-python-api-mysqlconnection-autocommit.html Seems strange.. – ACV Jun 09 '21 at 21:42
10

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.

Erik Anderson
  • 4,915
  • 3
  • 31
  • 30
  • Doing this is not necessarily a good thing. In many cases you want all of these or none of these to occur not say 2 out of 3 – mmmmmm Dec 20 '21 at 09:56
9

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

mmmmmm
  • 32,227
  • 27
  • 88
  • 117