smart-iss-posts/notebooks/test_mysql.ipynb

333 lines
9.2 KiB
Plaintext

{
"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
}