{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "f869ad0c-f0e0-44b2-a85b-51cbaa048489",
   "metadata": {},
   "source": [
    "# Insert Rows with Tuples"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "adbb53dc-6fb1-4787-b372-5db5ccf69846",
   "metadata": {},
   "outputs": [],
   "source": [
    "from DATA225utils import make_connection\n",
    "from employee import reset_employee, print_employee"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fc14f5fa-ca60-44af-8ad6-07b20794e143",
   "metadata": {},
   "outputs": [],
   "source": [
    "conn = make_connection(config_file = 'personnel.ini')\n",
    "cursor = conn.cursor()\n",
    "\n",
    "reset_employee(conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5cf62ee2-3807-4e57-ade9-641651d247f4",
   "metadata": {},
   "source": [
    "### Insert one row at a time"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "03d2546d-ba0c-40af-9aff-6180db3c9f17",
   "metadata": {},
   "outputs": [],
   "source": [
    "sql = ( \"\"\"\n",
    "        INSERT INTO employee\n",
    "        VALUES (%s, %s, %s, %s)\n",
    "        \"\"\"\n",
    "      )\n",
    "\n",
    "data = (123, 'Jones', 'Mary', 150_000.00)  # tuple\n",
    "\n",
    "cursor.execute(sql, data)\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2ae177d6-3b4e-4da0-9e8b-e83073d8a343",
   "metadata": {},
   "source": [
    "#### SQL syntax does not allow `_` in numbers. However, the number `150_000.00` is in the Python code."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ae658bec-fcbb-427c-a68d-09c3303fc7fb",
   "metadata": {},
   "source": [
    "### Insert multiple rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fe4265a0-4e1f-448a-b2f7-02c5fde3f7b4",
   "metadata": {},
   "outputs": [],
   "source": [
    "sql = ( \"\"\"\n",
    "        INSERT INTO employee\n",
    "        VALUES (%s, %s, %s, %s)\n",
    "        \"\"\"\n",
    "      ) \n",
    "\n",
    "data = [ (456, 'Smith',  'George', 100_000.00), \n",
    "         (789, 'Brown',  'Leslie', 125_000.00), \n",
    "         (941, 'Wilson', 'Sidney', 250_000.00)\n",
    "       ]\n",
    "\n",
    "cursor.executemany(sql, data)  # NOTE: executemany()\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4e2bc0bc-ac54-4124-b4f2-1506fd52b92c",
   "metadata": {},
   "source": [
    "### Insert specific columns\n",
    "\n",
    "#### You can also insert with lists."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5b2b2de7-09dc-4c72-bfb4-879d42e4fbd3",
   "metadata": {},
   "outputs": [],
   "source": [
    "sql = ( \"\"\"\n",
    "        INSERT INTO employee (first, last, id)\n",
    "        VALUES (%s, %s, %s)\n",
    "        \"\"\"\n",
    "      ) \n",
    "\n",
    "data = ['Frank', 'Schmidt', 604]  # list\n",
    "\n",
    "cursor.execute(sql, data)\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7c6d0367-1842-4855-b4ee-e16ee4727ed7",
   "metadata": {},
   "outputs": [],
   "source": [
    "print_employee(conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8a775d05-1f88-46fd-8f68-b74d5d3739b3",
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.close()\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "def1257d-d89e-4f9c-a751-64eeff746e9c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Copyright (c) 2023 by Ronald Mak"
   ]
  }
 ],
 "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.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}