SQL_lab_5_2024.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "provenance": [],
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/vn322/RUDN_Lab_Works/blob/main/SQL_lab_5_2024.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "**Методы**"
      ],
      "metadata": {
        "id": "Vo9T5hJVZrOR"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "import sqlite3 as sq\n",
        "\n",
        "with sq.connect(\"cars.db\") as con:\n",
        "    cur = con.cursor()\n",
        "\n",
        "    cur.execute(\"\"\"CREATE TABLE IF NOT EXISTS cars (\n",
        "        car_id INTEGER PRIMARY KEY AUTOINCREMENT,\n",
        "        model TEXT,\n",
        "        price INTEGER\n",
        "    )\"\"\")\n"
      ],
      "metadata": {
        "id": "mbUwESPyxVXM"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "\n",
        "    Когда контекстный менеджер завершает свою работу, он автоматически выполняет два метода:\n",
        "\n",
        "con.commit() – применение всех изменений в таблицах БД;\n",
        "\n",
        "con.close() – закрытие соединения с БД.\n",
        "# Это необходимые действия для сохранения внесенных изменений в БД.\n"
      ],
      "metadata": {
        "id": "4usUeG79Zz0A"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "дабавим несколько записей"
      ],
      "metadata": {
        "id": "DHVkzl93xfIe"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "cur.execute(\"INSERT INTO cars VALUES(1,'Audi',52642)\")\n",
        "\n",
        "cur.execute(\"INSERT INTO cars VALUES(2,'Mercedes',57127)\")\n",
        "\n",
        "cur.execute(\"INSERT INTO cars VALUES(3,'Skoda',9000)\")\n",
        "\n",
        "cur.execute(\"INSERT INTO cars VALUES(4,'Volvo',29000)\")\n",
        "\n",
        "cur.execute(\"INSERT INTO cars VALUES(5,'Bentley',350000)\")"
      ],
      "metadata": {
        "id": "NSmC4d0bxdOH",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "daffee61-1ffa-42d7-ef23-920652b4ebbf"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "<sqlite3.Cursor at 0x7e88c5f1b5c0>"
            ]
          },
          "metadata": {},
          "execution_count": 2
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Если данные хранятся в каких-либо коллекциях, например, так:"
      ],
      "metadata": {
        "id": "ab4HeC_UxmfP"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "cars = [\n",
        "    ('Audi', 52642),\n",
        "    ('Mercedes', 57127),\n",
        "    ('Skoda', 9000),\n",
        "    ('Volvo', 29000),\n",
        "    ('Bentley', 350000)\n",
        "]"
      ],
      "metadata": {
        "id": "qyuNd91faKoR"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "весь набор ранее приведенных запросов, можно заменить циклом:\n",
        "\n",
        "for car in cars:\n",
        "    cur.execute(\"INSERT INTO cars VALUES(NULL, ?, ?)\", car)\n",
        "\n",
        "Здесь вместо знаков вопроса будут подставлены соответствующие данные из первого кортежа списка"
      ],
      "metadata": {
        "id": "fUyAu3rEZ56J"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "Или, воспользоваться методом executemany, который специально для этого и существует:\n",
        "\n",
        "cur.executemany(\"INSERT INTO cars VALUES(NULL, ?, ?)\", cars)"
      ],
      "metadata": {
        "id": "1nrdBmzxah8f"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "помимо знаков вопроса можно использовать именованные параметры (плейсхолдеры). Для этого в запросе перед ними ставится двоеточие, а затем, указывается словарь, где имя – это ключ, вместо которого будет подставлено его значение:\n",
        "\n",
        "\n",
        "cur.execute(\"UPDATE cars SET price = :Price WHERE model LIKE 'A%'\", {'Price': 0})"
      ],
      "metadata": {
        "id": "ZSRHbiA8a8_x"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "выполнить несколько отдельных SQL-команд\n",
        "\n",
        "cur.executescript(\"\"\"DELETE FROM cars WHERE model LIKE 'A%'; UPDATE cars SET price = price+1000 \"\"\")"
      ],
      "metadata": {
        "id": "JRFc8UyKa_tL"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "# **commit и rollback**"
      ],
      "metadata": {
        "id": "JNQMSPcabK0m"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "import sqlite3 as sq\n",
        "con = None\n",
        "\n",
        "try:\n",
        "\n",
        "    con = sq.connect(\"cars.db\")\n",
        "    cur = con.cursor()\n",
        "\n",
        "    cur.executescript(\"\"\"CREATE TABLE IF NOT EXISTS cars (\n",
        "            car_id INTEGER PRIMARY KEY AUTOINCREMENT,\n",
        "            model TEXT,\n",
        "            price INTEGER\n",
        "        );\n",
        "        BEGIN;\n",
        "        INSERT INTO cars VALUES(NULL,'Audi',52642);\n",
        "        INSERT INTO cars VALUES(NULL,'Mercedes',57127);\n",
        "        INSERT INTO cars VALUES(NULL,'Skoda',9000);\n",
        "        INSERT INTO cars VALUES(NULL,'Volvo',29000);\n",
        "        INSERT INTO cars VALUES(NULL,'Bentley',350000);\n",
        "        UPDATE cars SET price = price+1000\n",
        "    \"\"\")\n",
        "    con.commit()\n",
        "\n",
        "except sq.Error as e:\n",
        "    if con: con.rollback()\n",
        "    print(\"Ошибка выполнения запроса\")\n",
        "finally:\n",
        "    if con: con.close()\n",
        "\n"
      ],
      "metadata": {
        "id": "mpF5Kr6j6RyV"
      },
      "execution_count": 1,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "здесь сами вызываем методы commit и close. Если операции с таблицами прошли успешно, то они будут сохранены, если же возникли какие-либо ошибки (исключения), то будет вызван метод rollback, который откатывает состояние БД в состояние отметки BEGIN, то есть, все внесенные изменения применены не будут.\n"
      ],
      "metadata": {
        "id": "7YRM-j57yEf-"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "#в команде UDPATE неверное имя таблицы:\n",
        "UPDATE cars2 SET price = price+1000\n"
      ],
      "metadata": {
        "id": "piCTU0OqyON2"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "import sqlite3 as sq\n",
        "\n",
        "with sq.connect(\"cars.db\") as con:\n",
        "    cur = con.cursor()\n",
        "\n",
        "    cur.execute(\"\"\"CREATE TABLE IF NOT EXISTS cars (\n",
        "        car_id INTEGER PRIMARY KEY AUTOINCREMENT,\n",
        "        model TEXT,\n",
        "        price INTEGER\n",
        "    )\"\"\")\n",
        "\n",
        "    Когда контекстный менеджер завершает свою работу, он автоматически выполняет два метода:\n",
        "\n",
        "con.commit() – применение всех изменений в таблицах БД;\n",
        "\n",
        "con.close() – закрытие соединения с БД.\n",
        "Это необходимые действия для сохранения внесенных изменений в БД.\n"
      ],
      "metadata": {
        "id": "kyB1bVvEwMsk"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "Если операции с таблицами прошли успешно, то они будут сохранены, если же возникли какие-либо ошибки (исключения), то будет вызван метод rollback, который откатывает состояние БД в состояние отметки BEGIN, то есть, все внесенные изменения применены не будут."
      ],
      "metadata": {
        "id": "XC0K1nvabjD9"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "Если при работе с БД предполагается сохранять вносимые изменения сразу после выполнения SQL-запроса, то это можно сделать с помощью метода connect, установив в нем параметр isolation_level=None:\n",
        "\n",
        "\n",
        "with sq.connect(\"cars.db\", isolation_level=None) as con:\n",
        "    cur = con.cursor()\n",
        "\n",
        "    cur.executescript(\"\"\"INSERT INTO cars VALUES(NULL,'Audi',52642);\n",
        "        INSERT INTO cars VALUES(NULL,'Mercedes',57127);\n",
        "        INSERT INTO cars VALUES(NULL,'Skoda',9000);\n",
        "        INSERT INTO cars VALUES(NULL,'Volvo',29000);\n",
        "        INSERT INTO cars VALUES(NULL,'Bentley',350000);\n",
        "    \"\"\")\n",
        "\n",
        "Однако так делать без особой надобности не стоит, т.к. это уменьшает скорость работы с БД из-за постоянной записи данных непосредственно в файл. Без изменения этого параметра все изменения сохраняются в памяти, а потому работа происходит куда быстрее."
      ],
      "metadata": {
        "id": "MtU9_Rklbq3k"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "**Задание 6**\n",
        "\n",
        "создайте таблицу cars с полями car_id (первичный ключ) model и price\n",
        "вставьте в нее несклоько записей из списка\n",
        "\n",
        "\n",
        "cars = [\n",
        "    ('KAMAZ КОМПАС', 32642),\n",
        "    ('Лада Президент', 5154),\n",
        "    ('Москвич пасфаиндер', 3000)\n",
        "]\n",
        "\n",
        "\n",
        "Продемонстируйте работу команд commit и rollback"
      ],
      "metadata": {
        "id": "Z94KzyqM3GNX"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "import sqlite3 as sq\n",
        "con = None\n",
        "cars = [ ('KAMAZ КОМПАС', 32642), ('Лада Президент', 5154), ('Москвич пасфаиндер', 3000) ]\n",
        "try:\n",
        "\n",
        "    con = sq.connect(\"cars.db\")\n",
        "    cur = con.cursor()\n",
        "\n",
        "    cur.executescript(\"\"\"CREATE TABLE IF NOT EXISTS cars (\n",
        "            car_id INTEGER PRIMARY KEY AUTOINCREMENT,\n",
        "            model TEXT,\n",
        "            price INTEGER\n",
        "        );   \"\"\")\n",
        "    cur.executemany(\"INSERT INTO cars VALUES(NULL,?, ?)\", cars)\n",
        "    con.commit()\n",
        "\n",
        "except sq.Error as e:\n",
        "    if con: con.rollback()\n",
        "    print(\"Ошибка выполнения запроса\")\n",
        "finally:\n",
        "    if con: con.close()\n",
        "\n"
      ],
      "metadata": {
        "id": "PNetEDQuMVfI"
      },
      "execution_count": 2,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "with sq.connect(\"cars.db\") as con:\n",
        "    cur = con.cursor()# запрос на выборку записей:\n",
        "cur.execute(\"SELECT model, price FROM cars\")\n",
        "#после формирования выборки сам экземпляр класса Cursor можно\n",
        "#использовать как итерируемый объект и выбирать записи в цикле:\n",
        "for result in cur:\n",
        "    print(result)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "d1a25239-9c41-41b5-909c-95fef644a2c0",
        "id": "h6kOiTcyNMFj"
      },
      "execution_count": 4,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "('KAMAZ КОМПАС', 32642)\n",
            "('Лада Президент', 5154)\n",
            "('Москвич пасфаиндер', 3000)\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "**Свойство lastrowid**\n",
        "\n",
        "1) создать еще одну таблицу cust, которая содержит покупателей машин.\n",
        "\n",
        "2) если происходит покупка по «trade-in», то прежняя машина владельца добавляется в конец таблицы cars, а в таблице cust появляется запись с именем покупателя, идентификатором машины сданной в «trade-in» и id новой купленной машины."
      ],
      "metadata": {
        "id": "TT9oGnQkbzm-"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "with sq.connect(\"cars.db\") as con:\n",
        "    cur = con.cursor()\n",
        "\n",
        "    cur.executescript(\"\"\"CREATE TABLE IF NOT EXISTS cars (\n",
        "            car_id INTEGER PRIMARY KEY AUTOINCREMENT,\n",
        "            model TEXT,\n",
        "            price INTEGER\n",
        "        );\n",
        "        CREATE TABLE IF NOT EXISTS cust(name TEXT, tr_in INTEGER, buy INTEGER);\n",
        "    \"\"\")\n",
        "\n",
        "    cur.execute(\"INSERT INTO cars VALUES(NULL,'Запорожец', 1000)\")"
      ],
      "metadata": {
        "id": "gtzWwkCnCT4t"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "Таблица cust с тремя полями\n",
        "\n",
        "Надо добавить в таблицу cars автомобиль «Запорожец», который сдает покупатель Федор по trade-in.\n",
        "Как теперь узнать car_id этой записи? Для этого можно воспользоваться специальным свойством:\n",
        "\n",
        "last_row_id = cur.lastrowid\n",
        "\n",
        "которое содержит значение rowid последней добавленной записи. В нашем случае поля car_id и rowid будут совпадать"
      ],
      "metadata": {
        "id": "-WgaGM92CmqW"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "buy_car_id = 2\n",
        "\n",
        "cur.execute(\"INSERT INTO cust VALUES('Федор', ?, ?)\", (last_row_id, buy_car_id))\n",
        "\n",
        "при выполнении программы в таблице cust появиться запись."
      ],
      "metadata": {
        "id": "wlXhq_VPC3iY"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "**функционал API для работы с СУБД SQLite.**"
      ],
      "metadata": {
        "id": "pnQ2h72IEPIx"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "\n",
        "*   fetchall() – возвращает число записей в виде упорядоченного списка;\n",
        "*   fetchmany(size) – возвращает число записей не более size;\n",
        "*   fetchone() – возвращает первую запись."
      ],
      "metadata": {
        "id": "-MA8GErpERMa"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "import sqlite3 as sq\n",
        "\n",
        "cars = [\n",
        "    ('Audi', 52642),\n",
        "    ('Mercedes', 57127),\n",
        "    ('Skoda', 9000),\n",
        "    ('Volvo', 29000),\n",
        "    ('Bentley', 350000)\n",
        "]\n",
        "\n",
        "with sq.connect(\"cars.db\") as con:\n",
        "    cur = con.cursor()\n",
        "\n",
        "    cur.executescript(\"\"\"CREATE TABLE IF NOT EXISTS cars (\n",
        "            car_id INTEGER PRIMARY KEY AUTOINCREMENT,\n",
        "            model TEXT,\n",
        "            price INTEGER)\n",
        "    \"\"\")\n",
        "\n",
        "    cur.executemany(\"INSERT INTO cars VALUES(NULL,?, ?)\", cars)"
      ],
      "metadata": {
        "id": "Bb88iusXEtWa"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# запрос на выборку записей:\n",
        "cur.execute(\"SELECT model, price FROM cars\")\n",
        "# получить доступ к сформированной выборке\n",
        "rows = cur.fetchall()\n",
        "print(rows)"
      ],
      "metadata": {
        "id": "wW6owngOE3Xs",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "90f0fa82-49a9-4c7b-8699-569f690475de"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "[('Audi', 53642), ('Mercedes', 58127), ('Skoda', 10000), ('Volvo', 30000), ('Bentley', 351000), ('Запорожец', 1000), ('Audi', 52642), ('Mercedes', 57127), ('Skoda', 9000), ('Volvo', 29000), ('Bentley', 350000)]\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# запрос на выборку записей:\n",
        "cur.execute(\"SELECT model, price FROM cars\")\n",
        "rows = cur.fetchone()\n",
        "print(rows)"
      ],
      "metadata": {
        "id": "rHH06ed5FJl2",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "c79876f9-47c4-48f2-a56e-f13b0d450061"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "('Audi', 53642)\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# запрос на выборку записей:\n",
        "cur.execute(\"SELECT model, price FROM cars\")\n",
        "rows = cur.fetchmany(4)\n",
        "print(rows)"
      ],
      "metadata": {
        "id": "qsM6_q5wFQuM",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "d15f3fc7-1083-47dc-8e6e-240d54a52aa1"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "[('Audi', 53642), ('Mercedes', 58127), ('Skoda', 10000), ('Volvo', 30000)]\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# запрос на выборку записей:\n",
        "cur.execute(\"SELECT model, price FROM cars\")\n",
        "#после формирования выборки сам экземпляр класса Cursor можно\n",
        "#использовать как итерируемый объект и выбирать записи в цикле:\n",
        "for result in cur:\n",
        "    print(result)"
      ],
      "metadata": {
        "id": "q0PLd-NwFm8s",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "9b78ef6f-7478-4ad2-b9de-ffc7238bec2b"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "('Audi', 53642)\n",
            "('Mercedes', 58127)\n",
            "('Skoda', 10000)\n",
            "('Volvo', 30000)\n",
            "('Bentley', 351000)\n",
            "('Запорожец', 1000)\n",
            "('Audi', 52642)\n",
            "('Mercedes', 57127)\n",
            "('Skoda', 9000)\n",
            "('Volvo', 29000)\n",
            "('Bentley', 350000)\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "Преимущество такого подхода в экономии памяти при большом числе записей в выборке. Здесь на каждой итерации цикла мы выбираем только одну следующую запись, а не храним их сразу целиком в памяти в виде списка. Это часто бывает эффективно и очень удобно."
      ],
      "metadata": {
        "id": "uqmHiuU8F32H"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "Далее, иногда более предпочтительным вариантом выходных данных является не кортеж с данными, а словарь, позволяющий обращаться к элементам по именам полей. Для этого после установления соединения с БД следует прописать вот такую строчку:\n",
        "\n",
        "con.row_factory = sq.Row"
      ],
      "metadata": {
        "id": "PQU1YXVvG2Gd"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "еперь, при выполнении программы увидим, что переменная result в цикле ссылается на объект Row, а не кортеж:\n",
        "\n",
        "<sqlite3.Row object at 0x00000185B226B8B0>\n",
        "\n",
        "И через этот объект доступ к данным осуществляется с помощью имен полей таблицы cars:\n",
        "\n",
        "print(result['model'], result['price'])"
      ],
      "metadata": {
        "id": "l5DMIocEHB3M"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "12.11.22\n",
        "\n",
        "**Хранение изображений в БД**\n",
        "\n",
        "специальный тип данных BLOB"
      ],
      "metadata": {
        "id": "bIB-Mj1bksak"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "cur.executescript(\"\"\"CREATE TABLE IF NOT EXISTS users (\n",
        "    name TEXT,\n",
        "    ava BLOB,\n",
        "    score INTEGER)\n",
        "\"\"\")"
      ],
      "metadata": {
        "id": "0CGRrq-rlasG",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "af432ee3-5aea-4a5d-e3c4-ac6dd215fff6"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "<sqlite3.Cursor at 0x7f01741207a0>"
            ]
          },
          "metadata": {},
          "execution_count": 29
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "#функция считывания изображения из файла\n",
        "def readAva(n): #номер аватарки\n",
        "    try:\n",
        "        with open(f\"avas/{n}.png\", \"rb\") as f:\n",
        "            return f.read()\n",
        "    except IOError as e:\n",
        "        print(e)\n",
        "        return False\n",
        "\n",
        "#возвратит набор двоичных данных, иначе значение False"
      ],
      "metadata": {
        "id": "crMW8Xp0lWjJ"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "img = readAva(1)\n",
        "if img:\n",
        "    binary = sq.Binary(img) # преобразование\n",
        "\n",
        "    cur.execute(\"INSERT INTO users VALUES ('Николай', ?, 1000)\", (binary,))"
      ],
      "metadata": {
        "id": "KgD87sQplx_K"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "#прочитаем изображение из этого поля\n",
        "cur.execute(\"SELECT ava FROM users LIMIT 1\")"
      ],
      "metadata": {
        "id": "rS_Wm3tDmL5m"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# с помощью метода fetchone обратимся к первой записи и возьмем данные из поля ava:\n",
        "img = cur.fetchone()['ava']"
      ],
      "metadata": {
        "id": "jd1XBS1JmYuj"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "#сохраним его в файл\n",
        "def writeAva(name, data):\n",
        "    try:\n",
        "        with open(name, \"wb\") as f:\n",
        "            f.write(data)\n",
        "    except IOError as e:\n",
        "        print(e)\n",
        "        return False\n",
        "\n",
        "    return True"
      ],
      "metadata": {
        "id": "EeGm8chZmgwl"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "writeAva(\"out.png\", img)"
      ],
      "metadata": {
        "id": "V8d6jhtDmnvM"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "**Создание бэкапа БД**\n",
        "iterdump()"
      ],
      "metadata": {
        "id": "qqgTYE71mqTX"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "with sq.connect(\"cars.db\") as con:\n",
        "    cur = con.cursor()\n",
        "\n",
        "    for sql in con.iterdump():\n",
        "        print(sql)"
      ],
      "metadata": {
        "id": "HiiXINPdmvt1"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "#сохраним все эти строчки в отдельном файле:\n",
        "\n",
        "    with open(\"sql_damp.sql\", \"w\") as f:\n",
        "        for sql in con.iterdump():\n",
        "            f.write(sql)"
      ],
      "metadata": {
        "id": "6Dnm-mzbm2tA"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        " #чтобы восстановить БД с помощью этого файла можно воспользоваться методом executescript, о котором мы уже говорили:\n",
        "\n",
        "    with open(\"sql_damp.sql\", \"r\") as f:\n",
        "        sql = f.read()\n",
        "        cur.executescript(sql)"
      ],
      "metadata": {
        "id": "jVWLusilm7Zj"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "**Создание БД в памяти**\n",
        "\n",
        "sq.connect(':memory:')"
      ],
      "metadata": {
        "id": "azPaeV_onDxP"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "data = [(\"car\", \"машина\"), (\"house\", \"дом\"), (\"tree\", \"дерево\"), (\"color\", \"цвет\")]\n",
        "\n",
        "con = sq.connect(':memory:')\n",
        "with con:\n",
        "    cur = con.cursor()\n",
        "    cur.execute(\"\"\"CREATE TABLE IF NOT EXISTS dict(\n",
        "        eng TEXT, rus TEXT\n",
        "    )\"\"\")\n",
        "\n",
        "    cur.executemany(\"INSERT INTO dict VALUES(?,?)\", data)\n",
        "\n",
        "    cur.execute(\"SELECT rus FROM dict WHERE eng LIKE 'c%'\")\n",
        "    print(cur.fetchall())"
      ],
      "metadata": {
        "id": "MFZdQ36gnCkZ"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "Что будет результатом?"
      ],
      "metadata": {
        "id": "rtne_gFgnudY"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "Задание 1:\n",
        "\n",
        "Спроектируйте и реализуйте базу данных\n",
        "\n",
        "1)студентов (фио. др. и номер студбилета)\n",
        "\n",
        "2)группы(назвние)\n",
        "\n",
        "3)предметы (название, к-во часов) которые они изучают\n",
        "\n",
        "4)вид контроля (экзамен, зачет-рейтинг и кр)\n",
        "\n",
        "5)оценка по предмету с датой проставления полученная конкретным студентом."
      ],
      "metadata": {
        "id": "exj2sFwLJBzt"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "import sqlite3 as sq\n",
        "\n",
        "students = [\n",
        "    ('Andi', 2001, 52642,1),\n",
        "    ('Maria', 2002, 57127,1),\n",
        "    ('Sam', 2001,90020,1),\n",
        "    ('Vova', 2003, 29000,2),\n",
        "    ('Ben', 2000, 35000,2)\n",
        "]\n",
        "\n",
        "with sq.connect(\"uni.db\") as con:\n",
        "    cur = con.cursor()\n",
        "\n",
        "    cur.executescript(\"\"\"CREATE TABLE IF NOT EXISTS students (\n",
        "            stud_id INTEGER PRIMARY KEY AUTOINCREMENT,\n",
        "            name TEXT,\n",
        "            year INTEGER,\n",
        "            bilet INTEGER,\n",
        "            group_id INTEGER)\n",
        "    \"\"\")\n",
        "\n",
        "    cur.executemany(\"INSERT INTO students VALUES(NULL,?, ?, ?,?)\", students)"
      ],
      "metadata": {
        "id": "7hf8IzNcXEmd"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "cur.execute(\"SELECT * FROM students\")\n",
        "#после формирования выборки сам экземпляр класса Cursor можно\n",
        "#использовать как итерируемый объект и выбирать записи в цикле:\n",
        "for result in cur:\n",
        "    print(result)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "aaO_n7e3YKR6",
        "outputId": "71366fd1-a5e9-4cf8-ecb4-61a307f361fe"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "(1, 'Andi', 2001, 52642, 1)\n",
            "(2, 'Maria', 2002, 57127, 1)\n",
            "(3, 'Sam', 2001, 90020, 1)\n",
            "(4, 'Vova', 2003, 29000, 2)\n",
            "(5, 'Ben', 2000, 35000, 2)\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "import sqlite3 as sq\n",
        "\n",
        "groups = [\n",
        "    (1,'Mathematics'),\n",
        "    (2,'Informatics'),\n",
        "\n",
        "]\n",
        "\n",
        "with sq.connect(\"uni.db\") as con:\n",
        "    cur = con.cursor()\n",
        "\n",
        "    cur.executescript(\"\"\"CREATE TABLE IF NOT EXISTS groups (\n",
        "            group_id INTEGER PRIMARY KEY AUTOINCREMENT,\n",
        "            name TEXT)\n",
        "    \"\"\")\n",
        "\n",
        "    cur.executemany(\"INSERT INTO groups VALUES(?, ?)\", groups)"
      ],
      "metadata": {
        "id": "KtPDfmJpXB7r"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "cur.execute(\"SELECT * FROM groups\")\n",
        "#после формирования выборки сам экземпляр класса Cursor можно\n",
        "#использовать как итерируемый объект и выбирать записи в цикле:\n",
        "for result in cur:\n",
        "    print(result)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "7f105ffa-328b-4a5b-86c0-46c4fa11a7ee",
        "id": "rG58NhndoYYg"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "(1, 'Mathematics')\n",
            "(2, 'Informatics')\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "import sqlite3 as sq\n",
        "\n",
        "subject = [\n",
        "    ('Math', 100),\n",
        "    ('Info', 80),\n",
        "    ('Physic', 50),\n",
        "\n",
        "]\n",
        "\n",
        "with sq.connect(\"uni.db\") as con:\n",
        "    cur = con.cursor()\n",
        "\n",
        "    cur.executescript(\"\"\"CREATE TABLE IF NOT EXISTS subject (\n",
        "            subject_id INTEGER PRIMARY KEY AUTOINCREMENT,\n",
        "            subject TEXT,\n",
        "            hour INTEGER)\n",
        "    \"\"\")\n",
        "\n",
        "    cur.executemany(\"INSERT INTO subject VALUES(NULL,?, ?)\", subject)"
      ],
      "metadata": {
        "id": "EPb2aDzBXCGl"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "cur.execute(\"SELECT * FROM subject\")\n",
        "#после формирования выборки сам экземпляр класса Cursor можно\n",
        "#использовать как итерируемый объект и выбирать записи в цикле:\n",
        "for result in cur:\n",
        "    print(result)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "6Tj3Ali0pYWq",
        "outputId": "c4f0a536-dd85-4021-c252-bff90c5f05bd"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "(1, 'Math', 100)\n",
            "(2, 'Info', 80)\n",
            "(3, 'Physic', 50)\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "import sqlite3 as sq\n",
        "\n",
        "control = [\n",
        "    (1,'Exam'),\n",
        "    (2,'Zachet'),\n",
        "    (3,'Test')\n",
        "]\n",
        "\n",
        "with sq.connect(\"uni.db\") as con:\n",
        "    cur = con.cursor()\n",
        "\n",
        "    cur.executescript(\"\"\"CREATE TABLE IF NOT EXISTS control (\n",
        "            control_id INTEGER PRIMARY KEY AUTOINCREMENT,\n",
        "            art TEXT)\n",
        "    \"\"\")\n",
        "\n",
        "    cur.executemany(\"INSERT INTO control VALUES(?,?)\", control)"
      ],
      "metadata": {
        "id": "ZL3DEdul2aMl"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "cur.execute(\"SELECT * FROM control\")\n",
        "#после формирования выборки сам экземпляр класса Cursor можно\n",
        "#использовать как итерируемый объект и выбирать записи в цикле:\n",
        "for result in cur:\n",
        "    print(result)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "55bf7afd-7ad4-4a6b-df4d-af2ca42ae3e4",
        "id": "df-2j-HyvIGK"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "(1, 'Exam')\n",
            "(2, 'Zachet')\n",
            "(3, 'Test')\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "import sqlite3 as sq\n",
        "\n",
        "value = [\n",
        "    (1, 1, '2024-5-7',5),\n",
        "    (1, 2, '2024-6-7',4),\n",
        "    (3, 3,'2024-5-17',3),\n",
        "    (2, 1, '2024-5-27',4),\n",
        "    (5, 2, '2024-6-1',3)]\n",
        "\n",
        "with sq.connect(\"uni.db\") as con:\n",
        "    cur = con.cursor()\n",
        "\n",
        "    cur.executescript(\"\"\"CREATE TABLE IF NOT EXISTS value (\n",
        "            val_id INTEGER PRIMARY KEY AUTOINCREMENT,\n",
        "            stud_id INTEGER,\n",
        "            subject_id INTEGER,\n",
        "            date TEXT,\n",
        "            value INTEGER)\n",
        "    \"\"\")\n",
        "\n",
        "    cur.executemany(\"INSERT INTO value VALUES(NULL,?, ?,?,?)\", value)"
      ],
      "metadata": {
        "id": "buSpJ-mzW-Sb"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "cur.execute(\"SELECT * FROM value\")\n",
        "#после формирования выборки сам экземпляр класса Cursor можно\n",
        "#использовать как итерируемый объект и выбирать записи в цикле:\n",
        "for result in cur:\n",
        "    print(result)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "f4f0205c-2ac8-44e8-cc9c-e8b1dcb07a84",
        "id": "a_uPaK8HZI7s"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "(1, 1, 1, '2024-5-7', 5)\n",
            "(2, 1, 2, '2024-6-7', 4)\n",
            "(3, 3, 3, '2024-5-17', 3)\n",
            "(4, 2, 1, '2024-5-27', 4)\n",
            "(5, 2, 2, '2024-6-1', 3)\n",
            "(6, 1, 1, '2024-5-7', 5)\n",
            "(7, 1, 2, '2024-6-7', 4)\n",
            "(8, 3, 3, '2024-5-17', 3)\n",
            "(9, 2, 1, '2024-5-27', 4)\n",
            "(10, 2, 2, '2024-6-1', 3)\n",
            "(11, 1, 1, '2024-5-7', 5)\n",
            "(12, 1, 2, '2024-6-7', 4)\n",
            "(13, 3, 3, '2024-5-17', 3)\n",
            "(14, 2, 1, '2024-5-27', 4)\n",
            "(15, 5, 2, '2024-6-1', 3)\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "Задание 2:\n",
        "\n",
        "создайте таблици связей между этими 5ю таблицами\n",
        "Заполните эти таблицы"
      ],
      "metadata": {
        "id": "pRveJzliMI6d"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "![Screenshot_16.png](data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAhsAAAEiCAYAAABQuhBFAAAAAXNSR0IArs4c6QAAAARnQU1BAACxjwv8YQUAAAAJcEhZcwAADsQAAA7EAZUrDhsAADrHSURBVHhe7d1fjOPWfS/w39jbpE5rx3ZjwIaDxJtIm3Ys+9rIRWFQD4bzYEDaOhjkYYBrP0xqGBobvYbkAvMQQMA6wAD3YYFaQuO4EorU85C0mId6AmQlIA8N/DBCg8JY/5Gn8EjFOoANtHfdrpOisd27tu75HZ4jHlKkRpRIDUl9PwvuiKTI0Ug/Hf54zuHh2kggAAAAgJjcoH4CAAAAxALJBgAAAMQKyQYAAADECskGAAAAxArJBgAAAMQKyQYAAADECskGAAAAxArJBgAAAMQKyQYAAADECskGAAAAxArJBgAAAMQKyQYAAADECjdiAwBImLXG6+oRgGNUe0A9Sh/UbAAAAECsULMBAJAwumZjVLtd/oTVttb4D/kTNRsAAAAAAZBsAAAAQKyQbAAAAECskGwAAABArNBBFAAgYZLeQfQLP7hGH12f/dDBf8eV33xGZ2/B+e080EEUAABWSthEg/HBcvFE4xe0vfYdag7VrGn4IyoGrYNECF2zgcFmwE/SM27E7epK49lgkms29Fl2WIv/LZxs/CWtD/6eqjm1KBJx7Tc6qNkAAICV89RDX/adgtYBzF2zgcFmgKUl40bcrp40nw0mvWYjKIH46398j/7X/7yLfu/MjWqJjZdP/i3vUrP4MNV69lyl8ytqlby1DOa8etz5E9ovf594M6vxKh1W7/E8T8xys0refg7R49QZ/R8qycfe3/kyUfm71LZnxYKXadR6RM0kB2o2AAAADN5EI1C3RbWCOLiPfiWnlp0NnOA1qu0S7fE2gwtEtT/36achEo/8z2hzYO931CHabb4rltuJxv7mq8bvfIRao5epQt+kBj8/gYlGViDZAACAULimwm8KWucrnyer/V0qykRgViIp2HuSZKVH7kmqV16jo4Fc4RheoT4nJfmv0tqamMo/od7RFbH8H2i/9zjVZU0ILBuSDQAACIWbRPymoHW+RLJwOPoV7dGfy6Rgu6uWz+xdOu6rhxO46UTVbPCEGotTh2QDAAAi88HHn6lHs8lV/54GjW9S/5hrOM7SumXUVnR/7vSnkF6j/UuqJkTVVGx4m19yZ6lAP1FNJ4bct2jT8lkOS5G5ZIOvAefONLNOjAebAVg2xCqk0Q1ra+qRvy/97oyHle737GYOMeVr31DNG/dQtf44tcuqCeSAqGI/W/kmFY7smpC1/Pep0NEdP02PUEv253h4vP+17V+I5WLfhy9TwVhu16Y8QhsV1ewinwdxyNTVKPMMNsOS+Lc4vL2zk0UfBHE1SjjZjNVkSUts+kn61VP6vZ1V/H8Hl5M/p43xVSfZkuZY1jJVszFP4b0c/EXA6HbgSG6sApyMk4cwU+y4ucXKU17NQvJkrhnFHEjGnILWAZwWv3jkKWgdAHjIYcq/Smvld5yrVCCRVqqD6H9d/1Q9mgVfk63a+8Zte4IObu9yXXvRdNoh7Uu6eDkPGmO2CdrP3d7+jnje90juInC/sIpCxaoRO8Xmj4xatDBx5q19M+fV4675e3Qnu4DvCcAyqCtaRqPkDjUOtpVKNmYebMZ38BdeLgpd2SlJXU41uED9sllAi4Ti6FF7Xedx6tVaooAPGjTmNeqv/4V4LrcxnrRfWDWzx6o7dvboZxO996OJs4DBlOYamAkAVk3mkg2/AWV4ClrnK2jwFzlYjHGp1cSgMiKh2FHJROlRkWC8Q8eBBfk3afO82v+J+4Us8otHnoLW+eLYsS7QjoqdXPXZid770cRZwGBKcw3MBACrJnPJhl/nJJ6C1gGcFr945CloXXIYgympauz5B2YCgFWQqUtf+fKgoNfF664+fevENeD+2zjNKPZNfjRuu/4ukbxhkJjl9u/8gOq6ivqkGwj5rtPzs+43WdJySVbS4ja6WHXHzrD5HcrXiBqBcRcUZ3bMH9XVOh4DgTvdjffzXerrm165tnPw796iv/B8Z05PWmLTj45XAFOaL33NVLJxY/MafVq9Tc25BRXuwYW+XcDq9m/7joTigSxo9d0EuR+GWZAHJxTd7a9SmXcm7ypInucKM+83WdJSoCctbiONVZkY/EQ+tBoXqFD7mYoXn9gJjDPB2A9VHqdK+x3Xfrh9pt1+zV6tvw/mNq67a56+tMSmHyde03twgehkIR4yd4t5XcDMKql/R1og2ZhfLLEaUOuwmGQnvEGQbEBWINmAlYdkI1lkDVr/Ag0OoxxzAMnGsiHZAFMW4mGlLn0FyB73OBfl9uPUiTTRAABYHJINgFTjm0upcTPkFEefCR4rJl21GgCQLEg2AAAAIFZINgAAACBWSDYAAFKCOwrqzoLs9asfjZfxxPNMz59pvkE3v/imfLyQ7jatra1R0W9s+2nrojTl9wybRbkueFC5Lm2vbdv3B5KP12ht4sm8vCiH4e9ui/X8HM9UbDbtbY3JtZthk4rm+mKT7Ferfqcx2dsFLc+eua9GATCl5WoUWD1ZuRqFl22t3057Rzzeir2cl1175j669fM3ykTjwR+/49rm+mcj+q//9xnd+tJbi70PfKAvt8lqDOjQ23ln2rpZqX1UOiN7/BY/U34PJxv5Wm/K9nxQP6CNUYtK8vEu9a0eFerm8+3l64NDo3+Sd5nfcxRONPL7tGms625vE7Wc3zm5nWe5/BuJOvJ1OvziIXU42QiDXrgsJwCWlnhA3K6eNH/mfq/9yq8/kT+D/qZrH1+fWPffn4psI4r3oFPhk9KRONCrBdHqVEjuXyQLcxk0rBO274wqVBH/68fWqNFpjCyrMXL+IrXc9Sd6l/k9hw1GDeuk3++33Wz794uHtEEzCgBACtxzy+fUI3+3vfQWXXjoTjVnnw3f1X6bXnj4brXExGfURvU9193zWbV4rJspfJsmji46zQS6icCzXXBTguBZx9twk4UcXVlol8XyoHaEgNfHE9dqhJavUr1Qoy39uhcxvET7vYpzk8N5dQ+obW3SeW+tSQYg2QAASLEPP/lUJhbVB++g541kg6vcP3i6QM+9+j7VxGTqbpepTRaJM2iu3aZRYNuFW6+/Tnvi+QNxGk89vwO1SGLyNepVOvZ+Bw2yxPPynEDIZgaxzmqQOHGX67k5pNQaUUfdqpibQWZ6LSLxkAmG+j16+7BKrQ4Valuyn8bselTL64RJ9wMRrHXKq4dOImT3AbEFbGcuP9ig0WE1k+PkINkAAEgp7qfBNRqvPHaWfvovv5ZJh3dizctX5U8tvy6SBXWQ8+twGcTaPC8PhLnzmyJVEXs4GsjlY3xmzj/bZfvgycmFXMEn//vysd7HIroHdlVIRVUl2H/PPErU6hSodjGgNsWXkaSZfSt6R6TfjVz1UKzr8O2EDAHb6eWcmLUPjCQkW5BsAACkFHcIvfzEN2jj61+kd3/z32qpXasxrTOhPBiq6oBeLe9u6ljA8Lgvf3InTvugqiajtqJwLmHn7aUdavTLi10FkjtHBerT8SJvYq5Ke40+7UbRrJNASDYAAFKAm0J0TQX/1Je5csKhlzNOMnStxtSrF0otkQh4z76d2orB0WQ/iN7+JZmU6FoKXbOgjWs8ahedM/RuUzYl6HXt3cnEJmzNhH5+Xx7dh3Rpf/K1zi5H1b0G9cvctDSvEu00xGeUN5tHwstV63M066QDkg0AgBRoPHz3uMaCpwfuuMk1H4YzjoTqu7FXpRyf4ctswG4C2e37JADc/0Ks0/0lJrpXiLPzQ24OEHsty/2LqbzvXqf2wet0Ew4fZDnpmdpB1MA1M1wxI2tl1vI0T/9QF1mrMGvCY/a9MP8Gri0i5+8W722/sWdc6uq/nZtKWraaaj5DRiEFXYLjXX75//52vIwnnmd6/sbG66Pf/8Eb8nHinXTZ15T14S/JohH3knJzLofSl4h5J6vRsLc1JtduBo2R+Co568eXfKnfaUz2dkHL3fTnmXRBr9O7PFNxyxCbai5d5nntehtzO79lkYv5slhIdyxrkdRscHUdDzZj4qo9HmyGM25uU+R5prPwj5+9n9576l65LHbqkqmF2uSWxiJrSvsh99wWn5uq/nQ6HB1Wz4m1ZgckY7Ca8WAzzrpOwenMFLiduVwcSdrlxaoIkybxcZs4iM20MJtV4mb307BoM7LrNT2X5coJn2/aRZJsXHlynV5+9CtqzsYFM49qx/yuD+dUeOFR7Wakey7HRrZ92pdxRWGz3qC+T7vmfIbU3KpRoeMeua4kR7ULobQhDiALdoBKmKTHbSQQmytpOfFpJwXcpGK5mgsWVaKWSi6dKWRMQOJEkmxEO9iM4DPwi6RqKAKXF4sTA874Dhjj2f84aw7a/0nUdvr5GGwmHaKOW/Nz15OsBQgbbwHxJPel121vT8S6L8TmyjDjmWPVrN04Kdbn4yQFUSWzkF2xdhCdZ7AZWTD7DPwiC03OGtQgLjyoDHcOclfpbroGnOFLp6cPGKOrYkXWPNP+ZyD2g8Fm0m2uuFWfu77kz/9zjy7eTh5cyQdiM9O4po5j1G/idQCnKbZkY97BZoIGfvEO4jK+jOrAKJ0L51zb2JdFTWGcDc20/xlgsJl0mzdu9eeuxxDw/dwjjLcTB1fygdgEgNMSW7LBHevmGWxGS9zAL6cBg80s3aJxuzIQmwAQQiTJRpSDzQQN/FLasM93dG1F0KAyfmY5g1tk/yYMNpMeUcZt2M99lnhbZHAlP4jN9NFxh2m1p0wYhTTP9b7mNkGPXTzX3Y+v31bXcwcuVxfbD9T4Ac5138Z1+fwcvX/X7YWFE/bv7M/Ds77jM96A3zgANn5tnlsfe36N/fd4l3ufOzn2gOv1Bv1tgdtNvhb5OsR7Zgr8DBNmntdpbhP02GR+7pZljGERNt7U7arHy819ebaRU3BwjZ+P2EyXNL92iF4W4mGN/xNf4pnpLCtMlbKZment/JZB+swTD6dh2XHLnR25M6Y4kE+OsrgI1bFUHHRxBcAJ0hKbftL82iF6WYiHWK9G8WMW1umDwWZWVbi47dJFvurDatBOlInGVIhNAEiupScb6c7UMdjMqjo5bs2DfZnafOn2Ui/HRGwCQHItJdlY/mAzAIsLF7eeg31ciUbEI4ICACzDUvpsQHalJR4Qt6snzZ854hVMWYiHpTejAAAAwGpBsgEAACfgPknmMPIGee+fgHUZxffccnfGtqdisznRUVsOfMdXkXnuYcT74HsWBe0ra+ZuRgEwJaV6T39JvWGNuF1dWWpG+cIP3qSPrn9Gf3DTGfpguyCX6edy7H9W/R/yMbv9r/p07ePrdNOZG+i3//t+tXRenGzs0vrAfYfexcW132Xxvv7gv4eTioMNdSk8J2hbRHuuvl3B22ahGQV9NmAhSYoH82wgKNlA3K6ONH/mfq9dJxomXq+fq/ktY4u9D0g2/M2ebJgJxsBMPMaynWygGQUAIAV0ouF3wOFl05ZPrhtSs+hU2dv3uOGDndkc4p0XBtxkYm/DTQA2z/Nks4retznWi/d38nY83L26g+9CN9pJAb6Pz+Y+bRWLVKZOtIP9pQCSDQCAlNBJgzd5OPs3/yynmXUvUq3QGV+qPduBTyQFu+LMnLcZ8E1t/O5HIxKI/D5t6jv1dkjdJI8TjTztbw6M38mXi/NdftUdfDNz9FXJ00Sype7j0yNqLG+0v8RAsgGZwF9sjQsygCzi6nQ9ma786R/JyeuX//pbOf3Tv9k3GRzLr5PVLhu1E7MQScGe6mMgztLrlR6pewU6hsfUNw+25bZ9Q8HhJdrvVaiezraSkFTyJJMq98B63e0yUaUg8jR3Z9FVgGQDACBFJptEnCTkzA3uqxge+rtjOf3x376jligiWTgUB8M92lJNGmr5zIZ03FcPJ1Soo2ov5LRq7QVButtU7jdop7VDDarRxYy3Gnkh2QAASAmdaPz021+TP71ef+IP5c/rVXdC8p9/5n81Sq56SIOGRf1jPs/O07pl1FZ0D6itHtp6tH9JnY+rmooNbx6RO0cFsZXddGLInadNy2f5yhhSc7evaoZyVN1rUL+8WvcuQrIBAJACZo3Gt792i6v/hp7u/YPPy2U3rrmX//7veIp6HvdB9ilYo3ytoJo3xEGwXqF2WTWBHHAdhcmiwpFdE7KWr1Gh43fvnRK1ZH+O/Hj/dsdPse/DDhWM5XZtSok2KlnrIGr22dBjaeSpVqg7V5nIZqg2lbPeKdaAS18zhq/lLrtPRySr0RBf9JrrTEXe/pxEobO77rppGO9jd31A9aO8777MkElKPPCXWvMLacTt6VvV2JxHul47X1VyQBu48V9sslB+RVazwdeA8xvypZbTkMfzPN3QfEMtsfFgM7yct5mfz2VZQKWWbis1enmL+cPqObHW7LikeqCXWtQpGO2Hwybt9hu0J1LwoH0lzUmJxjTLj9vVtYqxuRK4ucVap7yaBfATSbJhDjbz7x9dlwWyiQsBvYx/8qh2jLfxPheWr7TToP6u3Tu6e7FGhbo5ql12IW6Tb1VjMxX0eBpl3RcBIFgkyUa0g82IQmXbae9y2vHsmozt7aJat0KDwcRtRQebiTZuVU1b02kLNy8rnBbTza4zCBKvGjZ1jLv3ETxYUoat+EBIiaauaBmN0jr6JyxTZM0ouvD1FsKhB5sRXFWk7V2jqaRH/fU9Z52uPkUJNCMMNuMVZdzK9/dow47PToV6tYvj93haTI8HShLbcOe8LVIx7tqHSEx8B0vKCsQmQJZFlmxwtbKeTKEHm2HjntJce2GyaPM8Uuj5me3i2RhshuNkEZHGLb+/+oBY2hDJcJ/kFYVsSkyPq6DlNkaMm/sIGiwpM7IXmwDgiCzZYJNVy05hPvNgM1xVLMoWe1CYATUstRzik5HBZvhANY9I4naayGJ6BQdLWvGBkACyIvJmlIUHmxkcUU/3bJYDx8ilEJvVHmwmsridJoqYDhosKdMwEBJAVkSSbJhnhgsPNlOyz2DyXFW8dUSFwLPALA4GEzezXRyDzXD8aQvH7TQzx/Q0QYMlZQViEyDLMKgXLOS044EPTNq0UEbcrp40f+aIVzBlIR4i7bMBsEyzJhoAWcEHHX3gYa9f/Wi8jCeeZ3r+TPMNuvlFe+A6t1UbFJH/Xt0Ex4/9agad98R1qboxFZtNe1tjcu3GdXm6mIq6U7P6ncZkbxe0PHuQbAAApAAnDFvrt6s524M/foeuPXOfPOO9/MQ35DzjeZ4+fvZ+eu+pe+UyMFlk9cuBB/bQo90yTjTMy9PF1CkckXPNWMB25nJ5+Xs2+yUh2QAASIErT67Ty49+Rc3ZOKG49fM3ysf33PI5+dPE9X23vvRWqqvf47JZd0anXdyQmlt8czr3AGelVsj7xXgvmc8QJBsAACngl0yYbhNJxYWH7lRzdk3IXe236YWH71ZLfAycav/g0WrNqn2nqcFmztuP7VGet+XaRMtXqV6o0VYUbUlBt9wPS95nZpOyOJwUkg0AgBT78JNPZWJRffAOet5INrg244OnC/Tcq+9TTUyTeq7Ra92j1fJZuqraH/Alx7P279CjPLfUfLKVWnzb+62QfVfMK6eMJg/jZnTOLQfM9y1gO3P5wQaNjLscZwmSDUgl/mJqXCACrCLuEMo1Gq88dpYaATUYnHQ0L19VcybLM3qtOVqtcZYuLznu0WwD1qZtlOcStToFqoUaLc7se2E0k/Sc/hm56qFYx/09TAHb6eUiqbPaB0YSki1zX/oKYFp2m3DYZANxu7rS2F9Bx6vfa+d1ejk/5o6hD9xxk5xnXNPBCYh+Dick3HHUvS9u8til9YHuY2DME3d0PKK6cUDkqzMONrhT45Ttct51ScKv7YA25N/kfZ1DahbzdFTvEJW9r3/a3yufoPgtN5fNth3XiPC9kQ7dT5oaD2mBmg0AgBTgphB90OGf+jJXTiR4Xk/cYZSvWtHzvJ4Tkpmp0WoP9Cn2sEm7bV3Tkad1y6jl4D4G6mF66dFpvfctCqNEO3LMvcWuJJE3HQzdrJMSXLMBkDYcunoCyBp64bKcFnHl15+MfvjGVTXn1RlVyBo1BmrWOz9ojKzxd8x8ntCpON+/SsXYzrvPJOHXVhH/68eTr3PQsCb/1onn8rx+X+zJMjcw3xvXuqDtJl+LfB1WQ83ZooiH0xa6GQUgCdBnA7KMayRYmqvNITpZiAc0o0DqINEAAEgXJBsAAAAQKyQbAAAAECskGwAAABArJBuQKmZ/DQAASAckG5Ba6BwKAJAOuPQVUgVXosAq0Jc6Aphw6SsAAABAANRsQKqgZgMAIH1QswGpgUQDIDv4+6wnyD4kGwAAsFRIMFYPkg0AAFgaJBqrCckGAAAshV+igSbR1YBkAwAAYodEY7Uh2YBUMAsqFFAA6YJEA5BsAABAbJBoAEOyAQAAsUCiARqSDQAAiBwSDTAh2QAAgEgh0QAvJBuQeGbBhQILINmQaIAfJBsAABAJJBoQBMkGAAAsDIkGTINkAwAAFoJEA06CZAMSza8QA4DkQKIBs0CyAamBAgwgWZBowKyQbAAAQGhINCAMJBsAABAKEg0IC8kGAADMDIkGzGNNBEmoKFlrvK4ewSoZ1R5Qj5bHLNQWLcwQt2A6jXjOAiQaMC/UbAAAwIm8iQYnGUg0YFZz12yMarfLn5Bta43/kD+zUrOBuF1tpxnPaeaXaACEgZoNAAAIhEQDooBkAwAAfCHRgKgg2YBEirIJBQDCQ6IBUUKyAQAALkg0IGqp7CD6hR9co4+uz/6y+bVe+c1ndPYW5FZhnVaHuqhrNtLWQRQxHg90ED0ZEg2IQ+pKprCFMOMCJv5C+Be0vfYdag7VLMCcTi/GEcOrDokGxCV1yUbYQjic0yxsUdCDLd4YB/CHRAPilMo616ce+rLvFLQO0iXqJpQ08otjnoLWASwCiQbELZXJxjT/df1T9egk71Kz+FXxJbOn7S7XLHyX2vQa1fJi2fYvxHO8tQ2e+eGPqKi2LzavqIWKsW5t7XvUlQvV9s3vjX9vsfmuWu793d7XJ3cAMHOMc/y44qYr4q74I+Lw7W47sWXHm9fsse/EN6QREg1YhswlG7935kb16ATdFtUKL4sv1q/k1Co9Qq3Ry1Shb1JjIJa1HlFPDCIK3/z3qdCxt9+jn4lkQeN1P6NN3g/vv0O0K5MKJhKKo0fV8sepV2uJgtrnd0+8PrU5rLxZY7xaf5zaB04i0T34CVXqT1JOPC61VGxy3LX/MmTz3bT4hjRBogHLkspk46//8T3fKWidr3yerPZ3Vc3CHIZXqG9doB2VBOSqz4pkQeF1upaCz/zKP6Heka75EAnFjkpkSo+Kbd6hY7+CftHXB6nmF8c8Ba3zxfHV/vm4Vu2g78SrrOWQtRJcoxbS1PiGtECiAcuUymSDL/Pzm4LW+co9SYfirGyP/lwWmNE3UzxOHVUrIacTa0o8Yn99yeQtAFeVXxzzFLTO3yO003hH1joMm39J/c1vyVoN2QRSJhWfr1LDkk8OacH4hlOFRAOWLXPNKB98/Jl6NJtc9e9p0Pgm9Y/9ahDO0rr1Gh0N1Gz3585ZYO4sFXrfp4sqCeDC3LWOfhJJ1fL015dtKAD9hYnx3Pk/Idpv0cX9b1C9eo+9cDCgnpWnPD8e/gPt9+RSjxNiP6L4huVDogGnIXXJxg0nnPl+6Xdn/JPG1chfpXxNF8SP0EbF7KR5j93uXbaft3ZATlMJ97PoOOu26E/c6wYXiGoPj3+Hfyc8k+d3+74+WAWRxTjLfYs2RWLQLjxK424/pW1q0Pcpz/G1NaCCb83GCbEfOr4hCZBowGlJ5QiiehTAWZ3ma027ZY+4aBaGURaEaRtBFDEej2XHc5Ig0YDTlMpkA5YHyQZkyaomG0g04LRlrs8GpFdciQbAKkOiAUmAZAMAIKOQaEBSINkAAMggJBqQJEg2AAAyBokGJA2SDQCADEGiAUk099UosFri7r1vFpBxFI6IWzBl9WoUJBqQVAtc+rp616mvomV93stKNhC3qy3LcYBEA5IMzSgAACmHRAOSDskGAECKIdGANECyAQCQUkg0IC2QbMCpi7u/BkAWIdGANEGyAQCQMkg0IG2QbAAApAgSDUijyJINvqRMX1bGXr/60XgZTzzP9PyZ5ht084tvysdxGzaL8gu63VULvLrbcn2xOVQLHCdue6Iuba9ti//14zVam9gZLy8S//rutljPz/FMxWbT3taYXLsZNqlori82yf5r1O80Jnu7oOWrRcejttS4nRJ3UqxxOS/E82niv8uERAPSIpJkgwverXX3rbsf/PE7dO2Z++T17Jef+IacZzzP08fP3k/vPXWvXDYTVfCmvwCxyOqXA/+OUmskC5DRqEMV8dzGwJ4/rJ4Ta515nlolextZMOf3adNY1ykc0UCtDtzOXN6pULusDyKTdAEetTj2OaulxG3mpTOe0wiJBqRZJMnGlSfX6eVHv6LmbFww3/r5G+Xje275nPxp4q/JrS+9JZ83i+5BWz2KQaklv7iH1ZxaEK/NeoP6u/pMbVFDam7VqNA5JPPll1otGpfBsyhtiINBn479XtRzD6oH8SYHyy48lxG3C1lyXM4rdfGcQkg0IO0iSTb8CmXTbaJwvvDQnWrOPqO8q/02vfDw3WqJyVMdKk6ZuCq2rHKNdtle5q1i9lYr63me8rWevTBIwL5m2nYe+SrVCzXaCqo+D2N4ifZ7FdoIVRL76B5Q29qk837HtRcuqwc2/d6kXZRx2yzye2I3HUi6GYCr/wObBE6w7LicV9riOWX48zYh0YA0irWD6IeffCoL6OqDd9DzRqHNZ4UfPF2g5159n2piMnW3y9Q2q0NbJVkV26nY6ysde9lUopCWhXGlI/eht53JItuGUGp1qFDbcg5OM+lRLa8PWkYVsbVOefXQOSAZB76g7czlBxs0OqxSUNnsV8B5C8GsmCduq3UOlB7tX1KJwaV9MSfCqC7e01yVDjmWeeKA6s1xYF5SXM4rbfGcFt7vmN/3ECANYks2uGMdnxm+8thZ+um//FoW3t6JNS9flT+1/Lol/rcLjcCOcyfQTS4VdXpk73M2i2wbTolanQLVLjpF5cnMtmqjWrnntGfnqodiHbePmwK208sHDbLaB0ah7c+voLML/OwkHfPGrV1tLz6K/Us0FP8u7ctUwzlD17UbuooupOXF5bzSF89Jh0QDsiS2ZIM71nEHu42vf5He/c1/q6X22eG09m5ZuKjTtl4tP3uVcxqVdqgxpXPdTHLnqLBo27Q4895r9Gl3huTOLuCjSTrM5yelIJ03bvlgu2FnG3Spy00B4nFlQx4I5RUZ+RqJI+E4tjMphfGcVEg0IGsiSTa4Slmf8fFPfbkgF9x6OePCWp8dTi24Zcc499lM0Jlc78g+Bxoccelu08/tyxJLn2XOZpFtw8tRda9B/TI3Hc2rRDsN8RnkF+t5n6vWQ1WDT0s60iLquC2JD8KiHu3vchOKOMve4VRjSMd9XmvR5vkcDe2Z0JYbl/NKbzwnCRINyKJIko3Gw3fLQlhPD9xxk2s+DOe6fNV3Y89ud+XCg5OPcQdRPovi8rddls/f7TvJCNeO2E3jebEuT2H60i2y7VzkWZjz2qcz26qdZia7NoiorJbze9dv7Bm9+f23c1OF/FZTzc8mKOHgKemijFspd542xUfZ64mgGXdOFAdg1Z+DP4OtfV4W3tLjcl4pj+fTxn+PCYkGZMWaCOZQ0azP+MIUxt6zROa3DJInzOftLSjZtPAynx93oYq4BTZPHCwLEg3IslivRvFjFtbLNznKoLs3OyzCr3DU73PaxRu3iMtV5/2OINGArFl6snG6ZxQlaokvMX+RnSnkYEEwlX5fvbyFqTnv9/ykiTduEZerDIkGrIKlJBvm4El8hmieJZ40sBKkk1+BaZ+xp6eWA3ELcUOiAatiKX02IL2i+LxPSjCWUcAiboElKQ6QaMAqWXozCqweLkRRkAI4kGjAqkGyAUsTVKCmqWkFYFFINGAVzd2MAqslymrnoOQizkIXcQum02pGQaIBqwp9NmCqOD7vk2oy4iiAEbfATjMOkGjAKkMzCpwqvwKXC+WTEhKANEGiAasOyQacuqCCFwkHZAESDQAkG7BkQQkEF8Co5YCsQaIBYEOyAafGr+CdlnQApAkSDQAHkg1IpKCEA0kHpAESDQA3JBuQWKjlgDRCogEwCclGynS37bN771RsNifuHLrNtw3tbtNasUlDe3OJ91FsDgP3lTRx1nLwpZD6ckj2+tWPxst44nmm588036CbX3xTPo4X3wm2SOJj8jdsUjFw/QnbLoT3re9Iq+5WKwPN5Pz+0PGqyb/PWD+OYfU7jcneLmj5cvHvNSHRALBFlmx84Qd2AfylVl8tcQroG0QBbbr9r/pyOW8D4ZRa9tn+aNShClnUGNjzh9VzYq0zz1OLbxtaalGnUKOLuuAVhfhuv0F71VzgvuJiFsRhf4/+m7z0gWUeHINb67erOduDP36Hrj1znxyH4fIT35DzjOd5+vjZ++m9p+6Vy07mHHQjl6vS4eiQxMd4yiyy+uXAA3voeGWcaOT3adNY1ykc0UCtDtzOXN6pULu83Nv0I9EACBZJssFJw0fXP5OP//2j67IQN/GXTi/jn9c+vi4f8zbe50L0SjsN6u/aZ4bdizUq1Kt06seoOekDjNc8CceVJ9fp5Ue/ouZsnFDc+vkb5WO/O7vyb771pbdOZVCopNqsO/G1uCE1t0SMdtyJVKkV8pb7pQ2R3PTpOI5EzwcSDYDpIkk2dKLhVwDrM0IvvXxynTobbG6Pz1q5yl9zVcuOT6fUNl2n6pVXDZtF3324q2iXe/ZzKsRZ8N7mPm0Vi1SmjnEmmF5RJBwn3Sb+NpFUXHjoTjVnJ8p3td+mFx6+Wy0xiYNkUccUxx/HZJna1KNaXiyTseqt6fDMG3FZbB6rhUEW2TZi+SrVCzXaMr9j8xpeov1ehTYWjdHuAbWtTTq/hKzaG3dINAAmRdaMopMGb/Jw9m/+WU7hiAL6aMM+i+1UqFe7OE4IXNWy7V2j4Bbb7BLtqW3a5TXaoj2ffYhC2qyi7RDtRlFIJoI6sMmDjjuJylXrVOgRNXYykGkodhy4C3ZvwT+PDz/5VCYW1QfvoOeNZINj+4OnC/Tcq+9TTUwu3YtUK3TGr6lVKlHLbDo4McPjuOQzenv7PdoXicqsFtk2GqVWhwq1rZBNRgHxaq1TXj10ThjMJC0ozo3lB6L8OIy/Bs8bb954BABbZMkGF856Ml350z+Sk9cv//W3cvqnf7M74LmJAlofFL3VodzhURYyfNZoEtvsqcJFbmPRpj6tMfcxPBaPjEKpLM49j5zW4HQz27Ld1c7d7TJRpUC1raiqu5MjygKeO4RyjcYrj52lhm8Nhp10NC9fVXNKfp2sdtldgxYGx6XVIB32nBxW7IcnW2TbyIjkqiPia9w5aBYB8dpz+mfkqodiHSdtpqA4V8sHDfFZHLiS7Tgg0QCYXWTJBptsEnGSkDM3uL+YD/3dsZz++G/tDngz4apicczsyEJmQA1LLQ+tovahpiy0K0wjErRyXxyMWjvUIKOz6BKZBXMchfL4s1xw39whlDuGbnz9i2qJU9Oh6StUXGSHTa5V2JJ/62lcCXHqSiK+pnQWnUnuHBUW7WvBzYaNfqw1lkg0AMKJvBnlp9/+mvzp9foTfyh/Xq+6E5L//LP71aMZDI6op6tYZduuXBqOLMzaGWo6OcmQmrt9VeuTo+peg/pL7qWfVNwUopMI/qmTCE44dJLME3cY5atW9LxOSPzwmfhAZMF936NlntatHo0r0rhfgXoo47LnJILD5u7sTSGLbBspHV/eWscwSrTTEJ9NfrEYlc2GoZt1ZoNEAyC8SJINs0bj21+7ZTzPP/V07x98Xi67UXxPzeW//zshXgKfOYkz87z4sq9tHVFhrpqNErUGsjSThYacMnMaarZl251iu9t5qhXqTs9+cdZXr7SpvJKn3m7cTGLG4gN33OSa1xPjq1b46pUffuvLchk/12XcvLdG+VqB6vINL9FGRX0m8v0WB+O63Z9IPveA69g0boZw1m3RZoimkEW2jZisVZj1izkZr0w2nXSIymo5N5n2G3vG1Sn+27mppGWrqeajwb/PhEQDYDZr4ssS6tvCZ3ZMF8KQbVF93mYhfRoFNOIW2CJxgEQDYH6R9tkAgKjxJa7OWbw9oRls2ZBoACwGyQbEziyoUUiHxZfQGp2Z5RRygCtYCBINgMUh2QAACIBEAyAaSDYAAHwg0QCIDpINAAADJxlINACihWQDYuUttAGSzC9ekWgALA7JBiwNCm1IMiQaAPFBsgEAKw+JBkC8kGwAwEpDogEQv8iSDR6ZT4/Ox/g+E3oZT/q+E3r+TPMNuvnFN+XjbONBmfQgTGqApomhwnm5fQvt7rY5eJMzFZvNicGdXLvhm9SZ64v67q6Tg0LZ2wUtXy06HrXlxa3zmU+Qn2XAuihM3f+U1yXx+uzEMz/PC4kGQPQiSTa44OUbVZn4ZlXXnrlPDgvMN63iecbzPH387P303lP3ymWrxSJryp0xSy09cBPfVtu5lfZh9ZxYa95ae0Tjm9VywZzfp01jXafg3KY7cDtzuby3RrQjU5oFOf+OpEls3Mo7yB4a9wIJwznQB1po/17pjWckGgDLE0mywTeo4htVmbhg5rtlsntu+Zz8aeKv9K0vvSWft2o26w3q7+oztUUNqblVo0LHffAotUKOMlnaEAeDBW/tnTKI22hkJZ6RaADEJ5Jkw69QNt0mCucLD92p5uwzyrvab9MLD9+tljiaRU/1J99NU1ehuqpWnbMWV1XteGP7DG97uyifmyj5KtULNdqKop5c3mq/QhuhSmIffLtza5POR3K2mw5Rxq08SIrY1XFoh6G3lsGn1mHgxLRz91LP8wLifvJ38nZ8e3fzTrN+gvdfbB6rhSFkIJ6RaADEK9YOoh9+8qksoKsP3kHPG4U2nxV+8HSBnnv1faqJySRvwX3gFJLdgzZV6lXKcQFpVq12iHZV4eaqqm3vGoV5j/rre2J5S80nR6nVoUJty33gOZF5a23joGOtU149HDY5ueL15kEtYDtz+cEGjQ75fYZ54pa6F6lW6Kg4NKv2pxHv/y7RHm8zkPdD94mHoLjnRCNP+5sD43fyfVSM5oqZXgTvn2sS7H3s0b5IVsJLYzzr940nAIhXbMkGd6zjM8NXHjtLDd8zQbvwbl6+quYUrv5sH6gCpEsH/QbtcJk5PKa+WZiUxfnbkWrF5doPWfDwWZ3Jos3EnqqLA0OnQLWLTlF5MrOt2qhW7jnt2bnqoVjHBxxTwHZ6uTjQWeP3fLXNHbf5dfEelo3aiVmI939PHRBzVapXeqRDeiwo7lUNQH3Rjhe8f0t9x4Rcte6JnVkhngEgWGzJBnes4w52G1//olrinDFquqe/W4l2Gn159jZs7lJ/87xxdlKhjixg1MRnblwFXCa1fEANSz01DUo71JjSuW4muXNUWLSvhTjQ7an3PCp8YNT4s0qLueNWdrrkmoEt+beH/0yHdNxXDyf4xH0SZTieAWAxkSQbXKWsC2P+qQtjLrh5Xk/c8Y57/+t5XbB75c5vEu1fpIv75NRMyEKoPVmADI6op6td5dmeXJoSOaruNahf9tbIhMHJmfgM8mZ1cnh8Rhu+Gjzdoo5bxmfiA5Hx9uXRMk/rllFbwf0I1ENbj/YvqTc8qK9CUNznztOm5bM8LN5/r0a6QoIT/PljEfEMAAHEmVIo9MJlOS3iyq8/Gf3wjatqzl+nQiOqiPM506AxssRL5pctJ7l+MBJluz1vVUYVyxo1BvzkzqhC+vFp4tdREf/rx5OvSRycxOv3Lvc+l+eNv11MlrlBpxKwLmi7ydciX4fVUHO2eT9v8/clQexx63r/9ectmMsrIj7H77n9/lcq/Nnb651w93w2vnHP3J+tXiy/O67neXn2b7xGq9Fwr5vA265ePAPAYtb4P/GFnRmf2bG4L/3jK0wONmbtaAdxmffzTlozyrLiNhp8tcgBbbj6I0AU0hUHANkR69Uocxs2aVd3DAVYNfKyTeeKjPlx0qI6lo6nxZonAADmkbhkQ44bwJf66V76kDpJq9VIDT3eRbnvXKWyEL4U1uhYKifUlgDA8iUu2agecoEY1VDKACmirmhB/ANA1iSzGQUAAAAyA8kGAAAAxArJBkTK7K8BAADAkGxAbNA5FAAAGJINAAAAiBWSDQAAAIhVZMkGj8ynR+djfJ8JvYwnfd8JPX+m+Qbd/OKb8rEbD0Rk3k7aIMch0OumPC9R+HXqgZTUIEsTd6py/hYeOdU9CJM9FZtNe1tjcu1Gj9Ggp2KT7LdG/U5jsrcLWg7RcD5TCBY63vkOz+PYtvE++G67QfsCgNMXSbLBCQPfqMrEN6u69sx9clhgvmkVzzOe5+njZ++n9566Vy6bmRyHIMwYBEks8C2yptwZs9TSgy/xbbWdW2kfVs+JteattY2h3DnR4IHQjHWdgnOb7sDtzOWdCrXLi40uaRbsvM80+MIP7IT3Sy3nlqs6Ib5BJMSm2/+qL5fzNtFAQhI63kstEdvOjeP0aMN7olAI2hcAnL5Iko0rT67Ty49+Rc3ZOKHgu2Wye275nPxp4iLg1pfeWsl7FGzWG9TfdZ+dzW9Iza0aFTruJKzUCjlSZGlDFNAL3to7ZThp+Oj6Z/Lxv390XSYSJj5Q6WX889rH1+Vj3sb7XFie0o7z/eleFLFfx2jDAEkXSbLhl0yYbhNJxYWH7lRzdsF9V/tteuHhu9USHwOnWYCrSG1TzgRdzQh8hs7P5Vtd96iWF8uS1EaQr1JdnJ1tRXFKG3Rr8rDk/Tg2Sd/RfxXoRMMv4dU1cF56ud86MwaLzWO10Oaq4rfbA/zjcyKOYUKuSnub+7RVLFKZOrhZI0AKxNpB9MNPPpWJRfXBO+h5I9nggvqDpwv03KvvU01Mk0QBvEu0x1WigwZRbeuEqmZRcJvNCB2i3WaeWmZ1asJKpFKrQ4UT/y4vdWDyHoiMm3YNm0W13kzKArYzlx9s0Ohw9c4QddLgTR7O/s0/y2l2HINcw2TH4B7ti0TC4arib++Kz4bvW+KNT784jiAhTa2guOV8o06FHlEDd2sESIXYkg3uEMo1Gq88dpYaATUYXMA3L19VcyZRAOsbUYmzmHqlR0dOB4RJw2PqmwVTWZwvTt0gCcTBplOg2rjxeRZmG7bRTNJz+mfkqof2AU3N2wK208tFQme1D1yF+argZFhPpit/+kdy8vrlv/5WTv/0b3aH5zGOQcu5UzEfDF2fAXdslAdNrs0IkMo4jlNQ3PLbWSaqiO/PVlTNkQAQp9iSDe4Qyh1DN77+RbXEqenQ9BUq0w3p2Om7N0WFOrJQUlMa6lZLO9SY0ll0JrlzVFi0rwVXSzf6C51F88FR4/c/TfyaRHQCcuYG99UMD/3dsZz++G/tDs8z4aYRcWy043NADUst95XCOF42kbiV+yKxa4nvDxmdRQEgsSJJNrgpRCcR/FMnEZxw6EKbJ+4wylet6HmdkEzq0f4ldeCbpU+CPOC2U1jlnKPqXoP65Slnuycq0Q63NOUXa9+X1dKhm3XSTycaP/321+RPr9ef+EP583rVnZD855/drx4pHIM958A3bO46n+ngiHq6qUvGs1w6KbVxvExDau7qW/Dr7w/6tgAknjh7CoVeuCynRVz59SejH75xVc15dUYVskaVisWnx3KqiFM9m72uMfA+FgaNkThhHG9DaqNOxT2/fPw6K+J//dh4zcqgwX+rd7n3uTxv/H1isswNOpWAdUHbTb4W+Tqshpqzzfp5m/tPoiji9kTGZ2A1Gsb7Oxg1LPX+WJVRxXLe94n4DIjj7Jst3uX75XlPJpdNxra2lDgAgAlr/J/4Is+MaySYX9UzZM+sn3fSm1EQt8AQBwCnI9arUQAAAACQbMDC0tw5FAAA4odkAwAAAGKFZAMAAABihWQDAAAAYoVkAxZi9tcAAADwg2QDIoPOoQAA4AfJBgAAAMQKyQYAAADEKrJkg0fm06PzMb4/il7Gk75fip4/03yDbn7xTfk48fhGWq5btpu6tB24jvF6fe8GfrxGaxN3XnP20d1Wd/z0TMVm097WmFy7ka/RWF/Ud8NUv9OY7O2Clq8WHY/a8uLW+czThV834hkAwokk2eCCl2+wZuKbrF175j45LDDfbI3nGc/z9PGz99N7T90rlyVerkqHo0OqynveL8oia8qdXkstfcdPvk28c4vtw+o5sda85faIxjcE5YI5v0+bxrpOwbntfOB25vJOhdohb2jFBbrG+0ibzMftUmQnngEgPpEkG1eeXKeXH/2KmrNxwcx3eWX33PI5+dPEh6ZbX3pLPm/VbNYb1N/VZ2qLGlJzq0aFjjsZKrVaNO1GuRNKG+JgsOCt6lMGcRsNxDMAnCSSZMOvUDbdJgrnCw/dqebsM8q72m/TCw/frZZ4GFWodnWrrm62q2e3t4tinTpr8VS3OmdYTlWuzZxXj7vm75lWKnn25Xp9x2phCPkq1Qs12pr6O2c0yy34Z9E9oLa1Secjqb1Jhyjjtln0VNt3t8dV/66mBN8qgGmxKrhiPIFn64hnADhBrB1EP/zkU1lAVx+8g543Cm0+K/zg6QI99+r7VBOTmyho83xmY1eH7tE+tdUaW4/663tiHZ/puJ87GogzrLJZaE/To9ou0Z7ajmpbM2530uubTanVocLMv1MTrznvc9Cx1imvHg6bnIjxevN9CNjOXH6wQaPDKoUpm/nvz6J54rZar1D7wHlnuwdtqtTt99PVlNDeDfmZc7wZTQodot1wO1iKLMQzAMQntmSDO9bxmeErj52lRkANBhfezctX1ZwyPKa+1aAddWaTq9apYj9ULNrUpyv8XLF2fBaUE2dYlR4dOY27U1jU2FOFUZjtTnx9sypRq1Og2kWnqDyZ2VZtVCv3nPbsXPXQPqipeVvAdnq5SLas9oFRaM/O3md2ko6545ar7cfvYZcO+k6MyFoOeWAsh09MZYwbB9FyW3zcMwX4kmUjngEgHrElG9yxjjvYbXz9i2qJc8ao6Z7+p29Ix331cJlKO9SY0rluJrlzVFi0bVokW3uNfiLPmJdt/rgt0Y56D4fNXepvnrcTWW4CKRN15IFxQA1LPjmkitpeTU6PyGRBPANAgEiSDa5S1oUx/9SFMRfcPK8n7njHvf/1vC7YXbiw6dVInyBxwR14NigLpjaNa69Fwb7b1jUdeVq3jNoKbsNVD2092r+kCqMw7cRhXt+JclTd46afOc54x/ggJz6D/GJt+VxDE74aPN0ijVshd36TaP8iXdwvUF33bhwcUU83C8g4k0s9psSqivF0HDgRzwDgL5Jkg6ubuWpZTw/ccZNrXk+Me//zVQA//NaX5TJ+rhtXx/Jla3a18RZtTmmmEM+V/TRUFbNs29bVqqLg43Z0ve6Azw9NFhWOttR23AfDrI6dJszrm4E8C5v1dNdsq3Y6tcqq5g5RWS3n6vp+Y8/oze+/nZsq5Leaaj77oo1bIXdeREOb2oUNJ5b4bJ9qlOf3fuuICr4f9bRYtWNcfIDjz8+/k2lCIJ4BwMfaiOtlQ+AzO6YL4dhxNXT+iOquttlFcW//XVofRDV2RnYt/fOOSVb+DlgM4gDgdMR6NUoUuhdrTjV07DgJcc6Y7Gmx6lwAAIBVl8BkYyjHLNAH+3K7Qp2lXcJWopbZEU9OUdaoAAAArJ4EJhs5qh7GfbDnpAJNKAAAAMuQ+GYUAAAASDckGwAAABArJBsAAAAQq7kvfYXVkpVLXwEYLn0FWC7UbAAAAECsQtdsAAAAAISBmg0AAACIFZINAAAAiBWSDQAAAIgVkg0AAACIFZINAAAAiBWSDQAAAIgVkg0AAACIFZINAAAAiBWSDQAAAIgVkg0AAACIFZINAAAAiBWSDQAAAIgVkg0AAACIFZINAAAAiBWSDQAAAIgVkg0AAACIFZINAAAAiBWSDQAAAIgVkg0AAACIFZINAAAAiBWSDQAAAIgVkg0AAACIFZINAAAAiBWSDQAAAIgVkg0AAACIFZINAAAAiBWSDQAAAIgVkg0AAACIFZINAAAAiBWSDQAAAIgVkg0AAACIFZINAAAAiBHR/wdo4CTenbIrGAAAAABJRU5ErkJggg==)"
      ],
      "metadata": {
        "id": "BikfEVDGBd20"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "Задание 3:\n",
        "\n",
        "Напишите запрос, который покзыает самого (или самых, если их несколько) полохи студентов каждой группе"
      ],
      "metadata": {
        "id": "YpyYR7SqObHN"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "import sqlite3\n",
        "\n",
        "# Устанавливаем соединение с базой данных\n",
        "connection = sqlite3.connect('uni.db')\n",
        "cursor = connection.cursor()\n",
        "\n",
        "# Выбираем всех\n",
        "cursor.execute(\"SELECT students.name, min(value.value), students.group_id FROM students JOIN value ON students.stud_id = value.stud_id GROUP BY students.group_id\")\n",
        "users = cursor.fetchall()\n",
        "\n",
        "# Выводим результаты\n",
        "for name in users:\n",
        "  print(name)\n",
        "\n",
        "# Закрываем соединениеimport sqlite3\n",
        "\n",
        "# Закрываем соединение\n",
        "connection.close()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "sBEaZv5xDRi1",
        "outputId": "4c997436-5aec-4528-8b2d-8f73a79dc3f4"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "('Sam', 3, 1)\n",
            "('Ben', 3, 2)\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "Задание 4:\n",
        "Создайте бекап этой базы данных"
      ],
      "metadata": {
        "id": "24rxHiE6OlgX"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "with sq.connect(\"uni.db\") as con:\n",
        "    cur = con.cursor()\n",
        "\n",
        "    for sql in con.iterdump():\n",
        "        print(sql)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "BvEAaOYVGXxe",
        "outputId": "6d7b241a-d9da-46c7-9336-78c6974a2f82"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "BEGIN TRANSACTION;\n",
            "CREATE TABLE control (\n",
            "            control_id INTEGER PRIMARY KEY AUTOINCREMENT,\n",
            "            art TEXT);\n",
            "INSERT INTO \"control\" VALUES(1,'Exam');\n",
            "INSERT INTO \"control\" VALUES(2,'Zachet');\n",
            "INSERT INTO \"control\" VALUES(3,'Test');\n",
            "CREATE TABLE groups (\n",
            "            group_id INTEGER PRIMARY KEY AUTOINCREMENT,\n",
            "            name TEXT);\n",
            "INSERT INTO \"groups\" VALUES(1,'Mathematics');\n",
            "INSERT INTO \"groups\" VALUES(2,'Informatics');\n",
            "CREATE TABLE students (\n",
            "            stud_id INTEGER PRIMARY KEY AUTOINCREMENT,\n",
            "            name TEXT,\n",
            "            year INTEGER,\n",
            "            bilet INTEGER,\n",
            "            group_id INTEGER);\n",
            "INSERT INTO \"students\" VALUES(1,'Andi',2001,52642,1);\n",
            "INSERT INTO \"students\" VALUES(2,'Maria',2002,57127,1);\n",
            "INSERT INTO \"students\" VALUES(3,'Sam',2001,90020,1);\n",
            "INSERT INTO \"students\" VALUES(4,'Vova',2003,29000,2);\n",
            "INSERT INTO \"students\" VALUES(5,'Ben',2000,35000,2);\n",
            "CREATE TABLE subject (\n",
            "            subject_id INTEGER PRIMARY KEY AUTOINCREMENT,\n",
            "            subject TEXT,\n",
            "            hour INTEGER);\n",
            "INSERT INTO \"subject\" VALUES(1,'Math',100);\n",
            "INSERT INTO \"subject\" VALUES(2,'Info',80);\n",
            "INSERT INTO \"subject\" VALUES(3,'Physic',50);\n",
            "CREATE TABLE value (\n",
            "            val_id INTEGER PRIMARY KEY AUTOINCREMENT,\n",
            "            stud_id INTEGER,\n",
            "            subject_id INTEGER,\n",
            "            date TEXT,\n",
            "            value INTEGER);\n",
            "INSERT INTO \"value\" VALUES(1,1,1,'2024-5-7',5);\n",
            "INSERT INTO \"value\" VALUES(2,1,2,'2024-6-7',4);\n",
            "INSERT INTO \"value\" VALUES(3,3,3,'2024-5-17',3);\n",
            "INSERT INTO \"value\" VALUES(4,2,1,'2024-5-27',4);\n",
            "INSERT INTO \"value\" VALUES(5,2,2,'2024-6-1',3);\n",
            "INSERT INTO \"value\" VALUES(6,1,1,'2024-5-7',5);\n",
            "INSERT INTO \"value\" VALUES(7,1,2,'2024-6-7',4);\n",
            "INSERT INTO \"value\" VALUES(8,3,3,'2024-5-17',3);\n",
            "INSERT INTO \"value\" VALUES(9,2,1,'2024-5-27',4);\n",
            "INSERT INTO \"value\" VALUES(10,2,2,'2024-6-1',3);\n",
            "INSERT INTO \"value\" VALUES(11,1,1,'2024-5-7',5);\n",
            "INSERT INTO \"value\" VALUES(12,1,2,'2024-6-7',4);\n",
            "INSERT INTO \"value\" VALUES(13,3,3,'2024-5-17',3);\n",
            "INSERT INTO \"value\" VALUES(14,2,1,'2024-5-27',4);\n",
            "INSERT INTO \"value\" VALUES(15,5,2,'2024-6-1',3);\n",
            "DELETE FROM \"sqlite_sequence\";\n",
            "INSERT INTO \"sqlite_sequence\" VALUES('value',15);\n",
            "INSERT INTO \"sqlite_sequence\" VALUES('students',5);\n",
            "INSERT INTO \"sqlite_sequence\" VALUES('groups',2);\n",
            "INSERT INTO \"sqlite_sequence\" VALUES('subject',3);\n",
            "INSERT INTO \"sqlite_sequence\" VALUES('control',3);\n",
            "COMMIT;\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "#сохраним все эти строчки в отдельном файле:\n",
        "\n",
        "with open(\"sql_damp.sql\", \"w\") as f:\n",
        "    for sql in con.iterdump():\n",
        "         f.write(sql)"
      ],
      "metadata": {
        "id": "Vtv5yF0bGXxm"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "Задание 5:\n",
        "\n",
        "Удалите все таблици и восстановите их из бекапа"
      ],
      "metadata": {
        "id": "IGmq_6KYOrbA"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "with sq.connect(\"uni.db\") as con:\n",
        "    cur = con.cursor()"
      ],
      "metadata": {
        "id": "d2pSJWvfIGgv"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        " #чтобы восстановить БД с помощью этого файла можно воспользоваться методом executescript, о котором мы уже говорили:\n",
        "\n",
        "with open(\"sql_damp.sql\", \"r\") as f:\n",
        "     sql = f.read()\n",
        "     cur.executescript(sql)"
      ],
      "metadata": {
        "id": "lAVP6pxAHvN_"
      },
      "execution_count": null,
      "outputs": []
    }
  ]
}