{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Transacciones y UPSERT, la red de seguridad\n",
    "\n",
    "BEGIN, ROLLBACK y \"insértalo, y si ya está, actualízalo\", que en los cuatro motores se escribe de tres formas distintas.\n",
    "\n",
    "Cuaderno de práctica del capítulo 12 de **SQL desde cero**, de Miss Yera.\n",
    "\n",
    "Corre de arriba abajo. Si lo abres en Google Colab no necesitas instalar nada.\n",
    "\n",
    "Capítulo completo: https://missyera.com/guias/sql-desde-cero/upsert-y-transacciones/\n",
    "\n",
    "Los ejercicios están al final y traen una celda vacía debajo de cada uno. Las\n",
    "respuestas viven en el cuaderno de soluciones, y merece la pena pelearse un\n",
    "rato antes de abrirlo 💛"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Antes de empezar\n",
    "\n",
    "Se baja la base y se deja lista una función `q()` que corre las consultas."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "import urllib.request\n",
    "\n",
    "import pandas as pd\n",
    "\n",
    "urllib.request.urlretrieve(\"https://missyera.com/static/datasets/tienda.db\", \"tienda.db\")\n",
    "con = sqlite3.connect(\"tienda.db\")\n",
    "\n",
    "def q(sql):\n",
    "    \"\"\"Corre las sentencias del bloque y devuelve la ultima como tabla.\n",
    "\n",
    "    Parte por sentencias igual que la consola de SQLite, porque un bloque puede\n",
    "    traer varias y un CREATE TRIGGER lleva punto y coma dentro de su cuerpo.\n",
    "    \"\"\"\n",
    "    resultado = None\n",
    "    trozo = \"\"\n",
    "    for linea in sql.splitlines(keepends=True):\n",
    "        trozo += linea\n",
    "        if sqlite3.complete_statement(trozo):\n",
    "            if trozo.strip():\n",
    "                cur = con.execute(trozo.strip())\n",
    "                resultado = (pd.DataFrame(cur.fetchall(),\n",
    "                                          columns=[d[0] for d in cur.description])\n",
    "                             if cur.description else None)\n",
    "            trozo = \"\"\n",
    "    if trozo.strip():\n",
    "        cur = con.execute(trozo.strip())\n",
    "        resultado = (pd.DataFrame(cur.fetchall(),\n",
    "                                  columns=[d[0] for d in cur.description])\n",
    "                     if cur.description else None)\n",
    "    con.commit()\n",
    "    return resultado\n",
    "\n",
    "q(\"SELECT name FROM sqlite_master WHERE type = 'table' ORDER BY name\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Antes de empezar\n",
    "\n",
    "Esta celda baja el ayudante que corrige tus ejercicios. Después, en cada\n",
    "ejercicio que se pueda corregir solo, vas a ver `%%revisa` arriba de la celda:\n",
    "escribe tu respuesta debajo, ejecuta, y te digo si te salió 💛"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import urllib.request\n",
    "\n",
    "# El ayudante de los cuadernos. Trae la corrección de los ejercicios y, en los\n",
    "# capítulos de consola, la celda mágica que ejecuta los comandos. Se baja en\n",
    "# vez de venir pegado aquí para que siempre sea el último.\n",
    "urllib.request.urlretrieve(\n",
    "    \"https://missyera.com/static/cuadernos/revisa.py\", \"revisa.py\")\n",
    "import revisa\n",
    "revisa.carga({\n",
    "    1: \"aWRfY2xpZW50ZSAgc2FsZG8KLS0tLS0tLS0tLSAgLS0tLS0KMSAgICAgICAgICAgMzEwLjAKMiAgICAgICAgICAgNTAwLjA=\",\n",
    "    2: \"c2VnbWVudG8gICAgbWV0YSAgICAgICB2ZWNlcwotLS0tLS0tLS0tICAtLS0tLS0tLS0gIC0tLS0tCkhvcmVjYSAgICAgIDE3NDY4Ny40NiAgMQpCb2RlZ2EgICAgICAxNjY1NjUuOTEgIDEKTWF5b3Jpc3RhICAgMTMzNDU2LjkxICAxCk1pbmltYXJrZXQgIDEyMDM0OC42MyAgMQ==\",\n",
    "    3: \"c2VnbWVudG8gICAgbWV0YSAgICAgICB2ZWNlcwotLS0tLS0tLS0tICAtLS0tLS0tLS0gIC0tLS0tCkhvcmVjYSAgICAgIDE3NDY4Ny40NiAgMgpCb2RlZ2EgICAgICAxNjY1NjUuOTEgIDIKTWF5b3Jpc3RhICAgMTMzNDU2LjkxICAyCk1pbmltYXJrZXQgIDEyMDM0OC42MyAgMg==\",\n",
    "    4: \"c2VnbWVudG8gICAgbWV0YSAgICAgICB2ZWNlcwotLS0tLS0tLS0tICAtLS0tLS0tLS0gIC0tLS0tCkJvZGVnYSAgICAgIDE2NjU2NS45MSAgMgpIb3JlY2EgICAgICAxMDAwLjAgICAgIDEKTWF5b3Jpc3RhICAgMTMzNDU2LjkxICAyCk1pbmltYXJrZXQgIDEyMDM0OC42MyAgMg==\",\n",
    "    5: \"ZmlsYXMKLS0tLS0KNA==\",\n",
    "    6: \"Y2FuYWwgICAgIHZlY2VzCi0tLS0tLS0tICAtLS0tLQpXZWIgICAgICAgMgpXaGF0c0FwcCAgMQ==\",\n",
    "}, lenguaje=\"sql\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "En el capítulo 10 apagué la tabla entera con un `UPDATE` sin\n",
    "`WHERE`. Se arregló porque eran cinco filas de mentira.\n",
    "\n",
    "Este capítulo es la red que hace que eso se pueda deshacer de verdad, y de\n",
    "paso resuelve la otra pregunta que aparece el primer día que cargas datos:\n",
    "*¿y si la fila ya está?*\n",
    "\n",
    "Y una más, que es la que de verdad separa a quien carga datos de quien los cuida: **¿cómo sabes que se cargó todo lo que tenías que cargar?** Si la respuesta es \"porque no dio error\", este capítulo es para ti 🧾"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Una transacción es un \"todo o nada\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Vamos con el ejemplo de siempre porque es el que se entiende sin explicar\n",
    "nada: mover plata de una cuenta a otra."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "CREATE TABLE saldos (\n",
    "    id_cliente INTEGER PRIMARY KEY,\n",
    "    saldo      REAL NOT NULL DEFAULT 0\n",
    ");\n",
    "INSERT INTO saldos (id_cliente, saldo) VALUES (1, 500.00), (2, 300.00);\n",
    "SELECT * FROM saldos ORDER BY id_cliente;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Pasar 200 del cliente 1 al 2 son dos `UPDATE`. Y el problema está\n",
    "en el medio: si entre el primero y el segundo se cae la luz, se cortó la red o\n",
    "alguien mató el proceso, el cliente 1 se quedó sin sus 200 y el 2 nunca los\n",
    "recibió. La plata se evaporó 😳"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "BEGIN;\n",
    "UPDATE saldos SET saldo = saldo - 200 WHERE id_cliente = 1;\n",
    "UPDATE saldos SET saldo = saldo + 200 WHERE id_cliente = 2;\n",
    "COMMIT;\n",
    "SELECT * FROM saldos ORDER BY id_cliente;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`BEGIN` abre la transacción, `COMMIT` la cierra y la\n",
    "hace real. Entre esas dos líneas, **o pasa todo o no pasa nada**.\n",
    "Si el proceso se muere en el medio, la base deshace lo que llevaba y las cuentas\n",
    "quedan como estaban.\n",
    "\n",
    "Y aquí está lo que de verdad te va a salvar el pellejo."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "BEGIN;\n",
    "UPDATE saldos SET saldo = saldo - 300 WHERE id_cliente = 1;\n",
    "UPDATE saldos SET saldo = 0;\n",
    "ROLLBACK;\n",
    "SELECT * FROM saldos ORDER BY id_cliente;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ese segundo `UPDATE` es el desastre del capítulo 10: sin\n",
    "`WHERE`, saldo cero para todo el mundo. Y el\n",
    "`ROLLBACK` lo deshizo. Los saldos siguen en 300 y 500 como si nada\n",
    "hubiera pasado 🙌\n",
    "\n",
    "**Esa es la costumbre que quiero que te lleves de este capítulo:\n",
    "`BEGIN`, haces tu cambio, miras con un `SELECT`, y recién\n",
    "ahí `COMMIT` o `ROLLBACK`.** Es un segundo más y\n",
    "convierte cualquier metida de pata en un susto."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Abrir una transacción\n",
    "\n",
    "| PostgreSQL | `BEGIN;   -- también START TRANSACTION;` |\n",
    "|---|---|\n",
    "| MySQL | `START TRANSACTION;   -- también BEGIN;` |\n",
    "| SQL Server | `BEGIN TRANSACTION;   -- o BEGIN TRAN;` |\n",
    "| SQLite | `BEGIN;   -- también BEGIN TRANSACTION;` |\n",
    "\n",
    "COMMIT y ROLLBACK sí se llaman igual en los cuatro. Lo que cambia es cómo se abre, y SQL Server es el único que necesita la palabra TRANSACTION."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Deshacer solo un pedacito: SAVEPOINT"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "BEGIN;\n",
    "UPDATE saldos SET saldo = saldo + 10 WHERE id_cliente = 1;\n",
    "SAVEPOINT antes_del_lio;\n",
    "UPDATE saldos SET saldo = 999999 WHERE id_cliente = 2;\n",
    "ROLLBACK TO antes_del_lio;\n",
    "COMMIT;\n",
    "SELECT * FROM saldos ORDER BY id_cliente;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "El primer `UPDATE` se quedó (el cliente 1 pasó de 300 a 310) y el\n",
    "segundo se deshizo. Un `SAVEPOINT` es una marca a la que puedes\n",
    "volver sin tirar toda la transacción, y sirve muchísimo en cargas largas: si el\n",
    "paso 7 de 10 falla, vuelves al 6 y sigues, en vez de empezar de cero.\n",
    "\n",
    "Está en los cuatro motores con el mismo nombre."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Lo que no se puede deshacer"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Aquí hay una diferencia entre motores que muerde fuerte y que casi nadie\n",
    "avisa."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "¿Un CREATE TABLE dentro de una transacción se puede deshacer?\n",
    "\n",
    "| PostgreSQL | `sí, el DDL es transaccional como todo lo demás` |\n",
    "|---|---|\n",
    "| MySQL | `NO: cualquier CREATE, ALTER o DROP confirma la transacción abierta sin avisar` |\n",
    "| SQL Server | `sí` |\n",
    "| SQLite | `sí` |\n",
    "\n",
    "Lo de MySQL se llama commit implícito y es de las cosas que más caro salen: crees que estás dentro de un BEGIN, metes un ALTER TABLE en el medio, y todo lo anterior quedó confirmado. Tu ROLLBACK ya no deshace nada y no te lo dijo nadie."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Y una que vale para los cuatro: **por defecto, cada sentencia suelta es\n",
    "su propia transacción**. Eso se llama autocommit, y significa que un\n",
    "`DELETE` sin `BEGIN` está confirmado en el momento en que\n",
    "termina. No hay nada que deshacer."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Insertar algo que a lo mejor ya está"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Cambio de tema, mismo capítulo, porque las dos cosas van juntas en la vida\n",
    "real: cargar datos.\n",
    "\n",
    "Tienes una tabla de metas por canal y cada mes te llega el archivo nuevo. La\n",
    "mitad de los canales ya están y hay que actualizarlos; la otra mitad son nuevos y\n",
    "hay que insertarlos. Eso es un **upsert**: update si está, insert si\n",
    "no."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "CREATE TABLE metas (\n",
    "    canal       TEXT PRIMARY KEY,\n",
    "    meta        REAL NOT NULL,\n",
    "    actualizada TEXT NOT NULL DEFAULT '2026-01-01'\n",
    ");\n",
    "INSERT INTO metas (canal, meta) VALUES ('Web', 150000), ('WhatsApp', 140000);\n",
    "SELECT * FROM metas ORDER BY canal;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Si intentas insertar Web otra vez, ya sabes lo que pasa."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Esto revienta a propósito.** Se ejecuta dentro de un `try` para que puedas seguir con \"ejecutar todo\" y aun así ver la queja."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "try:\n",
    "    q(\"\"\"\n",
    "    INSERT INTO metas (canal, meta) VALUES ('Web', 999999);\n",
    "    \"\"\")\n",
    "except Exception as e:\n",
    "    print(f'{type(e).__name__}: {e}')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Y la queja que tiene que salir es esta:\n",
    "\n",
    "```\n",
    "IntegrityError: UNIQUE constraint failed: metas.canal\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "La forma que sale sola es `DELETE` y después\n",
    "`INSERT`, y es la forma mala: entre las dos sentencias la fila no\n",
    "existe, así que si algo lee justo ahí, ve un hueco. Y si el\n",
    "`INSERT` falla, borraste el dato bueno."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "INSERT INTO metas (canal, meta) VALUES ('Web', 160000)\n",
    "ON CONFLICT (canal) DO UPDATE SET meta = excluded.meta, actualizada = '2026-06-24';\n",
    "SELECT * FROM metas ORDER BY canal;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Se lee tal cual: \"insértalo, y si choca con la clave `canal`,\n",
    "entonces actualiza\". Ese `excluded` es la palabra clave: es\n",
    "**la fila que querías insertar**, la que se quedó fuera. Así puedes\n",
    "decir \"ponle el valor nuevo\" sin repetirlo."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "INSERT INTO metas (canal, meta) VALUES ('Tienda', 120000)\n",
    "ON CONFLICT (canal) DO UPDATE SET meta = excluded.meta, actualizada = '2026-06-24';\n",
    "SELECT * FROM metas ORDER BY canal;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "La misma sentencia, sin tocar una letra, insertó Tienda porque no existía. Y\n",
    "fíjate en su `actualizada`: se quedó en 2026-01-01, el\n",
    "`DEFAULT`, porque el `DO UPDATE` no se ejecutó. Esa\n",
    "columna te dice de un vistazo cuáles se actualizaron y cuáles nacieron 🔍"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Insertar y, si ya está, actualizar\n",
    "\n",
    "| PostgreSQL | `INSERT ... ON CONFLICT (canal) DO UPDATE SET meta = EXCLUDED.meta` |\n",
    "|---|---|\n",
    "| MySQL | `INSERT ... ON DUPLICATE KEY UPDATE meta = VALUES(meta)` |\n",
    "| SQL Server | `MERGE INTO ... WHEN MATCHED THEN UPDATE ... WHEN NOT MATCHED THEN INSERT ...` |\n",
    "| SQLite | `INSERT ... ON CONFLICT (canal) DO UPDATE SET meta = excluded.meta` |\n",
    "\n",
    "PostgreSQL y SQLite se escriben idéntico, MySQL cambia las palabras y no te deja elegir qué clave, y SQL Server te obliga a un MERGE de seis líneas que además arrastra fama de tener rarezas. Si tu consulta tiene que correr en los cuatro, el upsert es de las primeras cosas que se rompe."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Y un aviso sobre el `VALUES(meta)` de MySQL: está marcado como\n",
    "obsoleto desde la 8.0.20, y lo nuevo es ponerle alias a la fila y escribir\n",
    "`AS nueva ... ON DUPLICATE KEY UPDATE meta = nueva.meta`. Si copias\n",
    "código viejo de internet te va a salir un aviso 🫠"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Las otras dos formas de SQLite, y una que muerde"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "INSERT OR IGNORE INTO metas (canal, meta) VALUES ('Web', 1);\n",
    "SELECT * FROM metas ORDER BY canal;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`INSERT OR IGNORE` es \"si choca, no hagas nada\". Web se quedó en\n",
    "160000 y la sentencia no dio error. Sirve para cargar sin duplicar cuando lo que\n",
    "ya está es lo bueno."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "INSERT OR REPLACE INTO metas (canal, meta) VALUES ('Web', 170000);\n",
    "SELECT * FROM metas ORDER BY canal;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ahora mira bien la columna `actualizada` de Web. Estaba en\n",
    "2026-06-24 y volvió a 2026-01-01 😳\n",
    "\n",
    "Porque `INSERT OR REPLACE` **no actualiza: borra la fila\n",
    "entera y mete una nueva**. Todo lo que no nombraste vuelve a su\n",
    "`DEFAULT`, y si la columna no tuviera `DEFAULT` quedaría\n",
    "en `NULL`. Y lo peor: si otra tabla apuntaba a esa fila con una clave\n",
    "foránea en cascada, el borrado se lleva por delante lo de allá.\n",
    "\n",
    "Es de esas cosas que funcionan durante meses y un día te dejan sin datos. Si\n",
    "lo que quieres es actualizar, usa `ON CONFLICT DO UPDATE` y nombra\n",
    "las columnas 💛"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## El upsert que calcula"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "INSERT INTO metas (canal, meta) VALUES ('WhatsApp', 5000)\n",
    "ON CONFLICT (canal) DO UPDATE SET meta = metas.meta + excluded.meta;\n",
    "SELECT * FROM metas ORDER BY canal;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "WhatsApp pasó de 140.000 a 145.000: no lo reemplazó, le sumó. En el\n",
    "`DO UPDATE` tienes las dos filas a mano, `metas.meta` es\n",
    "la vieja y `excluded.meta` es la nueva, y puedes hacer con ellas lo\n",
    "que quieras.\n",
    "\n",
    "Así se llevan los acumulados sin leer primero: es una sola ida a la base en\n",
    "vez de un `SELECT`, una cuenta y un `UPDATE`. Menos\n",
    "código y, sobre todo, sin el hueco de tiempo en el que otro puede meterse.\n",
    "\n",
    "Y el upsert también funciona con un `SELECT` en vez de\n",
    "`VALUES`, que es como se cargan tablas enteras."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "INSERT INTO metas (canal, meta)\n",
    "SELECT canal, ROUND(SUM(monto) * 1.1, 2) FROM pedidos GROUP BY canal\n",
    "ON CONFLICT (canal) DO UPDATE SET meta = excluded.meta, actualizada = '2026-06-24';\n",
    "SELECT * FROM metas ORDER BY canal;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Las metas del año que viene, calculadas como la venta real más un 10%, en una\n",
    "sola sentencia. Los tres canales que ya estaban se actualizaron y Marketplace\n",
    "entró nuevo, y se distingue por la fecha.\n",
    "\n",
    "Esta es de las consultas que más se parecen a un trabajo de verdad de todo el\n",
    "libro 🌟"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Insertar y, si ya está, no hacer nada\n",
    "\n",
    "| PostgreSQL | `INSERT ... ON CONFLICT DO NOTHING` |\n",
    "|---|---|\n",
    "| MySQL | `INSERT IGNORE INTO ...` |\n",
    "| SQL Server | `no lo tiene: se resuelve con MERGE o con IF NOT EXISTS` |\n",
    "| SQLite | `INSERT OR IGNORE ...   -- y también ON CONFLICT DO NOTHING` |\n",
    "\n",
    "Cuidado con el INSERT IGNORE de MySQL, que es más ancho de lo que parece: se traga también otros errores, como un texto demasiado largo o una fecha inválida, y los convierte en avisos. Lo que querías ignorar era el duplicado, no todo."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Y el `MERGE` de SQL Server, para que veas que no está en los\n",
    "otros."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Esto revienta a propósito.** Se ejecuta dentro de un `try` para que puedas seguir con \"ejecutar todo\" y aun así ver la queja."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "try:\n",
    "    q(\"\"\"\n",
    "    MERGE INTO metas AS t USING (SELECT 'Web' AS canal) AS s ON t.canal = s.canal\n",
    "    WHEN MATCHED THEN UPDATE SET meta = 1;\n",
    "    \"\"\")\n",
    "except Exception as e:\n",
    "    print(f'{type(e).__name__}: {e}')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Y la queja que tiene que salir es esta:\n",
    "\n",
    "```\n",
    "OperationalError: near \"MERGE\": syntax error\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`MERGE` es del estándar y lo tienen SQL Server y PostgreSQL desde\n",
    "la 15. MySQL y SQLite no. Es más potente que el upsert (puede insertar,\n",
    "actualizar y borrar en la misma sentencia) y también bastante más difícil de\n",
    "leer."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Cuando dos personas escriben a la vez"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Todo lo de arriba asume que estás tú sola. En una base de verdad hay veinte\n",
    "procesos escribiendo, y ahí es donde las transacciones dejan de ser una\n",
    "comodidad y pasan a ser lo que sostiene todo.\n",
    "\n",
    "El nombre elegante es **aislamiento**: cuánto se enteran unas\n",
    "transacciones de lo que están haciendo las otras mientras van a medias."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "El nivel de aislamiento por defecto\n",
    "\n",
    "| PostgreSQL | `READ COMMITTED` |\n",
    "|---|---|\n",
    "| MySQL | `REPEATABLE READ` |\n",
    "| SQL Server | `READ COMMITTED` |\n",
    "| SQLite | `SERIALIZABLE, porque solo deja escribir a uno a la vez` |\n",
    "\n",
    "READ COMMITTED significa que solo ves lo que otros ya confirmaron. REPEATABLE READ, el de MySQL, además te garantiza que si lees dos veces lo mismo dentro de la transacción te sale igual. Que el default no sea el mismo explica por qué el mismo código se comporta distinto al cambiar de motor, y es de las cosas más difíciles de depurar que hay."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "¿Qué se bloquea mientras escribes?\n",
    "\n",
    "| PostgreSQL | `la fila` |\n",
    "|---|---|\n",
    "| MySQL | `la fila, con InnoDB` |\n",
    "| SQL Server | `la fila, la página o la tabla, según lo que decida el motor` |\n",
    "| SQLite | `la BASE ENTERA: el que llega segundo recibe \"database is locked\"` |\n",
    "\n",
    "Esto es lo que hace que SQLite sea perfecta para aprender, para una app de escritorio o para un archivo de análisis, y mala idea para una web con gente escribiendo a la vez. No es un defecto, es la decisión de diseño que la hace caber en un archivo."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Ejercicios"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Siete, y estos escriben. Sobre tu copia 💛"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1. El UPDATE que se deshace\n",
    "\n",
    "Sube todos los saldos un 10% y déjalos como estaban."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%revisa 1\n",
    "-- tu turno"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2. Un upsert de metas por segmento\n",
    "\n",
    "Crea una tabla de metas por segmento y llénala con la venta\n",
    "real de cada uno más un 15%, de forma que se pueda volver a correr sin\n",
    "duplicar."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%revisa 2\n",
    "-- tu turno"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3. La misma carga, otra vez\n",
    "\n",
    "Corre exactamente la misma sentencia del ejercicio 2 y mira\n",
    "qué cambia."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%revisa 3\n",
    "-- tu turno"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4. El REPLACE que borra sin querer\n",
    "\n",
    "Comprueba en tu tabla de metas por segmento que\n",
    "`INSERT OR REPLACE` se lleva puesta la columna que no nombraste."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%revisa 4\n",
    "-- tu turno"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 5. La carga que falla a la mitad\n",
    "\n",
    "Inserta un segmento nuevo dentro de una transacción,\n",
    "deshazla, y comprueba cuántas filas quedaron."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%revisa 5\n",
    "-- tu turno"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 6. Un contador que no se lee antes\n",
    "\n",
    "Lleva la cuenta de cuántas veces se ha visto cada canal,\n",
    "sumando de a uno, sin hacer un `SELECT` previo."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%revisa 6\n",
    "-- tu turno"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 7. Escríbelo para los cuatro\n",
    "\n",
    "Sin ejecutar: subir la meta de Web a 200.000, insertándola\n",
    "si no existiera, en los cuatro motores."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# tu turno"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## La fila que se perdió en silencio"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ya sabes lo que hace cada forma de insertar. Ahora mira lo que pasa cuando eliges la que no revienta por si acaso 🤫"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### La trampa\n",
    "\n",
    "Cargas el archivo del mes con INSERT OR IGNORE, para que no reviente si alguna fila ya estaba. Corre limpio y sigues.\n",
    "\n",
    "```\n",
    "INSERT OR IGNORE INTO clientes VALUES\n",
    "    (1, 'Bodega Aurora', 'Lima', 'Bodega', '2025-01-01'),\n",
    "    (2, 'Minimarket Sur', 'Cusco', 'Bodega', '2025-01-01');\n",
    "-- 1 fila insertada de 2\n",
    "```\n",
    "\n",
    "**¿Qué está mal?** La respuesta está en el cuaderno de soluciones. Míralo tú primero."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Comprueba que lo tienes\n",
    "\n",
    "Dentro de una transacción borras mil filas por error y escribes ROLLBACK. ¿Qué vuelve y qué no?\n",
    "\n",
    "a) Vuelven las filas, pero un cambio de estructura hecho en medio no\n",
    "\n",
    "b) Vuelve todo, para eso está el ROLLBACK\n",
    "\n",
    "c) No vuelve nada, el DELETE es definitivo\n",
    "\n",
    "d) Vuelven solo las filas que no había leído nadie"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Lo que te llevas"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- 🔒 `BEGIN`, cambio, `SELECT` para mirar, y recién ahí\n",
    "`COMMIT` o `ROLLBACK`. Es la costumbre que convierte un\n",
    "desastre en un susto.\n",
    "\n",
    "- 📍 `SAVEPOINT` y `ROLLBACK TO` deshacen un pedacito sin\n",
    "tirar toda la transacción. Están en los cuatro.\n",
    "\n",
    "- ⚠️ En MySQL, un `CREATE`, `ALTER` o `DROP`\n",
    "confirma la transacción abierta sin avisar. En los otros tres, el DDL se puede\n",
    "deshacer.\n",
    "\n",
    "- 🔁 El upsert es `ON CONFLICT DO UPDATE` en PostgreSQL y SQLite,\n",
    "`ON DUPLICATE KEY UPDATE` en MySQL y `MERGE` en SQL\n",
    "Server.\n",
    "\n",
    "- 🎯 `excluded` es la fila que querías insertar, y con ella puedes\n",
    "sumar en vez de reemplazar.\n",
    "\n",
    "- 💣 `INSERT OR REPLACE` borra la fila y crea otra: lo que no\n",
    "nombras vuelve al `DEFAULT`. No es un update.\n",
    "\n",
    "- ♻️ Una carga con upsert es idempotente: la corres diez veces y da lo\n",
    "mismo.\n",
    "\n",
    "- 🚦 SQLite bloquea la base entera al escribir. Por eso es genial para\n",
    "aprender y mala para una web con mucha gente escribiendo.\n",
    "\n",
    "Y si de todo el capítulo te llevas una sola frase, que sea esta:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Cuenta antes, cuenta después, y que la diferencia no te la cuente nadie."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Y cargar un archivo en una tabla es media pelea. La otra media es abrir\n",
    "el archivo sin romperlo, con su codificación y sus tipos, y eso está en el\n",
    "[libro de Python](https://missyera.com/guias/python-desde-cero/) 📂\n",
    "\n",
    "En el capítulo 12 vamos a por qué una consulta tarda: índices,\n",
    "`EXPLAIN` y cómo leer lo que la base te contesta cuando le preguntas\n",
    "qué piensa hacer.\n",
    "\n",
    "Que tengas lindo día! 🌸"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "Ese era el capítulo 12 de **SQL desde cero**. El texto completo, con las salidas de cada bloque, está en https://missyera.com/guias/sql-desde-cero/upsert-y-transacciones/\n",
    "\n",
    "Que tengas lindo día! 🌸"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "name": "python",
   "version": "3.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}