Lessons/Базы данных/12. Базы данных.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Базы данных\n",
    "\n",
    "**Реляционная база данных** - это набор взаимосвязанных таблиц, в которых хранятся различные данные. **Строка** таблицы (row) содержит данные об одном объекте (например, о студенте), а **столбцы** таблицы (columns) описывают различные характеристики этих объектов — атрибутов (например, имя, курс, специальность, номер группы). Каждый столбец описывает только одну характеристику объекта и имеет *строго определенный тип данных*. Все стоки (записи) имеют одни и те же поля, только в них отображаются различная информация в зависимости от объекта. Вот так:\n",
    "\n",
    "|ФИО (строка)| Специальность (строка)| Курс (число)|\n",
    "| ------------- |-------------:| -----:|\n",
    "|Петя Иванов| филология| 1|\n",
    "|Вася Петров| физика| 2|\n",
    "|Маша Сидорова| биология| 3|\n",
    "\n",
    "В реляционной базе данных каждая таблица должна иметь **первичный ключ** (primary key) — поле или комбинацию полей, которые единственным образом идентифицируют каждую строку таблицы. Если ключ состоит из нескольких полей, он называется составным. Ключ должен быть уникальным и однозначно определять запись. По значению ключа можно отыскать единственную запись. Ключи служат также для упорядочивания информации в БД. По умолчанию это по умолчанию создающееся пое Id с порядковой нумерацией.\n",
    "\n",
    "Таблицы реляционной БД должны отвечать требованиям нормализации отношений. Нормализация отношений — это формальный аппарат ограничений на формирование таблиц, который позволяет устранить дублирование, обеспечивает непротиворечивость хранимых в базе данных, уменьшает трудозатраты на ведение базы данных.\n",
    "\n",
    "*Реляционные таблицы могут быть связаны друг с другом*, следовательно, данные могут извлекаться одновременно из нескольких таблиц. Таблицы связываются между собой для того, чтобы в конечном счете уменьшить объем БД. Связь каждой пары таблиц обеспечивается при наличии в них одинаковых столбцов.\n",
    "\n",
    "Существуют следующие типы информационных связей:\n",
    "* один-к-одному (one-to-one)\n",
    "* один-ко-многим (one-to-many)\n",
    "* многие-ко-многим (many-to-many)\n",
    "\n",
    "Связь **один-к-одному** предполагает, что одному атрибуту первой таблицы соответствует только один атрибут второй таблицы и наоборот. Связь **один-ко-многим** - это когда одному атрибуту первой таблицы соответствует несколько атрибутов второй таблицы. Связь **многие-ко-многим** - это когда одному атрибуту первой таблицы соответствует несколько атрибутов второй таблицы и наоборот. \n",
    "\n",
    "Для работы с БД используются специальные программы - они называются **Системами управления базами данных**, или **СУБД** (англ. *Database Management System, DBMS*). Существует множество СУБД, вот некоторые из них:\n",
    "\n",
    "* SQLite\n",
    "* MySQL\n",
    "* PostgreSQL\n",
    "* MongoDB\n",
    "* ...\n",
    "\n",
    "## SQL\n",
    "\n",
    "В названии нескольких СУБД встретилось сочетание SQL. Что это такое?\n",
    "\n",
    "**SQL** *(Structured Query Language)* - это особый язык для управления данными в БД. С помощью него можно добавлять, удалять, изменять и выбирать данные в таблицах. **Любое** обращение к базе данных называется **запросом**.\n",
    "\n",
    "SQL - очень простой язык. Нам потребуется лишь несколько команд для операций с данными (CREATE, DELETE, DROP, SELECT, INSERT, UPDATE) и команд-ограничителей для создания более точных запросов (WHERE, IN, AND, OR, NOT, BETWEEN, LIKE, LIMIT, OFFSET). Стоит обратить внимание, что порядок слов в запросе фиксирован: сначала идет \"что\", затем \"где\" и, наконец, \"с каким условием\".\n",
    "\n",
    "Для тренировки мы будем использовать интерактивный тьюториал SQLBolt.\n",
    "\n",
    "SELECT упражнения\n",
    "\n",
    "* https://sqlbolt.com/lesson/select_queries_introduction - основы (уроки 1-5, 8)\n",
    "* https://sqlbolt.com/lesson/select_queries_with_joins - выбор данных из нескольких таблиц (уроки 6-7)\n",
    "* https://sqlbolt.com/lesson/select_queries_with_expressions - работа с математическими выражениями\n",
    "\n",
    "\n",
    "DML упражнения\n",
    "\n",
    "* https://sqlbolt.com/lesson/inserting_rows\n",
    "* https://sqlbolt.com/lesson/updating_rows\n",
    "* https://sqlbolt.com/lesson/deleting_rows\n",
    "\n",
    "Создание и удаление таблиц (там же описаны типы данных)\n",
    "\n",
    "* https://sqlbolt.com/lesson/creating_tables (уроки 16-18)\n",
    "\n",
    "\n",
    "## Программы для работы с базами данных\n",
    "\n",
    "База данных -- это не текстовый формат, так что просто открыть его в блокноте и посмотреть, что внутри, не получится. Для этого существуют специальные программы.\n",
    "\n",
    "* [MySQL](https://www.mysql.com/)\n",
    "* [PostgreSQL](https://www.postgresql.org/)\n",
    "* [MongoDB](https://www.mongodb.com/)\n",
    "* [Firebird](https://firebirdsql.org/)\n",
    "\n",
    "[Вот тут](https://blog.capterra.com/free-database-software/) есть неплохое описание плюсов и минусов всего вышеперечисленного. \n",
    "\n",
    "\n",
    "## Базы данных и Python\n",
    "\n",
    "Питон позволяет работать с различными СУБД, но проще всего использовать встроенную библиотеку [SQLite](https://docs.python.org/3.5/library/sqlite3.html). Важно также запомнить, что файлы баз данных имеют расширение `.db`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "\n",
    "# подключаемся к базе данных\n",
    "conn = sqlite3.connect('example.db')\n",
    "\n",
    "# создаем объект \"курсор\", которому будем передавать запросы\n",
    "c = conn.cursor()\n",
    "\n",
    "# создаем таблицу\n",
    "c.execute(\"CREATE TABLE IF NOT EXISTS students(name text, major text, year integer)\")\n",
    "\n",
    "# вставляем строку\n",
    "c.execute(\"INSERT INTO students VALUES ('Петя Иванов','филология',1), ('Маша Петрова','история',4)\")\n",
    "\n",
    "# сохраняем изменения\n",
    "conn.commit()\n",
    "\n",
    "# отключаемся от БД\n",
    "#conn.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Важная вещь №1**: после подключения к БД нужно обязательно инициализировать курсор, иначе вы не сможете делать к ней запросы!\n",
    "\n",
    "**Важная вещь №2**: если вы что-то изменили, нужно обязательно закоммитить изменения, иначе они не сохранятся в файле БД!\n",
    "\n",
    "И немного о безопасности: при создании запроса нельзя использовать конкатенацию строк и форматирование строк, как в питоне. Это сделает ваше приложение уязвимым для SQL-инъекций - особых хакерских атак, которые заключаются в подставлении в запрос нежелательных комманд - например, DROP TABLE. Поподробнее об этом можно почитать [вот здесь](https://habrahabr.ru/post/148151/).\n",
    "\n",
    "![](https://imgs.xkcd.com/comics/exploits_of_a_mom.png)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('Петя Иванов', 'филология', 1)\n"
     ]
    }
   ],
   "source": [
    "# Так нельзя!\n",
    "name = 'Петя'\n",
    "c.execute(\"SELECT * FROM students WHERE name = '%s'\" % name)\n",
    "\n",
    "# Вот как надо\n",
    "x = ('Петя Иванов',)\n",
    "c.execute('SELECT * FROM students WHERE name=?', x)\n",
    "print(c.fetchone())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('a', 1234.56, 4567.8)\n",
      "('u', 1111.1, 3333.3)\n",
      "('a', 1234.56, 4567.8)\n",
      "('u', 1111.1, 3333.3)\n"
     ]
    }
   ],
   "source": [
    "# Если результатом запроса является несколько строк, можно по ним итерировать\n",
    "\n",
    "for row in c.execute('SELECT * FROM students ORDER BY year'):\n",
    "    print(row)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "# как подставить несколько переменных в sql-запрос\n",
    "\n",
    "x = 'Вася Пупкин'\n",
    "y = 'математика'\n",
    "z = 3\n",
    "\n",
    "c.execute('INSERT INTO students VALUES (?, ?, ?)', (x, y, z))\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Форматирование строк\n",
    "\n",
    "Если нужно подставить переменные в качестве названия таблицы или колонок, то придется использовать форматирование строк."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x18620759ab0>"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "params = ['vowel', 'f1', 'f2']\n",
    "c.execute('CREATE TABLE vowels({}, {}, {})'.format(params[0], params[1], params[2]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [],
   "source": [
    "# как написать длинный запрос посимпатичнее\n",
    "c.execute('''\n",
    "INSERT INTO vowels \n",
    "VALUES \n",
    "('a', 1234.56, 4567.8), \n",
    "('u', 1111.1, 3333.3)'''\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('a', 1234.56, 4567.8)\n",
      "('u', 1111.1, 3333.3)\n",
      "('a', 1234.56, 4567.8)\n",
      "('u', 1111.1, 3333.3)\n"
     ]
    }
   ],
   "source": [
    "for row in c.execute('SELECT * FROM vowels'):\n",
    "    print(row)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Функции курсора\n",
    "\n",
    "* **fetchone()** -- возвращает следующий элемент из результата запроса (т.е. одну строку из бд). Результат -- кортеж, где элементом является значение каждой из колонок или None\n",
    "* **fetchall()** -- возвращает все результаты запроса в виде списка\n",
    "* **fetchmany()** -- взвращает заданное количество строк из результатов запроса"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('Петя Иванов', 'филология', 1)\n",
      "('Вася Пупкин', 'математика', 3)\n",
      "('Маша Петрова', 'история', 4)\n"
     ]
    }
   ],
   "source": [
    "# извлекаем строки по одной\n",
    "# обратите внимание, что после каждого вызова fetchone возвращает следующую строку!\n",
    "c.execute('SELECT * FROM students ORDER BY year')\n",
    "print(c.fetchone())\n",
    "print(c.fetchone())\n",
    "print(c.fetchone())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[('Петя Иванов', 'филология', 1), ('Вася Пупкин', 'математика', 3)]\n"
     ]
    }
   ],
   "source": [
    "# извлекаем две строки\n",
    "c.execute('SELECT * FROM students ORDER BY year')\n",
    "print(c.fetchmany(2))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[('Петя Иванов', 'филология', 1), ('Вася Пупкин', 'математика', 3), ('Маша Петрова', 'история', 4)]\n"
     ]
    }
   ],
   "source": [
    "# извлекаем все строки\n",
    "c.execute('SELECT * FROM students ORDER BY year')\n",
    "print(c.fetchall())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Задание\n",
    "\n",
    "1. Помните [таблицу с нанайскими гласными](https://github.com/ancatmara/learnpython2018/blob/master/Lessons/11/nanai-vowels.csv)? Давайте сделаем из не базу данных с помощью `sqlite3`.\n",
    "2. Теперь более реальная задача: возьмите за основу код сайта-анкеты из последнего домашнего задания и перепишите обработку данных. Ответы пользователя должны сохраняться не в сsv-таблицу, в базу данных и извлекаться из нее для отображения."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}