jupyter_russian/tutorials/Oracle_and_Pandas.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Эти заметки я поначалу делал для себя, а потом решил выложить их в качестве туториала - будут рад, если кому-то это покажется полезным или интересным. \n",
    "Все дальнейшие примеры рассмотрены на примере бесплатной СУБД Oracle Database 11g Express Edition, которую можно найти на [oracle.com](http://www.oracle.com/technetwork/database/database-technologies/express-edition/downloads/index.html) и при желании скачать, предварительно зарегистрировавшись. \n",
    "В заключении рассмотрены вопросы быстродействия при загрузке данных из pandas.DataFrame в БД Oracle (проведено сравнение с загрузкой в MS SQL Server, данные и программный код для сравнения взяты из [тюториала Олега (@Oleg)](https://github.com/Yorko/mlcourse_open/blob/master/jupyter_russian/tutorials/Pandas_and_SQL_server_oleg.ipynb) с разрешения автора) "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Oracle и macOS"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Прежде всего хотелось бы рассмотреть один из способов установки сервера Oracle в macOS. Пользователи других ОС могут переходить к следующему пункту - для них установка OracleXE не должна вызвать особых сложностей. \n",
    "\n",
    "Для начала понадобится скачать и установить Docker — программное обеспечение для автоматизации развёртывания и управления приложениями в виртуальной среде. Отмечу, что данный вариант не требователен к ресурсам и подойдет для Mac, выпущеных не ранее 2010 года и всё ещё работающих на OS X El Capitan 10.11 или более новых версиях macOs.  \n",
    "\n",
    "Скачать стабильную сборку Docker можно [по ссылке](https://download.docker.com/mac/stable/Docker.dmg). Устанавливаем приложение стандартым способом и запускаем.\n",
    "\n",
    "Далее необходимо использовать официальные Docker images для Oracle Software на github - клонируем git репозиторий командой: \n",
    "\n",
    "**git clone https://github.com/oracle/docker-images.git** \n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "После чего скачаем [OracleXE для Linux](http://www.oracle.com/technetwork/database/database-technologies/express-edition/downloads/index.html)\n",
    "и скопируем zip-архив OracleXE в каталог ../docker-images/OracleDatabases/dockerfiles/11.2.0.2 клонированного git репозитория:\n",
    "\n",
    "![title](../../img/finder.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Билдим Oracle XE Docker Image запуском скрипта из каталога dockerfiles: \n",
    "\n",
    "**./buildDockerImage.sh -v 11.2.0.2 -x -i** \n",
    "\n",
    "Убедимся, что образ Oracle создан - выполним команду **docker images**:"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "![title](../../img/bash.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Запустим новый контейнер:\n",
    "\n",
    "**docker run --name OracleXE --shm-size=1g -p 1521:1521 -p 8080:8080 -e ORACLE_PWD=weblogic1 oracle/database:11.2.0.2-xe** "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "в результате в логах увидим\n",
    "\n",
    "**DATABASE IS READY TO USE!**\n",
    "\n",
    "(для пользователей SYS и SYSTEM установится переданный в ORACLE_PWD пароль weblogic1) "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Увидеть список запущенных контейнеров можно командой: \n",
    "\n",
    "**docker ps**\n",
    "\n",
    "Остановить контейнер:\n",
    "\n",
    "**docker stop OracleXE**\n",
    "\n",
    "Запустить контейнер:\n",
    "\n",
    "**docker start OracleXE**\n",
    "\n",
    "Просмотреть логи:\n",
    "\n",
    "**docker logs OracleXE**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Уже теперь можно скачать SQLDeveloper или Aqua Data Studio, подключиться к БД (system/weblogic1@localhost/xe) и всё должно работать, но для работы с БД в Jupyter Notebook (запуска sql*plus) необходимо установить Instant Client Package - Basic (+ Instant Client Package - SQL*Plus) [отсюда](http://www.oracle.com/technetwork/topics/intel-macsoft-096467.html)\n",
    "Подробности установки описаны [здесь](https://oracle.github.io/odpi/doc/installation.html#macos)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Подробнее о работе с Docker в macOs можно почитать [на сайте](https://docs.docker.com/docker-for-mac/)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Подключаемся к БД "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Шлюзом между базой данных и языком Python является объект **Connection**. Именно его мы создадим в первую очередь после импорта библиотеки **cx_Oracle**: вызовем метод **connect**, передав в него параметры подключения в виде строки \"пользователь/пароль@хост/SID\". Полученный объект Connection будет в дальнейшем использоваться при создании курсоров для доступа к БД. \n",
    "\n",
    "Все действия будем проводить на демонстрационной БД, которая идет в составе OracleXE, предварительно разблокировав учетную запись пользователя HR - владельца схемы HR.\n",
    "\n",
    "При успешном подключении будет выведен номер версии БД (атрибут **version** объекта Connection)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import cx_Oracle\n",
    "\n",
    "db = cx_Oracle.connect(\"system/weblogic1@localhost/xe\")\n",
    "cursor = db.cursor()\n",
    "cursor.execute(\"ALTER USER HR ACCOUNT UNLOCK IDENTIFIED BY HR\")\n",
    "db.close()\n",
    "db = cx_Oracle.connect(\"HR/HR@localhost/xe\")\n",
    "print(db.version)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Работаем с БД"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Рассмотрим способы получения данных из таблиц БД. Все они основаны на работе с курсорами. Под курсором в Oracle понимается получаемый при выполнении запроса результирующий набор и связанный с ним указатель текущей записи. Простым программам достаточно одного курсора, который можно использовать снова и снова, для более крупных проектов могут потребоваться несколько отдельных курсоров. В библиотеке cx_Oracle курсор создается методом cursor() объекта Connection. \n",
    "\n",
    "Можно выделить 3 этапа обработки операторов SQL, каждому из которых соответствуют методы cx_Oracle:\n",
    "\n",
    "1) Разбор (parsing)\n",
    "\n",
    "2) Выполнение (execute)\n",
    "\n",
    "3) Извлечение (fetch)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Первый этап** не является обязательным, т.к. все операторы автоматически парсятся на этапе выполнения. Метод parsing() можно использовать для предварительной проверки корректности оператора SQL (в случае ошибки возникнет исключение DatabaseError с соответствующим сообщением): "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor = db.cursor()\n",
    "cursor.parse(\"select * from departments order by department_id\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Второй этап** - непосредственное выполнение оператора SQL - представлен метод курсора execute(). После выполнения запроса к таблице БД, удобно использовать курсор в качестве итератора в цикле for для последовательного доступа к возвращаемым записям, которые представлены в виде набор кортежей (тюплов или таплов - кому как нравится). Каждому кортежу соответствует одна запись в таблице hr.departments. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute(\"select * from departments order by department_id\")\n",
    "for result in cursor:\n",
    "    print(result)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Третий этап** - извлечение данных - не является обязательным, используется только для запросов (поскольку операторы DDL и DCL не возвращают результаты) и представлен несколькими fetch-методами. На курсоре, который не выполнил запрос, fetch-методы вызовут исключение InterfaceError. \n",
    "\n",
    "Метод курсора **fetchone()** возвращает одну текущую запись из результирующего набора данных. При дальнейших вызовах этого метода будут возвращаться следующие записи последовательно. Eсли все данные из курсора были ранее \"извлечены\", метод вернёт None."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute(\"select * from departments order by department_id\")\n",
    "row = cursor.fetchone()\n",
    "print(row)\n",
    "row = cursor.fetchone()\n",
    "print(row)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Метод **fetchmany()** возвращает список кортежей - ровно столько записей, сколько было передано в параметре **numRows** (или пустой список, если все записи из курсора были выбраны ранее):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from pprint import pprint\n",
    "\n",
    "res = cursor.fetchmany(numRows=3)\n",
    "pprint(res)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Метод **fetchall()** возвращает все оставшиеся записи открытого курсора:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "res = cursor.fetchall()\n",
    "pprint(res)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Обычно используется тот или иной fetch-метод - в зависимости от дальнейших действий над возвращаемыми данными, либо записи последовательно обрабатываются в цикле, когда курсор служит итератором. \n",
    "\n",
    "Если в курсоре используется запрос, то атрибут **description** отображает структуру запрашиваемых данных: выдается список кортежей, где каждый кортеж состоит из имени столбца, типа столбца, видимого размера, внутреннего размера, точности, масштаба и возможно ли null-значение.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pprint(cursor.description)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Как известно, в запросах часто используются условия. В качестве подставляемых параметров запроса рекомендуется использовать связываемые переменные. cx_Oracle поддерживает передачу связываемых переменных по имени (query1 и query2) или по позиции (query3). "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "named_params = {\"dept_id\": 50, \"sal\": 8000}\n",
    "\n",
    "query1 = cursor.execute(\n",
    "    \"SELECT FIRST_NAME, LAST_NAME FROM hr.employees WHERE department_id = :dept_id AND salary > :sal\",\n",
    "    named_params,\n",
    ")\n",
    "pprint(query1.fetchall())\n",
    "\n",
    "query2 = cursor.execute(\n",
    "    \"SELECT FIRST_NAME, LAST_NAME FROM hr.employees WHERE department_id = :dept_id AND salary > :sal\",\n",
    "    dept_id=50,\n",
    "    sal=8000,\n",
    ")\n",
    "pprint(query2.fetchall())\n",
    "\n",
    "query3 = cursor.execute(\n",
    "    \"SELECT * FROM hr.locations WHERE country_id=:1 AND city=:2\", (\"US\", \"Seattle\")\n",
    ")\n",
    "pprint(query3.fetchall())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "При работе со связываемыми переменными можно сначала подготовить (**prepare()**) запрос, а потом выполнить его. Подготовленные операторы можно выполнять многократно."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.prepare(\"SELECT * FROM jobs WHERE min_salary > : min\")\n",
    "r = cursor.execute(None, {\"min\": 9000})\n",
    "pprint(r.fetchall())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "В cx_Oracle существует возможность пакетной обработки операторов SQL при помощи метода **executemany()**, что часто применяется при добавлении данных в таблицы. Вначале необходимо подготовить последовательность (в нашем случае - список), затем передать в качестве параметра методу executemany(). Создадим таблицу, заполним её данными при помощи executemany(), подсчитаем количество строк, а потом удалим таблицу:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "create_table = \"\"\"\n",
    "CREATE TABLE python_modules (\n",
    "module_name VARCHAR2(50) NOT NULL,\n",
    "file_path VARCHAR2(300) NOT NULL\n",
    ")\n",
    "\"\"\"\n",
    "cursor.execute(create_table)\n",
    "\n",
    "from sys import modules\n",
    "\n",
    "M = []\n",
    "for m_name, m_info in modules.items():\n",
    "    try:\n",
    "        M.append((m_name, m_info.__file__))\n",
    "    except AttributeError:\n",
    "        pass\n",
    "\n",
    "cursor.prepare(\"INSERT INTO python_modules(module_name, file_path) VALUES (:1, :2)\")\n",
    "cursor.executemany(None, M)\n",
    "db.commit()\n",
    "r = cursor.execute(\"SELECT COUNT(*) FROM python_modules\")\n",
    "print(cursor.fetchone())\n",
    "\n",
    "cursor.execute(\"DROP TABLE python_modules PURGE\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Работаем с Pandas"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Библиотека Pandas позволяет загружать данные из таблиц Oracle непосредственно в DataFrame, для чего используется метод **read_sql()**:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "df_ora = pd.read_sql(\"select * from employees\", con=db)\n",
    "df_ora[:5]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "При этом также можно использовать связываемые переменные в запросах:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_ora = pd.read_sql(\n",
    "    \"select * from hr.employees where EMPLOYEE_ID=:myempno\",\n",
    "    params={\"myempno\": 110},\n",
    "    con=db,\n",
    ")\n",
    "df_ora"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "И конечно же всеми любимая визуализация данных: достаточно одного взгляда на график, чтобы оценить уровень зарплаты сотрудников"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import seaborn as sns\n",
    "\n",
    "%matplotlib inline\n",
    "df_ora = pd.read_sql('select LAST_NAME \"Name\", SALARY \"Salary\" from employees', con=db)\n",
    "df_ora.plot(\n",
    "    x=\"Name\",\n",
    "    y=\"Salary\",\n",
    "    title=\"Salary details, from Oracle demo table\",\n",
    "    figsize=(16, 8),\n",
    "    kind=\"bar\",\n",
    "    color=\"blue\",\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Оценка быстродействия при загрузке данных в БД"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Для загрузки данных в БД можно использовать стандартный метод Pandas.DataFrame **to_sql()**. В параметре con этого метода передается объект **Engine** библиотеки **SQLAlchemy**, поэтому сначала импортируем эту библиотеку, определим параметры подключения к БД, создадим объект **Engine**:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy import create_engine\n",
    "\n",
    "#######################################################\n",
    "### DB connection strings config\n",
    "#######################################################\n",
    "tns = \"\"\"\n",
    "  (DESCRIPTION =\n",
    "    (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))\n",
    "    (CONNECT_DATA =\n",
    "      (SERVER = DEDICATED)\n",
    "      (SERVICE_NAME = XE)\n",
    "    )\n",
    "  )\n",
    "\"\"\"\n",
    "\n",
    "usr = \"HR\"\n",
    "pwd = \"HR\"\n",
    "\n",
    "engine = create_engine(\"oracle+cx_oracle://%s:%s@%s\" % (usr, pwd, tns))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Чтобы было с чем сравнивать, я решил воспользоваться прекрасным примером из [тюториала Олега (@Oleg)](https://github.com/Yorko/mlcourse_open/blob/master/jupyter_russian/tutorials/Pandas_and_SQL_server_oleg.ipynb). Следующие пять ячеек заимствованы мной у него практически без изменений, за что ему огромное спасибо). Предварительно нужно скачать файлы [excel](https://drive.google.com/open?id=1UlcTpCWSDMVirX8YU-GOuYKkN2dOdXym) в подкаталоги Production и Prices. В результате будут сформированы 2 датафрейма: df_prices с даннами о ценах на электричество (966840 строк) и df_production с данными о плановом производстве электричества (179760 строк)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "\n",
    "path_production = r\"Production\"\n",
    "path_prices = r\"Prices\"\n",
    "list_files_production = os.listdir(path_production)\n",
    "list_files_prices = os.listdir(path_prices)\n",
    "\n",
    "files_production = [f for f in list_files_production if f[-3:] == \"xls\"]\n",
    "files_prices = [f for f in list_files_prices if f[-3:] == \"xls\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Функция предобработки файлов с ценами\n",
    "def Prices_preprocessing(path, f):\n",
    "    df_list = []  ## list с DataFrame по каждому часу\n",
    "    for i in range(24):\n",
    "        df = pd.read_excel(path + \"//\" + f, sheet_name=i)  # Считываем файл в DataFrame\n",
    "        df.drop(\n",
    "            [0, 1], inplace=True\n",
    "        )  # Вырезаем первые две строки, не содержащие ничего полезного\n",
    "        df.drop(\n",
    "            df.columns[[1, 2, 3, 5]], axis=1, inplace=True\n",
    "        )  # Удаляем лишние столбцы, оставляем\n",
    "        df.columns = [\n",
    "            \"ID_node\",\n",
    "            \"price\",\n",
    "        ]  # Для красоты переименуем столбцы с ID узлов и ценами\n",
    "\n",
    "        df[\"_datetime\"] = f[:8]  # Вырезаем из названия дату\n",
    "        df[\"_datetime\"] = df[\"_datetime\"].apply(\n",
    "            lambda x: datetime.strptime(x, \"%Y%m%d\").replace(hour=i)\n",
    "        )\n",
    "        df_list.append(df)\n",
    "    df = pd.concat(\n",
    "        df_list, axis=0, ignore_index=True\n",
    "    )  # Соединим данные с разных страниц в один DataFrame\n",
    "    df.fillna(0, inplace=True)  # Заполним \"дырки\" нулями\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import time\n",
    "from datetime import datetime\n",
    "\n",
    "# Соединение данных в один DataFrame\n",
    "start = time.time()\n",
    "df_prices = []\n",
    "for f in files_prices:\n",
    "    df_prices.append(Prices_preprocessing(path_prices, f))\n",
    "df_prices = pd.concat(df_prices, axis=0, ignore_index=True)\n",
    "\n",
    "time_prices_preprocessing = time.time() - start\n",
    "print(\"Time of Prices preprocessing = \", round(time_prices_preprocessing, 1))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def func(x):\n",
    "    return datetime.strptime(x[0], \"%Y%m%d\").replace(hour=int(x[1]))\n",
    "\n",
    "\n",
    "# Функция предобработки данных по объёму производства\n",
    "def Production_preprocessing(path, f):\n",
    "    df = pd.read_excel(path + \"//\" + f)\n",
    "    df.drop(\n",
    "        df.columns[\n",
    "            [\n",
    "                1,\n",
    "                3,\n",
    "                4,\n",
    "                6,\n",
    "                7,\n",
    "                9,\n",
    "                10,\n",
    "                12,\n",
    "                13,\n",
    "                15,\n",
    "                16,\n",
    "                18,\n",
    "                19,\n",
    "                21,\n",
    "                22,\n",
    "                24,\n",
    "                25,\n",
    "                27,\n",
    "                28,\n",
    "                30,\n",
    "                31,\n",
    "                33,\n",
    "                34,\n",
    "                36,\n",
    "                37,\n",
    "                39,\n",
    "                40,\n",
    "                42,\n",
    "                43,\n",
    "                45,\n",
    "                46,\n",
    "                48,\n",
    "                49,\n",
    "                51,\n",
    "                52,\n",
    "                54,\n",
    "                55,\n",
    "                57,\n",
    "                58,\n",
    "                60,\n",
    "                61,\n",
    "                63,\n",
    "                64,\n",
    "                66,\n",
    "                67,\n",
    "                69,\n",
    "                70,\n",
    "                72,\n",
    "                73,\n",
    "                75,\n",
    "                76,\n",
    "                77,\n",
    "                78,\n",
    "            ]\n",
    "        ],\n",
    "        axis=1,\n",
    "        inplace=True,\n",
    "    )  # Вырезаем ненужные столбцы\n",
    "    df.drop([0, 1, 2, 3, 4, 5], inplace=True)  # Отрезаем верхние лишние строки\n",
    "    df.drop(df.tail(1).index, inplace=True)  # Отрезаем строку \"Итого\"\n",
    "    df.columns = [\"ID_unit\", \"ID_node\"] + [\n",
    "        x for x in range(24)\n",
    "    ]  # Переименуем столбцы, в том числе данным по производству дадим номер соответствующего часа\n",
    "    df[\"_datetime\"] = f[:8]  # Вырезаем из названия дату\n",
    "    df = pd.melt(\n",
    "        df,\n",
    "        id_vars=[\"ID_unit\", \"ID_node\", \"_datetime\"],\n",
    "        value_vars=[x for x in range(24)],\n",
    "    )  # Превратим данные из столбцов по часам в строки (unpivot)\n",
    "    df.rename(columns={\"value\": \"production\", \"variable\": \"hour\"}, inplace=True)\n",
    "    df[\"_datetime\"] = df[[\"_datetime\", \"hour\"]].apply(\n",
    "        func, axis=1\n",
    "    )  # Склеиваем дату и время\n",
    "    df.drop([\"hour\"], axis=1, inplace=True)  # Убираем ненужный уже столбец \"hour\"\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "start = time.time()\n",
    "df_production = []\n",
    "for f in files_production:\n",
    "    df_production.append(Production_preprocessing(path_production, f))\n",
    "df_production = pd.concat(df_production, axis=0, ignore_index=True)\n",
    "\n",
    "time_production_preprocessing = time.time() - start\n",
    "print(\"Time of Production preprocessing = \", round(time_production_preprocessing, 1))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ноутбук у меня старенький, поэтому препроцессинг занял больше времени, чем у Олега. Вполне возможно, что и загрузка данных отнимет немало времени, ведь и установка Oracle в виртуальном контейнере Докера, как будто бы не должна способствовать быстродействию..."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "В начале используем **первый способ** загрузки - с помощью команды **Insert**:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# cursor.execute('drop table Production purge')\n",
    "\n",
    "create_table = \"\"\"\n",
    "CREATE TABLE Production (\n",
    "ID_unit NUMBER(20) NOT NULL,\n",
    "ID_node NUMBER(20) NOT NULL,\n",
    "datetime DATE NOT NULL,\n",
    "production NUMBER(30) NOT NULL\n",
    ")\n",
    "\"\"\"\n",
    "cursor.execute(create_table)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "start = time.time()\n",
    "\n",
    "cursor.prepare(\"INSERT INTO Production VALUES (:1, :2, :3, :4)\")\n",
    "cursor.executemany(None, df_production.values.tolist())\n",
    "db.commit()\n",
    "\n",
    "time_production_commit = time.time() - start\n",
    "print(time_production_commit)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# cursor.execute('drop table Price purge')\n",
    "\n",
    "create_table = \"\"\"\n",
    "CREATE TABLE Price (\n",
    "ID_node NUMBER(20) NOT NULL,\n",
    "price float NOT NULL,\n",
    "datetime DATE NOT NULL\n",
    ")\n",
    "\"\"\"\n",
    "cursor.execute(create_table)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "start = time.time()\n",
    "\n",
    "cursor.prepare(\"INSERT INTO Price VALUES (:1, :2, :3)\")\n",
    "cursor.executemany(None, df_prices.values.tolist())\n",
    "db.commit()\n",
    "\n",
    "time_price_commit = time.time() - start\n",
    "print(time_price_commit)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Результаты немного удивляют - практически в 10 раз быстрее, чем на MS SQL Server. \n",
    "\n",
    "Проверим загрузку **вторым способом** - методом **pandas.DataFrame.to_sql()**. При его использовании не нужно создавать таблицы БД, делать Commit - всё происходит автоматически."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%time\n",
    "df_production.to_sql(\"production2\", engine, index=False, if_exists=\"replace\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%time\n",
    "df_prices.to_sql(\"prices2\", engine, index=False, if_exists=\"replace\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Вторым способом получили немного худшее быстродействие. Обратим внимание на то, что типы столбцов у создаваемых таблиц определяются автоматически на основании типов столбцов у DataFrame. В нашем примере для столбца production у результирующей таблицы выбрался тип \"CLOB\" (Character Large Object), работа с таким типом отрицательно сказывается на быстродействии, поэтому лучше его заменить на обычный FLOAT."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_production.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Передадим методу to_sql в параметре **dtype** требуемый тип для столбца production и повторим загрузку"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%time\n",
    "df_production.to_sql(\n",
    "    \"production3\",\n",
    "    engine,\n",
    "    index=False,\n",
    "    if_exists=\"replace\",\n",
    "    dtype={\"production\": types.FLOAT},\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Видим, что время загрузки уменьшилось и уже сопоставимо со временем загрузки первого метода (insert)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Графики цен и планового производства"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Из любопытства посмотрим на графики цен и планового производства Саяно-Шушенской ГЭС, где плановое производство электроэнергии в первые дни 2015 года было максимальным"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_prices[df_prices[\"ID_node\"] == 1001068].plot(\n",
    "    x=\"_datetime\", y=\"price\", figsize=(16, 8)\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_production[df_production[\"ID_node\"] == 1001068].plot(\n",
    "    x=\"_datetime\", y=\"production\", figsize=(16, 8)\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "И на графики ТЭЦ ОАО \"ППГХО\" в Забайкальском крае с самой дешевой ценой в первые дни 2015 года"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_prices[df_prices[\"ID_node\"] == 1001163].plot(\n",
    "    x=\"_datetime\", y=\"price\", figsize=(16, 8)\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_production[df_production[\"ID_node\"] == 1001163].plot(\n",
    "    x=\"_datetime\", y=\"production\", figsize=(16, 8)\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ну и просто цены и плановое производство электричества в масштабах страны, так сказать:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_prices.plot(x=\"_datetime\", y=\"price\", figsize=(16, 8))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_production.plot(x=\"_datetime\", y=\"production\", figsize=(16, 8))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "В конце работы рекомендуется закрывать открытые курсоры и подключения к БД (а также выключать компьютеры с целью экономии электроэнергии)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.close()\n",
    "db.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "!sudo shutdown"
   ]
  }
 ],
 "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.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}