{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "ddecbcc3-cee4-41ff-905e-cb8042483b75",
   "metadata": {},
   "source": [
    "# Insert Rows with Dictionaries"
   ]
  },
  {
   "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": "05536eb8-a11e-48c3-9981-a18584770499",
   "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 (%(id)s, %(last)s, %(first)s, %(salary)s)\n",
    "        \"\"\"\n",
    "      ) \n",
    "\n",
    "data = {\n",
    "    'id':     123,\n",
    "    'first':  'Mary',\n",
    "    'last':   'Jones',\n",
    "    'salary': 150_000.00\n",
    "}\n",
    "cursor.execute(sql, data)\n",
    "conn.commit()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "375096fe-f25c-459d-a376-6f4ac2ea027e",
   "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 (%(id)s, %(last)s, %(first)s, %(salary)s)\n",
    "        \"\"\"\n",
    "      ) \n",
    "\n",
    "data = [  # list of dictionaries\n",
    "    {\n",
    "        'id':     456,\n",
    "        'first':  'George',\n",
    "        'last':   'Smith',\n",
    "        'salary': 100_000.00\n",
    "    },\n",
    "    {\n",
    "        'id':     789,\n",
    "        'first':  'Leslie',\n",
    "        'last':   'Brown',\n",
    "        'salary': 125_000.00\n",
    "    },\n",
    "\n",
    "    {\n",
    "        'id':     941,\n",
    "        'first':  'Sidney',\n",
    "        'last':   'Wilson',\n",
    "        'salary': 250_000.00\n",
    "    }\n",
    "]\n",
    "\n",
    "cursor.executemany(sql, data)  # NOTE: executemany()\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "604f1587-5bfb-4709-bedb-e434f34e29fd",
   "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 (first, last, id)\n",
    "        VALUES (%(first)s, %(last)s, %(id)s)\n",
    "        \"\"\"\n",
    "      ) \n",
    "\n",
    "data = {\n",
    "    'id':     604,\n",
    "    'first':  'Frank',\n",
    "    'last':   'Schmidt',\n",
    "    'salary': 50_000\n",
    "}\n",
    "    \n",
    "cursor.execute(sql, data)\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "723b3bf2-ea97-462e-befd-5cb15c9a2989",
   "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": []
  }
 ],
 "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
}