What is the difference between connection.commit() and cursor.execute('commit')?-CodePudding

I want to know that performance wise which is a better option i.e. after inserting rows should we use connection.commit() or cursor.execute('commit') considering the fact that I am using Oracle database.

CodePudding user response：

Based on the documentation, the only apparent difference is that both belong to different objects

Connection.commit() Commit any pending transactions to the database.

Cursor.execute() Execute a statement against the database

If you have more than one cursor against the same connection, connection.commit() will commit any pending transaction which can belong to any cursor in the program.
cursor.execute('commit') you would thing that only pending transaction that belongs to the corresponding cursor will be committed. Here is the misconception, because once you commit any of the cursor in the corresponding connection, all of the remaining will be committed as well.

A demo of what I just explained:

Demo table

SQL> desc test_cursors
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 D                                                  DATE
 ID                                                 VARCHAR2(100)

Simple python program

import cx_Oracle

host="ODCGRC1R.SCGER.DEV.CORP"
port=60995
sid='odcgrc1r'
user='CPL_REP'
password='Cpl3_r3p'
sid = cx_Oracle.makedsn(host, port, service_name=sid)

sql = ('insert into test_cursors values(sysdate,1)')

connection = cx_Oracle.connect(user, password, sid, encoding="UTF-8")
cursor = connection.cursor()
cursor.execute(sql)
connection.commit()

We execute it to just insert one record, no matter what method we use the result is the same.

C:\python>python testconn.py

C:\python>sqlplus cpl_rep/Cpl3_r3p@//ODCGRC1R.SCGER.DEV.CORP:60995/odcgrc1r

SQL*Plus: Release 12.2.0.1.0 Production on Sun Oct 3 11:54:18 2021

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Last Successful login time: Sun Oct 03 2021 11:54:14  02:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

SQL> select * from test_cursors ;

D
---------
ID
--------------------------------------------------------------------------------
03-OCT-21
1

So far, so good. Our record has been committed into the table by the connection method commit ( it would have been the same by cursor.execute('commit'). Now, let's use it with several statements with the same cursor

import cx_Oracle

host="ODCGRC1R.SCGER.DEV.CORP"
port=60995
sid='odcgrc1r'
user='CPL_REP'
password='Cpl3_r3p'
sid = cx_Oracle.makedsn(host, port, service_name=sid)

sql1 = ('insert into test_cursors values(sysdate,2)')
sql2 = ('insert into test_cursors values(sysdate,3)')

connection = cx_Oracle.connect(user, password, sid, encoding="UTF-8")
cursor = connection.cursor()
cursor.execute(sql1)
cursor.execute(sql2)
connection.commit()

Let's run it and confirm that all records have been inserted.

C:\python>python testconn.py

C:\python>sqlplus cpl_rep/Cpl3_r3p@//ODCGRC1R.SCGER.DEV.CORP:60995/odcgrc1r

SQL*Plus: Release 12.2.0.1.0 Production on Sun Oct 3 11:56:49 2021

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Last Successful login time: Sun Oct 03 2021 11:56:44  02:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

SQL>  select * from test_cursors ;

D                  ID
-------------------------------------------------------------------
03-OCT-21          1

03-OCT-21          2

03-OCT-21          3

Well, as predicted, the connection.commit() method has done its job, committing all pending transactions in the connection.

Now, let's switch to cursor.execute('commit') instead. In the next example, on purpose, I am creating two different cursors from the same connection, but I am only going to commit one of them. I am setting autocommit to false

import cx_Oracle

host="ODCGRC1R.SCGER.DEV.CORP"
port=60995
sid='odcgrc1r'
user='CPL_REP'
password='Cpl3_r3p'
sid = cx_Oracle.makedsn(host, port, service_name=sid)

sql1 = ('insert into test_cursors values(sysdate,4)')
sql2 = ('insert into test_cursors values(sysdate,5)')

connection = cx_Oracle.connect(user, password, sid, encoding="UTF-8")
connection.autocommit = False
cursor1 = connection.cursor()
cursor2 = connection.cursor()
cursor1.execute(sql1)
cursor2.execute(sql2)
cursor1.execute('commit')

We execute it

C:\python>python testconn.py

C:\python>sqlplus cpl_rep/Cpl3_r3p@//ODCGRC1R.SCGER.DEV.CORP:60995/odcgrc1r

SQL*Plus: Release 12.2.0.1.0 Production on Sun Oct 3 12:06:13 2021

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Last Successful login time: Sun Oct 03 2021 12:06:11  02:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

SQL> set lines 80
SQL> col id for a20
SQL> select * from test_cursors ;

D         ID
--------- --------------------
03-OCT-21 1
03-OCT-21 2
03-OCT-21 3
03-OCT-21 4
03-OCT-21 5

Well, even though the cursor 2 was not committed in the program, it did indeed commit in the database. Why ? Because both of them belong to the same connection.cursor() , therefore when I commit one, I was indeed committing all of them.

Coming back to the purpose of your question, I believe both of them are the same in terms of what is happening in the database. Normally speaking I would use connection.commit() for closing a connection with any number of different transactions. However, for performance reasons, if you are handling big transactions, it is better to commit some work along the way, for example to avoid undo problems. But again, that depends entirely of what your code is doing.