{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "c7f30f47",
   "metadata": {},
   "source": [
    "# 22-22 · CRUD на SQLite\n",
    "\n",
    "Практика к разделу [«SQL: создаём, читаем и изменяем данные»](../../site/chapters/glava-22/22-22-sql.html)."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f45d4e03",
   "metadata": {},
   "source": [
    "## Цель\n",
    "\n",
    "Выполнить все четыре операции CRUD настоящими SQL-запросами через модуль sqlite3 — прямо в этом ноутбуке, без установки чего-либо дополнительно."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5dfa2039",
   "metadata": {},
   "source": [
    "## Рабочий пример"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f19290df",
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "\n",
    "baza = sqlite3.connect(\":memory:\")\n",
    "baza.execute(\"CREATE TABLE tasks (id INTEGER PRIMARY KEY, title TEXT NOT NULL, done INTEGER NOT NULL DEFAULT 0)\")\n",
    "\n",
    "baza.execute(\"INSERT INTO tasks (title) VALUES (?)\", (\"Купить хлеб\",))\n",
    "baza.execute(\"INSERT INTO tasks (title) VALUES (?)\", (\"Выучить SQL\",))\n",
    "baza.commit()\n",
    "\n",
    "stroki = baza.execute(\"SELECT id, title, done FROM tasks ORDER BY id\").fetchall()\n",
    "print(stroki)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a62cb3d2",
   "metadata": {},
   "source": [
    "## Проверка результата"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3cb5499d",
   "metadata": {},
   "outputs": [],
   "source": [
    "assert len(stroki) == 2\n",
    "assert stroki[0][1] == \"Купить хлеб\"\n",
    "assert stroki[1][1] == \"Выучить SQL\"\n",
    "assert stroki[0][2] == 0\n",
    "print(\"Верно: обе задачи добавлены (Create) и прочитаны (Read).\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "985cff9f",
   "metadata": {},
   "source": [
    "## Эксперимент — Update и Delete"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bc9909cc",
   "metadata": {},
   "outputs": [],
   "source": [
    "baza.execute(\"UPDATE tasks SET done = 1 WHERE title = ?\", (\"Купить хлеб\",))\n",
    "baza.execute(\"DELETE FROM tasks WHERE title = ?\", (\"Выучить SQL\",))\n",
    "baza.commit()\n",
    "\n",
    "posle = baza.execute(\"SELECT id, title, done FROM tasks\").fetchall()\n",
    "print(posle)\n",
    "\n",
    "assert len(posle) == 1\n",
    "assert posle[0][1] == \"Купить хлеб\"\n",
    "assert posle[0][2] == 1\n",
    "print(\"Верно: Update изменил done, Delete убрал вторую задачу.\")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Cartesian Python 3.14",
   "language": "python",
   "name": "cartesian-python314"
  },
  "language_info": {
   "name": "python",
   "version": "3.14.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}