{"id":3040,"date":"2019-11-23T19:05:32","date_gmt":"2019-11-24T02:05:32","guid":{"rendered":"https:\/\/oracletutorial.com\/?page_id=3040"},"modified":"2025-05-31T20:06:19","modified_gmt":"2025-06-01T03:06:19","slug":"managing-transaction-in-python","status":"publish","type":"page","link":"https:\/\/www.oracletutorial.com\/python-oracle\/managing-transaction-in-python\/","title":{"rendered":"Managing Transaction in Python"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the python-oracledb API to manage transactions in Python.<\/p>\n\n\n\n

Transaction management #<\/a><\/h2>\n\n\n\n

When you call the cursor.execute()<\/code> to insert, update, or delete data from a table, Python does not automatically commit the change to the database.<\/p>\n\n\n\n

To apply the change to the database, you need to call the connection.commit()<\/code> method:<\/p>\n\n\n

cursor.execute('<DML statement>'<\/span>)\nconnection.commit()<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Or to undo the change, you call the connection.rollback()<\/code> method:<\/p>\n\n\n
cursor.execute('<DML statement>'<\/span>)\nconnection.rollback()<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
By default, an uncommitted transaction is rolled back if the database connection is closed.<\/p>\n\n\n\n
Autocommitting mode #<\/a><\/h2>\n\n\n\n
The Connection<\/code> object has an attribute called autocommit<\/code> that allows you to commit the transaction automatically. <\/p>\n\n\n\n
By default, its value is set to False. To instruct python to commit the transaction automatically, you set the value of the connection.autocommit<\/code> to True<\/code> as follows:<\/p>\n\n\n
connection.autocommit = True<\/span><\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Different from the connection.commit()<\/code>, setting connection.autocommit<\/code> to True<\/code> does not require an additional roundtrip to the Oracle Database. <\/p>\n\n\n\n
Therefore, it is more efficient when you know how to use it appropriately.<\/p>\n\n\n\n
Starting a transaction explicitly #<\/a><\/h2>\n\n\n\n
A transaction starts implicitly. To explicitly start a transaction, you use the connection.begin()<\/code> method.<\/p>\n\n\n\n
A transaction can be local or global. The connection.begin()<\/code> method without any parameter starts a local transaction.<\/p>\n\n\n\n
Transaction example #<\/a><\/h2>\n\n\n\n
The following code illustrates how to manage transactions in Python:<\/p>\n\n\n\n
    \n
  • First, insert a row into the billing_headers<\/code> table and return the generated billing_no<\/code>.<\/li>\n\n\n\n
  • Second, insert some rows into the billing_items<\/code> table.<\/li>\n\n\n\n
  • Third, if the two steps succeed, commit the transaction. Otherwise, roll it back.<\/li>\n<\/ul>\n\n\n\n
    Here is the transaction.py<\/code> code:<\/p>\n\n\n
    import<\/span> oracledb\nfrom<\/span> connect import<\/span> connect\nfrom<\/span> datetime import<\/span> datetime\nimport<\/span> logging\n\ndef<\/span> insert_billing_header<\/span>(billing_header_data : tuple, cursor: oracledb.Cursor)<\/span> -> int:<\/span>\n\n    sql = '''INSERT INTO billing_headers(billing_date, amount, customer_id) \n             VALUES(:billing_date, :amount, :customer_id)  \n             RETURNING billing_no INTO :billing_no '''<\/span>\n\n    bind_params = {\n        'billing_date'<\/span>: billing_header_data[0<\/span>],\n        'amount'<\/span>: billing_header_data[1<\/span>],\n        'customer_id'<\/span>: billing_header_data[2<\/span>],\n        'billing_no'<\/span>: cursor.var(oracledb.NUMBER) \n    }\n\n    try<\/span>:\n        cursor.execute(sql, bind_params)\n        inserted_billing_no = bind_params['billing_no'<\/span>].getvalue()[0<\/span>]\n        return<\/span> inserted_billing_no\n    except<\/span> oracledb.Error as<\/span> e:\n        logging.error(f\"Error inserting billing header: {e}<\/span>\"<\/span>)\n        raise<\/span> \n\ndef<\/span> insert_billing_items<\/span>(billing_no: int, billing_items: list, cursor: oracledb.Cursor)<\/span> -> None<\/span>:<\/span>\n   \n    sql = '''INSERT INTO billing_items(billing_no, product_id, price) \n             VALUES(:billing_no, :product_id, :price)'''<\/span>\n\n    items = []\n    for<\/span> item in<\/span> billing_items:\n        items.append({\n            'billing_no'<\/span>: billing_no,\n            'product_id'<\/span>: item[0<\/span>],\n            'price'<\/span>: item[1<\/span>]\n        })\n\n    if<\/span> not<\/span> items:\n        logging.warning(f\"No billing items provided for billing_no: {billing_no}<\/span>. Skipping item insertion.\"<\/span>)\n        return<\/span>\n\n    try<\/span>:\n        cursor.executemany(sql, items)\n    except<\/span> oracledb.Error as<\/span> e:\n        logging.error(f\"Error inserting billing items for billing_no {billing_no}<\/span>: {e}<\/span>\"<\/span>)\n        raise<\/span> \n\ndef<\/span> insert_billing_doc<\/span>(billing_header_data: tuple, billing_items: list)<\/span> -> bool:<\/span> \n   \n    connection = None<\/span>\n    try<\/span>:\n        connection = connect()\n\n        with<\/span> connection.cursor() as<\/span> cursor:\n            # Insert the billing header<\/span>\n            billing_no = insert_billing_header(billing_header_data, cursor)\n\n            if<\/span> billing_no is<\/span> None<\/span>:\n                # This check is mostly for robustness, insert_billing_header should re-raise on failure<\/span>\n                logging.error(\"Failed to generate billing number. Rolling back transaction.\"<\/span>)\n                connection.rollback()\n                return<\/span> False<\/span>\n\n            # Insert the billing items<\/span>\n            insert_billing_items(billing_no, billing_items, cursor)\n\n            # Commit the transaction if all operations were successful<\/span>\n            connection.commit()\n            logging.info(f\"Billing document (billing_no: {billing_no}<\/span>) and items committed successfully.\"<\/span>)\n            return<\/span> True<\/span>\n\n    except<\/span> oracledb.Error as<\/span> e:\n        logging.error(f\"Transaction failed due to Oracle database error: {e}<\/span>\"<\/span>)\n        if<\/span> connection:\n            logging.info(\"Attempting to roll back the transaction.\"<\/span>)\n            connection.rollback()\n            logging.info(\"Transaction rolled back.\"<\/span>)\n        return<\/span> False<\/span>\n    except<\/span> Exception as<\/span> e:\n        logging.critical(f\"An unexpected error occurred during billing document insertion: {e}<\/span>\"<\/span>)\n        if<\/span> connection:\n            logging.info(\"Attempting to roll back the transaction.\"<\/span>)\n            connection.rollback()\n            logging.info(\"Transaction rolled back.\"<\/span>)\n        return<\/span> False<\/span>\n    finally<\/span>:\n        # Ensure the connection is closed even if an error occurs<\/span>\n        if<\/span> connection:\n            connection.close()\n            logging.info(\"Database connection closed.\"<\/span>)\n\n\n\nif<\/span> __name__ == '__main__'<\/span>:\n    insert_billing_doc(\n        [datetime.now(), 1<\/span>, 1000<\/span>],\n        [(1<\/span>, 200<\/span>),\n        (2<\/span>, 300<\/span>),\n        (3<\/span>, 500<\/span>)])<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
    In this tutorial, you have learned how to use python-oracledb API to manage Oracle Database transactions in Python.<\/p>\n
    \n\t
    \n\t\t
    Was this tutorial helpful?<\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t