{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Transacciones ACID (Insert/ Update / Delete) en SQLite3 --- 6:46 min\n",
    "===\n",
    "\n",
    "* 6:46 min | Última modificación: Octubre 14, 2021 | [YouTube](https://youtu.be/TaDZcKq1E5Y)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "El lenguaje SQL estándar provee directivas para la insertar, actualizar y borrar registros en una tabla. En este tutorial se presentan ejemplos representativos de estas instrucciones en SQLlite3. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "#\n",
    "# Apertura de la conexión\n",
    "#\n",
    "import sqlite3\n",
    "\n",
    "conn = sqlite3.connect(\":memory:\")  ## aca se indica el nombre de la db.\n",
    "cur = conn.cursor()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Creación de la tabla"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur.execute(\n",
    "    \"\"\"\n",
    "CREATE TABLE persons (\n",
    "    id INT,\n",
    "    firstname VARCHAR(10),\n",
    "    surname VARCHAR(10),\n",
    "    birthday DATE,\n",
    "    color VARCHAR(10),\n",
    "    quantity INT\n",
    ");\n",
    "\"\"\"\n",
    ").fetchall()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## INSERT\n",
    "\n",
    "    INSERT [INTO] tbl_name [(column_name,...)]\n",
    "    {VALUES | VALUE}\n",
    "    ({expression|DEFAULT},...)[,(...),...];"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(1, 'Vivian', 'Hamilton', '1971-07-08', 'green', 1)]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#\n",
    "# Inserta el registro en la tabla.\n",
    "# Los valores están en el mismo orden de los campos.\n",
    "#\n",
    "cur.execute(\n",
    "    \"\"\"\n",
    "INSERT INTO \n",
    "    persons \n",
    "VALUES \n",
    "    (1,\"Vivian\",\"Hamilton\",\"1971-07-08\",\"green\",1);\n",
    "\"\"\"\n",
    ").fetchall()\n",
    "\n",
    "cur.execute(\"SELECT * FROM persons;\").fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(1, 'Vivian', 'Hamilton', '1971-07-08', 'green', 1),\n",
       " (2, 'Karen', 'Holcomb', '1974-05-23', 'green', 4),\n",
       " (3, 'Cody', 'Garrett', '1973-04-22', 'orange', 1)]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#\n",
    "# Inserta varios registros a la vez.\n",
    "# Los valores deben estar en el mismo orden de los campos.\n",
    "#\n",
    "cur.execute(\n",
    "    \"\"\"\n",
    "INSERT INTO \n",
    "    persons \n",
    "VALUES\n",
    "    (2,\"Karen\",\"Holcomb\",\"1974-05-23\",\"green\",4),\n",
    "    (3,\"Cody\",\"Garrett\",\"1973-04-22\",\"orange\",1);\n",
    "\"\"\"\n",
    ").fetchall()\n",
    "\n",
    "cur.execute(\"SELECT * FROM persons;\").fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(1, 'Vivian', 'Hamilton', '1971-07-08', 'green', 1),\n",
       " (2, 'Karen', 'Holcomb', '1974-05-23', 'green', 4),\n",
       " (3, 'Cody', 'Garrett', '1973-04-22', 'orange', 1),\n",
       " (None, 'Vivian', 'Kinney', None, 'violet', None),\n",
       " (None, 'Hope', 'Kingston', None, 'blue', None),\n",
       " (None, 'Kylan', 'Bigelow', None, 'red', None)]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#\n",
    "# Inserta registros con información parcial.\n",
    "# Se indica en cuales campos se inserta la información.\n",
    "#\n",
    "cur.execute(\n",
    "    \"\"\"\n",
    "INSERT INTO persons (color,firstname,surname) VALUES\n",
    "    (\"violet\",\"Vivian\", \"Kinney\"),\n",
    "    (\"blue\",\"Hope\",\"Kingston\"),\n",
    "    (\"red\",\"Kylan\",\"Bigelow\");\n",
    "\"\"\"\n",
    ").fetchall()\n",
    "\n",
    "cur.execute(\"SELECT * FROM persons;\").fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(1, 'Vivian', 'Hamilton', '1971-07-08', 'green', 1),\n",
       " (2, 'Karen', 'Holcomb', '1974-05-23', 'green', 4),\n",
       " (3, 'Cody', 'Garrett', '1973-04-22', 'orange', 1),\n",
       " (None, 'Vivian', 'Kinney', None, 'violet', None),\n",
       " (None, 'Hope', 'Kingston', None, 'blue', None),\n",
       " (None, 'Kylan', 'Bigelow', None, 'red', None),\n",
       " (None, 'Camil', None, None, None, None)]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#\n",
    "# Inserta un registro con información en un solo campo.\n",
    "# Se indica en cual campo se inserta la información\n",
    "#\n",
    "cur.execute(\n",
    "    \"\"\"\n",
    "INSERT INTO \n",
    "    persons (firstname) \n",
    "VALUES \n",
    "    (\"Camil\");\n",
    "\"\"\"\n",
    ").fetchall()\n",
    "\n",
    "cur.execute(\"SELECT * FROM persons;\").fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(1, 'Vivian', 'Hamilton', '1971-07-08', 'green', 1),\n",
       " (2, 'Karen', 'Holcomb', '1974-05-23', 'green', 4),\n",
       " (3, 'Cody', 'Garrett', '1973-04-22', 'orange', 1),\n",
       " (None, 'Vivian', 'Kinney', None, 'violet', None),\n",
       " (None, 'Hope', 'Kingston', None, 'blue', None),\n",
       " (None, 'Kylan', 'Bigelow', None, 'red', None),\n",
       " (None, 'Camil', None, None, None, None),\n",
       " (None, 'Kate', 'Bond', '1974-05-23', None, None)]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#\n",
    "# Se inserta información en un registro.\n",
    "# No se indican los nombres de los campos.\n",
    "# Se usa NULL para los campos que no tienen información.\n",
    "#\n",
    "cur.execute(\n",
    "    \"\"\"\n",
    "INSERT INTO \n",
    "    persons \n",
    "VALUES\n",
    "    (NULL,\"Kate\",\"Bond\",\"1974-05-23\",NULL,NULL);\n",
    "\"\"\"\n",
    ").fetchall()\n",
    "\n",
    "cur.execute(\"SELECT * FROM persons;\").fetchall()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## UPDATE\n",
    "\n",
    "     UPDATE tbl_name\n",
    "     (SET col_name={expression|DEFAULT}, ...)\n",
    "     [WHERE where_conditions];\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(1, 'Vivian', 'Hamilton', '1971-07-08', 'green', 1),\n",
       " (2, 'Karen', 'Holcomb', '1974-05-23', 'green', 4),\n",
       " (3, 'Cody', 'Garrett', '1973-04-22', 'orange', 1),\n",
       " (None, 'Vivian', 'Kinney', None, 'violet', None),\n",
       " (None, 'Hope', 'Kingston', None, 'blue', None),\n",
       " (None, 'Kylan', 'Bigelow', None, 'red', None),\n",
       " (None, 'Camil', None, None, None, None),\n",
       " (8, 'Kate', 'Bond', '1974-05-23', 'gray', None)]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#\n",
    "# Se actualiza la información de los campos de registros existentes.\n",
    "# Se indica explictamente el valor de cada campo.\n",
    "# Si no se introduce la clausura WHERE se cambian todos los\n",
    "# los registros de la tabla.\n",
    "#\n",
    "#\n",
    "# Se inserta información en un registro.\n",
    "# No se indican los nombres de los campos.\n",
    "# Se usa NULL para los campos que no tienen información.\n",
    "#\n",
    "cur.execute(\n",
    "    \"\"\"\n",
    "UPDATE persons SET\n",
    "    id = 8, \n",
    "    color = \"gray\"\n",
    "WHERE \n",
    "    firstname = \"Kate\";\n",
    "\"\"\"\n",
    ").fetchall()\n",
    "\n",
    "cur.execute(\"SELECT * FROM persons;\").fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(1, 'Vivian', 'Hamilton', '1971-07-08', 'green', 1),\n",
       " (2, 'Karen', 'Holcomb', '1974-05-23', 'green', 4),\n",
       " (3, 'Cody', 'Garrett', '1973-04-22', 'orange', 1),\n",
       " (None, 'Vivian', 'Kinney', None, 'violet', None),\n",
       " (None, 'Hope', 'Kingston', None, 'blue', None),\n",
       " (None, 'Kylan', 'Bigelow', None, 'red', None),\n",
       " (None, 'Camil', None, None, None, None),\n",
       " (8, 'Kate', 'Bond', '1974-05-23', 'black', None)]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#\n",
    "# Se actualiza el campo con base en el indicador.\n",
    "#\n",
    "cur.execute(\n",
    "    \"\"\"\n",
    "UPDATE \n",
    "    persons \n",
    "SET \n",
    "   color = \"black\" \n",
    "WHERE \n",
    "   id = 8;\n",
    "\"\"\"\n",
    ").fetchall()\n",
    "\n",
    "cur.execute(\"SELECT * FROM persons;\").fetchall()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## DELETE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(3, 'Cody', 'Garrett', '1973-04-22', 'orange', 1),\n",
       " (None, 'Vivian', 'Kinney', None, 'violet', None),\n",
       " (None, 'Hope', 'Kingston', None, 'blue', None),\n",
       " (None, 'Kylan', 'Bigelow', None, 'red', None),\n",
       " (None, 'Camil', None, None, None, None),\n",
       " (8, 'Kate', 'Bond', '1974-05-23', 'black', None)]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur.execute(\n",
    "    \"\"\"\n",
    "DELETE FROM \n",
    "    persons \n",
    "WHERE \n",
    "   color = 'green';\n",
    "\"\"\"\n",
    ").fetchall()\n",
    "\n",
    "cur.execute(\"SELECT * FROM persons;\").fetchall()"
   ]
  }
 ],
 "metadata": {
  "kernel_info": {
   "name": "python3"
  },
  "kernelspec": {
   "display_name": "Python 3",
   "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.6.9"
  },
  "nteract": {
   "version": "0.12.3"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": false,
   "sideBar": true,
   "skip_h1_title": true,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": true
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}