3-DML.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![Banner](images/banner.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# DML - INSERT, UPDATE, DELETE, and MERGE Statements"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import oracledb"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "un = \"pythondemo\"\n",
    "pw = \"welcome\"\n",
    "cs = \"localhost/orclpdb1\"\n",
    "\n",
    "connection = oracledb.connect(user=un, password=pw, dsn=cs)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor = connection.cursor()\n",
    "cursor.execute(\"drop table mytab\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute(\"create table mytab (id number, data varchar2(1000))\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Binding for Insertion\n",
    "\n",
    "Documentation reference link: [Using Bind Variables](https://python-oracledb.readthedocs.io/en/latest/user_guide/bind.html)\n",
    "\n",
    "Binding is very, very important. It:\n",
    "- eliminates escaping special characters and helps prevent SQL injection attacks\n",
    "- is important for performance and scalability"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "with connection.cursor() as cursor:\n",
    "    cursor.execute(\"truncate table mytab\")\n",
    "\n",
    "    sql = \"insert into mytab (id, data) values (:idVal, :dataVal)\"\n",
    "\n",
    "    # bind by position using a sequence (list or tuple)\n",
    "    cursor.execute(sql, [1, \"String 1\"])\n",
    "    cursor.execute(sql, (2, \"String 2\"))\n",
    "\n",
    "    # bind by name using a dictionary\n",
    "    cursor = connection.cursor()\n",
    "    cursor.execute(sql, {\"idVal\": 3, \"dataVal\": \"String 3\"})\n",
    "\n",
    "    # bind by name using keyword arguments\n",
    "    cursor.execute(sql, idVal=4, dataVal=\"String 4\")\n",
    "\n",
    "    print(\"Done\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Batch execution - Inserting multiple rows with executemany()\n",
    "\n",
    "Documentation reference link: [Executing Batch Statements and Bulk Loading](https://python-oracledb.readthedocs.io/en/latest/user_guide/batch_statement.html)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "with connection.cursor() as cursor:\n",
    "    cursor.execute(\"truncate table mytab\")\n",
    "\n",
    "    rows = [ (1, \"First\" ),\n",
    "             (2, \"Second\" ),\n",
    "             (3, \"Third\" ),\n",
    "             (4, \"Fourth\" ),\n",
    "             (5, \"Fifth\" ),\n",
    "             (6, \"Sixth\" ),\n",
    "             (7, \"Seventh\" ) ]\n",
    "\n",
    "    # Using setinputsizes helps avoid memory reallocations.\n",
    "    # The parameters correspond to the insert columns.  \n",
    "    # The value None says use python-oracledb's default size for a NUMBER column.  \n",
    "    # The second value is the maximum input data (or column) width for the VARCHAR2 column\n",
    "    cursor.setinputsizes(None, 7)\n",
    "\n",
    "    cursor.executemany(\"insert into mytab(id, data) values (:1, :2)\", rows)\n",
    "\n",
    "    # Now query the results back\n",
    "\n",
    "    for row in cursor.execute('select * from mytab'):\n",
    "        print(row)\n",
    "\n",
    "    connection.rollback()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Benchmark - executemany() vs execute()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import matplotlib.pyplot as plt\n",
    "import time\n",
    "\n",
    "cursor = connection.cursor()\n",
    "cursor.execute(\"truncate table mytab\")\n",
    "\n",
    "# Row counts to test inserting\n",
    "numrows = (1, 5, 10, 100, 1000)\n",
    "\n",
    "longstring = \"x\" * 1000\n",
    "\n",
    "def create_data(n):\n",
    "    d = []\n",
    "    for i in range(n):\n",
    "        d.append((i, longstring))\n",
    "    return d\n",
    "\n",
    "ex = []  # seconds for execute() loop\n",
    "em = []  # seconds for executemany()\n",
    "\n",
    "for n in numrows:\n",
    "    \n",
    "    rows = create_data(n)\n",
    "    \n",
    "    ############################################################\n",
    "    #\n",
    "    # Loop over each row\n",
    "    #\n",
    "\n",
    "    start=time.time()\n",
    "\n",
    "    for r in rows:\n",
    "        cursor.execute(\"insert into mytab(id, data) values (:1, :2)\", r)          # <==== Loop over execute()\n",
    "        \n",
    "    elapsed = time.time() - start\n",
    "    ex.append(elapsed)\n",
    "    \n",
    "    r, = cursor.execute(\"select count(*) from mytab\").fetchone()\n",
    "    print(\"execute() loop {:6d} rows in {:06.4f} seconds\".format(r, elapsed))    \n",
    "    connection.rollback()\n",
    "    \n",
    "    ############################################################# \n",
    "    #\n",
    "    # Insert all rows in one call\n",
    "    #\n",
    "\n",
    "    start = time.time()\n",
    "\n",
    "    cursor.executemany(\"insert into mytab(id, data) values (:1, :2)\", rows)       # <==== One executemany()\n",
    "    \n",
    "    elapsed = time.time() - start\n",
    "    em.append(elapsed)\n",
    "    \n",
    "    r, = cursor.execute(\"select count(*) from mytab\").fetchone()\n",
    "    print(\"executemany()  {:6d} rows in {:06.4f} seconds\".format(r, elapsed))  \n",
    "    connection.rollback()\n",
    "\n",
    "\n",
    "print(\"Plot is:\")\n",
    "plt.xticks(numrows)\n",
    "plt.plot(numrows, ex, label=\"execute() loop\", marker=\"o\")\n",
    "plt.plot(numrows, em, label=\"one executemany()\", marker=\"o\")\n",
    "plt.xscale(\"log\")\n",
    "plt.xlabel('Number of rows')\n",
    "plt.ylabel('Seconds')\n",
    "plt.legend(loc=\"upper left\")\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Noisy Data - Batch Errors\n",
    "\n",
    "Dealing with bad data is easy with the `batcherrors` parameter."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# Initial data\n",
    "\n",
    "with connection.cursor() as cursor:\n",
    "\n",
    "    for row in cursor.execute(\"select * from ParentTable order by ParentId\"):\n",
    "        print(row)\n",
    "\n",
    "    for row in cursor.execute(\"select * from ChildTable order by ChildId\"):\n",
    "        print(row)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dataToInsert = [\n",
    "    (1016, 10, 'Child Red'),\n",
    "    (1018, 20, 'Child Blue'),\n",
    "    (1018, 30, 'Child Green'),  # duplicate key\n",
    "    (1022, 40, 'Child Yellow'),\n",
    "    (1021, 75, 'Child Orange')  # parent does not exist\n",
    "]\n",
    "\n",
    "with connection.cursor() as cursor:\n",
    "    \n",
    "    cursor.executemany(\"insert into ChildTable values (:1, :2, :3)\", dataToInsert, batcherrors=True)\n",
    "       \n",
    "    print(\"\\nErrors in rows that were not inserted:\\n\")\n",
    "    for error in cursor.getbatcherrors():\n",
    "        print(\"Error\", error.message, \"at row offset\", error.offset)    \n",
    "        \n",
    "    print(\"\\nRows that were successfully inserted:\\n\")\n",
    "    for row in cursor.execute(\"select * from ChildTable order by ChildId\"):\n",
    "        print(row)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now you can choose whether or not to fix failed records and reinsert them.\n",
    "You can then rollback or commit.\n",
    "\n",
    "This is true even if you had enabled autocommit mode - no commit will occur if there are batch errors."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "connection.rollback()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}