{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "a4f0eb3f-f612-4277-821f-b0d3e61032c2",
   "metadata": {},
   "source": [
    "# Insert Rows into a Table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "adbb53dc-6fb1-4787-b372-5db5ccf69846",
   "metadata": {},
   "outputs": [],
   "source": [
    "from data201 import db_connection\n",
    "from employee import create_employee, print_employee"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fc14f5fa-ca60-44af-8ad6-07b20794e143",
   "metadata": {},
   "outputs": [],
   "source": [
    "conn = db_connection(config_file = 'personnel.ini')\n",
    "cursor = conn.cursor()\n",
    "\n",
    "create_employee(conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "63e3869b-3803-4438-839b-9c71ee6658e6",
   "metadata": {},
   "source": [
    "### Insert one row at a time\n",
    "#### Note that SQL syntax does not allow `_` in numbers."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "03d2546d-ba0c-40af-9aff-6180db3c9f17",
   "metadata": {},
   "outputs": [],
   "source": [
    "sql = ( \"\"\"\n",
    "        INSERT INTO employee\n",
    "        VALUES (123, 'Jones', 'Mary', 150000.00)\n",
    "        \"\"\"\n",
    "      )\n",
    "       \n",
    "cursor.execute(sql)\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "57b5ca07-3818-4451-8efc-fa7a7385f3f2",
   "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 (456, 'Smith',  'George', 100000.00),\n",
    "               (789, 'Brown',  'Leslie', 125000.00),\n",
    "               (941, 'Wilson', 'Sidney', 250000.00)\n",
    "        \"\"\"\n",
    "      ) \n",
    "\n",
    "cursor.execute(sql)\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "435df950-7b78-4169-9dfa-84439bac1d6c",
   "metadata": {},
   "source": [
    "#### An insertion requires a `conn.commit()` or it doesn’t actually happen."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ef8c567b-e11a-4a59-bc65-b7d29d8c7195",
   "metadata": {},
   "source": [
    "### Insert specific columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5b2b2de7-09dc-4c72-bfb4-879d42e4fbd3",
   "metadata": {},
   "outputs": [],
   "source": [
    "sql = ( \"\"\"\n",
    "        INSERT INTO employee (last, first, id)\n",
    "        VALUES ('Schmidt', 'Frank', 604)\n",
    "        \"\"\"\n",
    "      )\n",
    "    \n",
    "cursor.execute(sql)\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1c13096d-a9cb-456b-bb34-fa2e8b689ea0",
   "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": "3176c687-a024-408c-a965-17a52f986444",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Copyright (c) 2025 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.12.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}