Managing Transaction in Python

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()

Or to undo the change, you call the Connection.rollback() method:

cursor.execute('<DML statement>')
connection.rollback()

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 sets to False. To instruct cx_Oracle commit the transaction automatically, you set the value of the Connection.autocommit to True as follows:

connection.autocommit = True

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 transaction in Python:

• First, insert a row into the billing_headers table and return the generated billing_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)])

In this tutorial, you have learned how to use cx_Oracle API to manage Oracle Database transactions in Python.