{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "os.chdir(os.getcwd() + '/..')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "%load_ext autoreload\n",
    "%autoreload 2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "from iss.data.DataBaseManager import DataBaseManager"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Collecting MySQL-connector-python\n",
      "\u001b[?25l  Downloading https://files.pythonhosted.org/packages/f7/59/c2220c52d747da492f2aed108cdf99b640b88cf89dbbe2ea13a8c04201aa/mysql_connector_python-8.0.18-cp36-cp36m-manylinux1_x86_64.whl (16.1MB)\n",
      "\u001b[K    100% |████████████████████████████████| 16.1MB 4.1MB/s \n",
      "\u001b[?25hRequirement already satisfied: protobuf>=3.0.0 in /opt/conda/lib/python3.6/site-packages (from MySQL-connector-python) (3.6.1)\n",
      "Requirement already satisfied: six>=1.9 in /opt/conda/lib/python3.6/site-packages (from protobuf>=3.0.0->MySQL-connector-python) (1.12.0)\n",
      "Requirement already satisfied: setuptools in /opt/conda/lib/python3.6/site-packages (from protobuf>=3.0.0->MySQL-connector-python) (40.8.0)\n",
      "Installing collected packages: MySQL-connector-python\n",
      "Successfully installed MySQL-connector-python-8.0.18\n"
     ]
    }
   ],
   "source": [
    "!pip install MySQL-connector-python"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "import mysql.connector\n",
    "from iss.tools.config import Config\n",
    "import pandas as pd\n",
    "import datetime as dt\n",
    "import time\n",
    "import numpy as np\n",
    "from iss.data.DataBaseManager import DataBaseManager"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "ename": "TypeError",
     "evalue": "__init__() missing 2 required positional arguments: 'project_dir' and 'mode'",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mTypeError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-6-e6f50bbb757a>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0mcfg\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mConfig\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;31mTypeError\u001b[0m: __init__() missing 2 required positional arguments: 'project_dir' and 'mode'"
     ]
    }
   ],
   "source": [
    "cfg = Config()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "mydb = mysql.connector.connect(\n",
    "    host = cfg.get('mysql')['database']['server'],\n",
    "    user = cfg.get('mysql')['database']['user'],\n",
    "    passwd = cfg.get('mysql')['database']['password'],\n",
    "    database = cfg.get('mysql')['database']['name'],\n",
    "    port = cfg.get('mysql')['database']['port']\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "history = pd.read_csv(\"./data/raw/history/history.txt\", sep=\";\", names=['latitude', 'longitude', 'id', 'location'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    2018-05-13 15:40:01\n",
       "1    2018-05-13 16:00:01\n",
       "2    2018-05-13 17:20:01\n",
       "3    2018-05-13 17:40:01\n",
       "4    2018-05-13 18:40:01\n",
       "Name: id, dtype: object"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.to_datetime(history.id, format=\"%Y%m%d-%H%M%S\").dt.strftime(\"%Y-%m-%d %H:%M:%S\").head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "history['timestamp'] = pd.to_datetime(history.id, format=\"%Y%m%d-%H%M%S\").dt.strftime(\"%Y-%m-%d %H:%M:%S\")\n",
    "history.fillna('NULL', inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "history = history[['latitude', 'longitude', 'id', 'timestamp', 'location']]\n",
    "history_tuple = [tuple(x) for x in history.values]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('NULL', 'NULL', '20190228-180001', '2019-02-28 18:00:01', 'NULL'),\n",
       " ('NULL', 'NULL', '20190301-020001', '2019-03-01 02:00:01', 'NULL'),\n",
       " (-50.906848280632,\n",
       "  -129.67152170575,\n",
       "  '20190301-040001',\n",
       "  '2019-03-01 04:00:01',\n",
       "  'South Pacific Ocean'),\n",
       " (46.224697680948,\n",
       "  38.508590038953,\n",
       "  '20190301-080001',\n",
       "  '2019-03-01 08:00:01',\n",
       "  'Russia'),\n",
       " (48.272215894114,\n",
       "  -115.83481468293002,\n",
       "  '20190301-140001',\n",
       "  '2019-03-01 14:00:01',\n",
       "  'United States'),\n",
       " (1.6470165175667,\n",
       "  -29.550114288583003,\n",
       "  '20190301-160001',\n",
       "  '2019-03-01 16:00:01',\n",
       "  'North Atlantic Ocean'),\n",
       " (32.938297820693,\n",
       "  -150.40851274645001,\n",
       "  '20190301-220001',\n",
       "  '2019-03-01 22:00:01',\n",
       "  'North Pacific Ocean'),\n",
       " (-44.190661647858,\n",
       "  -100.34112806661,\n",
       "  '20190302-000001',\n",
       "  '2019-03-02 00:00:01',\n",
       "  'South Pacific Ocean'),\n",
       " (-16.432920558626,\n",
       "  131.67746802656,\n",
       "  '20190302-060001',\n",
       "  '2019-03-02 06:00:01',\n",
       "  'Australia'),\n",
       " (15.985374697618001,\n",
       "  13.727633100791,\n",
       "  '20190302-120001',\n",
       "  '2019-03-02 12:00:01',\n",
       "  'Niger'),\n",
       " (-33.152964317853,\n",
       "  -64.290916318981,\n",
       "  '20190302-200001',\n",
       "  '2019-03-02 20:00:01',\n",
       "  'Argentina'),\n",
       " (-1.9700946508908002,\n",
       "  174.83004739548,\n",
       "  '20190303-020001',\n",
       "  '2019-03-03 02:00:01',\n",
       "  'South Pacific Ocean'),\n",
       " (30.034749186781003,\n",
       "  54.744294350939995,\n",
       "  '20190303-080001',\n",
       "  '2019-03-03 08:00:01',\n",
       "  'Iran'),\n",
       " (-46.295462250407,\n",
       "  106.72882908036999,\n",
       "  '20190303-100001',\n",
       "  '2019-03-03 10:00:01',\n",
       "  'Indian Ocean'),\n",
       " (50.937467506234,\n",
       "  -85.532585394465,\n",
       "  '20190303-140001',\n",
       "  '2019-03-03 14:00:01',\n",
       "  'Canada'),\n",
       " (-19.534658922649,\n",
       "  -23.957895905650002,\n",
       "  '20190303-160001',\n",
       "  '2019-03-03 16:00:01',\n",
       "  'South Atlantic Ocean'),\n",
       " (12.864013306513,\n",
       "  -142.02470134325,\n",
       "  '20190303-220001',\n",
       "  '2019-03-03 22:00:01',\n",
       "  'North Pacific Ocean'),\n",
       " (-51.795754684285,\n",
       "  -72.822090610609,\n",
       "  '20190304-000001',\n",
       "  '2019-03-04 00:00:01',\n",
       "  'South Pacific Ocean'),\n",
       " (-35.859523196283,\n",
       "  141.0150238206,\n",
       "  '20190304-060001',\n",
       "  '2019-03-04 06:00:01',\n",
       "  'Australia'),\n",
       " (-5.2636362719691,\n",
       "  19.004947687717,\n",
       "  '20190304-120001',\n",
       "  '2019-03-04 12:00:01',\n",
       "  'Democratic Republic of the Congo')]"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "history_tuple[3850:]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "dbm = DataBaseManager(mydb, cfg)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "dbm.createPicturesTable(force=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3870"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dbm.insertRowPictures(history_tuple)"
   ]
  }
 ],
 "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.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}