Summary: in this tutorial, you will learn how to use cx_Oracle API to manage transactions in Python.
Transaction management
When you call the Cursor.execute()
to insert, update, or delete data from a table, the cx_Oracle does not automatically commit the change to the database.
To apply the change to the database, you need to call the Connection.commit()
method:
cursor.execute('<DML statement>')
connection.commit()
Code language: Python (python)
Or to undo the change, you call the Connection.rollback()
method:
cursor.execute('<DML statement>')
connection.rollback()
Code language: Python (python)
By default, an uncommitted transaction is rolled back if the database connection is closed.
Autocommitting mode
The Connection
object has an attribute called autocommit
that allows you to commit the transaction automatically. By default, its value is set to False. To instruct cx_Oracle to commit the transaction automatically, you set the value of the Connection.autocommit
to True
as follows:
connection.autocommit = True
Code language: Python (python)
Different from the Connection.commit()
, setting Connection.autocommit
to True
does not require an additional roundtrip to the Oracle Database. Therefore, it is more efficient when you know how to use it appropriately.
Starting a transaction explicitly
A transaction starts implicitly. To explicitly start a transaction, you use the Connection.begin()
method.
A transaction can be local or global. The Connection.begin()
method without any parameter starts a local transaction.
Transaction example
The following code illustrates how to manage transactions in Python:
- First, insert a row into the
billing_headers
table and return the generatedbilling_no
. - Second, insert some rows into the
billing_items
table. - Third, if the two steps succeed, commit the transaction. Otherwise, roll it back.
Here is the transaction.py
code:
import cx_Oracle
import config as cfg
from datetime import datetime
def insert_billing_header(billing_header, cursor):
"""
Insert a new row into the billing_header table and
return the inserted billing no
:param billing_header:
:param cursor:
:return:
"""
sql = ('insert into billing_headers(billing_date, amount, customer_id) '
'values(:billing_date,:amount,:customer_id) '
'returning billing_no into :billing_no')
billing_no = cursor.var(int)
# add the variable to billing_header list
billing_header.append(billing_no)
# execute the insert statement
cursor.execute(sql, billing_header)
# return the inserted value
return billing_no.getvalue()[0]
def insert_billing_items(billing_no, billing_items, cursor):
"""
insert billing items
:param billing_no:
:param billing_items:
:param cursor:
:return:
"""
# insert into billing items
sql = ('insert into billing_items(billing_no, product_id, price) '
'values(:billing_no,:product_id,:price)')
items = []
for item in billing_items:
items.append((billing_no, item[0], item[1]))
cursor.executemany(sql, items)
def insert_billing_doc(billing_header, billing_items):
"""
Insert a billing document
:param billing_header:
:param billing_items:
:return:
"""
try:
# establish a new connection
with cx_Oracle.connect(cfg.username,
cfg.password,
cfg.dsn,
encoding=cfg.encoding) as connection:
# create a cursor
with connection.cursor() as cursor:
# insert the billing header
billing_no = insert_billing_header(billing_header, cursor)
# rollback the transaction if no billing no is generated
if not billing_no:
connection.rollback()
# insert the billing items
insert_billing_items(billing_no, billing_items, cursor)
# commit the transaction
connection.commit()
except cx_Oracle.Error as error:
print(error)
if __name__ == '__main__':
insert_billing_doc(
[datetime.now(), 1, 1000],
[(1, 200),
(2, 300),
(3, 500)])
Code language: Python (python)
In this tutorial, you have learned how to use cx_Oracle API to manage Oracle Database transactions in Python.