d04-stdlib-bazy-danych-rozwiazania.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Python Biblioteka Standardowa - bazy danych - zadania"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "from datetime import datetime, timezone\n",
    "import sqlite3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Database SQLite Iris\n",
    "\n",
    "1. Użyj danych z sekcji \"Input\" (patrz poniżej)\n",
    "1. Zapisz dane wejściowe do pliku ``database-sqlite-iris.csv``\n",
    "1. Wczytaj dane z pliku (nie używaj biblioteki ``csv`` lub ``pandas``)\n",
    "1. Połącz się do bazy danych ``sqlite3`` używając context managera (``with``)\n",
    "1. Stwórz tabelę ``iris`` o kolumnach podanych w sekcji \"Input\" (patrz poniżej)\n",
    "1. Podmień ``int`` na ``str`` zgodnie z tabelą podstawień ``SPECIES`` (patrz dane wejściowe)\n",
    "1. Zapisz dane do tabeli w bazie danych\n",
    "1. Wypisz wyniki z bazy danych ``SELECT * FROM iris ORDER BY datetime DESC``\n",
    "\n",
    "\n",
    "Input:\n",
    "```text\n",
    "4.3,3.0,1.1,0.1,0\n",
    "5.8,4.0,1.2,0.2,0\n",
    "5.7,4.4,1.5,0.4,1\n",
    "5.4,3.9,1.3,0.4,2\n",
    "5.1,3.5,1.4,0.3,1\n",
    "5.7,3.8,1.7,0.3,0\n",
    "5.1,3.8,1.5,0.3,0\n",
    "5.4,3.4,1.7,0.2,1\n",
    "5.1,3.7,1.5,0.4,0\n",
    "4.6,3.6,1.0,0.2,0\n",
    "5.1,3.3,1.7,0.5,2\n",
    "4.8,3.4,1.9,0.2,0\n",
    "5.0,3.0,1.6,0.2,1\n",
    "5.0,3.4,1.6,0.4,2\n",
    "5.2,3.5,1.5,0.2,1\n",
    "5.2,3.4,1.4,0.2,2\n",
    "4.7,3.2,1.6,0.2,0\n",
    "```\n",
    "\n",
    "```python\n",
    "SPECIES = {\n",
    "    0: 'setosa',\n",
    "    1: 'versicolor',\n",
    "    2: 'virginica',\n",
    "}\n",
    "````\n",
    "\n",
    "```sql\n",
    "CREATE TABLE IF NOT EXISTS iris (\n",
    "    id INTEGER PRIMARY KEY AUTOINCREMENT,\n",
    "    species TEXT,\n",
    "    datetime DATETIME,\n",
    "    sepal_length REAL,\n",
    "    sepal_width REAL,\n",
    "    petal_length REAL,\n",
    "    petal_width REAL\n",
    ");\n",
    "```\n",
    "\n",
    "```sql\n",
    "INSERT INTO iris VALUES (\n",
    "    NULL,\n",
    "    :species,\n",
    "    :datetime,\n",
    "    :sepal_length,\n",
    "    :sepal_width,\n",
    "    :petal_length,\n",
    "    :petal_width\n",
    ");\n",
    "```\n",
    "\n",
    "```sql\n",
    "SELECT * FROM iris ORDER BY datetime DESC\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "FILE = 'database-sqlite-iris.csv'\n",
    "DATABASE = '_temporary.sqlite3'\n",
    "\n",
    "SQL_CREATE_TABLE = \"\"\"\n",
    "    CREATE TABLE IF NOT EXISTS iris (\n",
    "        id INTEGER PRIMARY KEY AUTOINCREMENT,\n",
    "        species TEXT,\n",
    "        datetime DATETIME,\n",
    "        sepal_length REAL,\n",
    "        sepal_width REAL,\n",
    "        petal_length REAL,\n",
    "        petal_width REAL\n",
    "    );\"\"\"\n",
    "\n",
    "SQL_INSERT = \"\"\"\n",
    "    INSERT INTO iris VALUES (\n",
    "        NULL,\n",
    "        :species,\n",
    "        :datetime,\n",
    "        :sepal_length,\n",
    "        :sepal_width,\n",
    "        :petal_length,\n",
    "        :petal_width\n",
    "    );\"\"\"\n",
    "\n",
    "SQL_SELECT = \"\"\"\n",
    "    SELECT * FROM iris ORDER BY datetime DESC\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "DATA = \"\"\"4.3,3.0,1.1,0.1,0\n",
    "5.8,4.0,1.2,0.2,0\n",
    "5.7,4.4,1.5,0.4,1\n",
    "5.4,3.9,1.3,0.4,2\n",
    "5.1,3.5,1.4,0.3,1\n",
    "5.7,3.8,1.7,0.3,0\n",
    "5.1,3.8,1.5,0.3,0\n",
    "5.4,3.4,1.7,0.2,1\n",
    "5.1,3.7,1.5,0.4,0\n",
    "4.6,3.6,1.0,0.2,0\n",
    "5.1,3.3,1.7,0.5,2\n",
    "4.8,3.4,1.9,0.2,0\n",
    "5.0,3.0,1.6,0.2,1\n",
    "5.0,3.4,1.6,0.4,2\n",
    "5.2,3.5,1.5,0.2,1\n",
    "5.2,3.4,1.4,0.2,2\n",
    "4.7,3.2,1.6,0.2,0\"\"\"\n",
    "\n",
    "with open(FILE, mode='w') as file:\n",
    "    file.write(DATA)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'id': 17, 'species': 'setosa', 'datetime': '2020-10-11 21:52:03.399904+00:00', 'sepal_length': 4.7, 'sepal_width': 3.2, 'petal_length': 1.6, 'petal_width': 0.2}\n",
      "{'id': 16, 'species': 'virginica', 'datetime': '2020-10-11 21:52:03.399844+00:00', 'sepal_length': 5.2, 'sepal_width': 3.4, 'petal_length': 1.4, 'petal_width': 0.2}\n",
      "{'id': 15, 'species': 'versicolor', 'datetime': '2020-10-11 21:52:03.399830+00:00', 'sepal_length': 5.2, 'sepal_width': 3.5, 'petal_length': 1.5, 'petal_width': 0.2}\n",
      "{'id': 14, 'species': 'virginica', 'datetime': '2020-10-11 21:52:03.399817+00:00', 'sepal_length': 5.0, 'sepal_width': 3.4, 'petal_length': 1.6, 'petal_width': 0.4}\n",
      "{'id': 13, 'species': 'versicolor', 'datetime': '2020-10-11 21:52:03.399803+00:00', 'sepal_length': 5.0, 'sepal_width': 3.0, 'petal_length': 1.6, 'petal_width': 0.2}\n",
      "{'id': 12, 'species': 'setosa', 'datetime': '2020-10-11 21:52:03.399782+00:00', 'sepal_length': 4.8, 'sepal_width': 3.4, 'petal_length': 1.9, 'petal_width': 0.2}\n",
      "{'id': 11, 'species': 'virginica', 'datetime': '2020-10-11 21:52:03.399738+00:00', 'sepal_length': 5.1, 'sepal_width': 3.3, 'petal_length': 1.7, 'petal_width': 0.5}\n",
      "{'id': 10, 'species': 'setosa', 'datetime': '2020-10-11 21:52:03.399682+00:00', 'sepal_length': 4.6, 'sepal_width': 3.6, 'petal_length': 1.0, 'petal_width': 0.2}\n",
      "{'id': 9, 'species': 'setosa', 'datetime': '2020-10-11 21:52:03.399669+00:00', 'sepal_length': 5.1, 'sepal_width': 3.7, 'petal_length': 1.5, 'petal_width': 0.4}\n",
      "{'id': 8, 'species': 'versicolor', 'datetime': '2020-10-11 21:52:03.399662+00:00', 'sepal_length': 5.4, 'sepal_width': 3.4, 'petal_length': 1.7, 'petal_width': 0.2}\n",
      "{'id': 7, 'species': 'setosa', 'datetime': '2020-10-11 21:52:03.399654+00:00', 'sepal_length': 5.1, 'sepal_width': 3.8, 'petal_length': 1.5, 'petal_width': 0.3}\n",
      "{'id': 6, 'species': 'setosa', 'datetime': '2020-10-11 21:52:03.399647+00:00', 'sepal_length': 5.7, 'sepal_width': 3.8, 'petal_length': 1.7, 'petal_width': 0.3}\n",
      "{'id': 5, 'species': 'versicolor', 'datetime': '2020-10-11 21:52:03.399638+00:00', 'sepal_length': 5.1, 'sepal_width': 3.5, 'petal_length': 1.4, 'petal_width': 0.3}\n",
      "{'id': 4, 'species': 'virginica', 'datetime': '2020-10-11 21:52:03.399630+00:00', 'sepal_length': 5.4, 'sepal_width': 3.9, 'petal_length': 1.3, 'petal_width': 0.4}\n",
      "{'id': 3, 'species': 'versicolor', 'datetime': '2020-10-11 21:52:03.399610+00:00', 'sepal_length': 5.7, 'sepal_width': 4.4, 'petal_length': 1.5, 'petal_width': 0.4}\n",
      "{'id': 2, 'species': 'setosa', 'datetime': '2020-10-11 21:52:03.399545+00:00', 'sepal_length': 5.8, 'sepal_width': 4.0, 'petal_length': 1.2, 'petal_width': 0.2}\n",
      "{'id': 1, 'species': 'setosa', 'datetime': '2020-10-11 21:52:03.399468+00:00', 'sepal_length': 4.3, 'sepal_width': 3.0, 'petal_length': 1.1, 'petal_width': 0.1}\n"
     ]
    }
   ],
   "source": [
    "SPECIES = {\n",
    "    0: 'setosa',\n",
    "    1: 'versicolor',\n",
    "    2: 'virginica',\n",
    "}\n",
    "\n",
    "result = []\n",
    "\n",
    "with open(FILE) as file:\n",
    "    for line in file:\n",
    "        line = [float(x) for x in line.strip().split(',')]\n",
    "        result.append({\n",
    "            'species': SPECIES.get(line[4]),\n",
    "            'datetime': datetime.now(timezone.utc),\n",
    "            'sepal_length': line[0],\n",
    "            'sepal_width': line[1],\n",
    "            'petal_length': line[2],\n",
    "            'petal_width': line[3],\n",
    "        })\n",
    "        \n",
    "with sqlite3.connect(DATABASE) as db:\n",
    "    db.row_factory = sqlite3.Row\n",
    "    db.execute(SQL_CREATE_TABLE)\n",
    "    db.executemany(SQL_INSERT, result)\n",
    "    \n",
    "    for row in db.execute(SQL_SELECT):\n",
    "        print(dict(row))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Database SQLite Logs\n",
    "\n",
    "1. Użyj danych z sekcji \"Input\" (patrz poniżej)\n",
    "1. Zapisz dane wejściowe do pliku ``apollo11-timeline.log``\n",
    "1. Wyciągnij obiekt ``datetime``, poziom logowania oraz wiadomość z każdej linii\n",
    "1. Zbierz dane do ``DATA: List[dict]`` (patrz sekcja input)\n",
    "1. Stwórz schemat bazy danych dla logów\n",
    "1. Dodaj wszystkie linie do bazy danych\n",
    "1. Wybierz tylko logi ``WARNING`` z przedziału 1969-07-20 i 1969-07-21\n",
    "1. Posortuj logi w kolejności datetime malejąco\n",
    "1. Wyświetl ``result: List[dict]``\n",
    "1. Porównaj wyniki z sekcją \"Output\" (patrz poniżej)\n",
    "\n",
    "Źródło:\n",
    "* Apollo 11 timeline https://history.nasa.gov/SP-4029/Apollo_11i_Timeline.htm\n",
    "\n",
    "Output:\n",
    "```python\n",
    "result: List[dict] = [\n",
    "\n",
    "    {'date': datetime.datetime(1969, 7, 21, 17, 54, 00, tzinfo=datetime.timezone.utc),\n",
    "     'level': 'WARNING',\n",
    "     'message': 'LM lunar liftoff ignition (LM APS)'},\n",
    "\n",
    "    {'date': datetime.datetime(1969, 7, 21, 2, 56, 15, tzinfo=datetime.timezone.utc),\n",
    "     'level': 'WARNING',\n",
    "     'message': '1st step taken lunar surface (CDR) \"That\\'s one small step for [a] man... one giant leap for mankind\"'},\n",
    "\n",
    "    {'date': datetime.datetime(1969, 7, 20, 20, 17, 39, tzinfo=datetime.timezone.utc),\n",
    "     'level': 'WARNING',\n",
    "     'message': 'LM lunar landing'},\n",
    "\n",
    "...]\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "DATA = \"\"\"\n",
    "    1969-07-14T21:00:00 [INFO] Terminal countdown started\n",
    "    1969-07-16T13:31:53 [WARNING] S-IC engine ignition (#5)\n",
    "    1969-07-16T13:33:23 [DEBUG] Maximum dynamic pressure (735.17 lb/ft^2)\n",
    "    1969-07-16T13:34:44 [WARNING] S-II ignition\n",
    "    1969-07-16T13:35:17 [DEBUG] Launch escape tower jettisoned\n",
    "    1969-07-16T13:39:40 [DEBUG] S-II center engine cutoff\n",
    "    1969-07-16T16:22:13 [INFO] Translunar injection\n",
    "    1969-07-16T16:56:03 [INFO] CSM docked with LM/S-IVB\n",
    "    1969-07-16T17:21:50 [INFO] Lunar orbit insertion ignition\n",
    "    1969-07-16T21:43:36 [INFO] Lunar orbit circularization ignition\n",
    "    1969-07-20T17:44:00 [INFO] CSM/LM undocked\n",
    "    1969-07-20T20:05:05 [WARNING] LM powered descent engine ignition\n",
    "    1969-07-20T20:10:22 [ERROR] LM 1202 alarm\n",
    "    1969-07-20T20:14:18 [ERROR] LM 1201 alarm\n",
    "    1969-07-20T20:17:39 [WARNING] LM lunar landing\n",
    "    1969-07-21T02:39:33 [DEBUG] EVA started (hatch open)\n",
    "    1969-07-21T02:56:15 [WARNING] 1st step taken lunar surface (CDR) \"That's one small step for [a] man... one giant leap for mankind\"\n",
    "    1969-07-21T03:05:58 [DEBUG] Contingency sample collection started (CDR)\n",
    "    1969-07-21T03:15:16 [INFO] LMP on lunar surface\n",
    "    1969-07-21T05:11:13 [DEBUG] EVA ended (hatch closed)\n",
    "    1969-07-21T17:54:00 [WARNING] LM lunar liftoff ignition (LM APS)\n",
    "    1969-07-21T21:35:00 [INFO] CSM/LM docked\n",
    "    1969-07-22T04:55:42 [WARNING] Transearth injection ignition (SPS)\n",
    "    1969-07-24T16:21:12 [INFO] CM/SM separation\n",
    "    1969-07-24T16:35:05 [WARNING] Entry\n",
    "    1969-07-24T16:50:35 [WARNING] Splashdown (went to apex-down)\n",
    "    1969-07-24T17:29 [INFO] Crew egress\n",
    "\"\"\"\n",
    "\n",
    "FILE = 'apollo11-timeline.log'\n",
    "DATABASE = '_temporary.sqlite3'\n",
    "\n",
    "with open(FILE, mode='w') as file:\n",
    "    file.write(DATA)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'id': 16, 'date': '1969-07-21 02:39:33', 'level': 'DEBUG', 'message': 'EVA started (hatch open)'}\n",
      "{'id': 18, 'date': '1969-07-21 03:05:58', 'level': 'DEBUG', 'message': 'Contingency sample collection started (CDR)'}\n",
      "{'id': 20, 'date': '1969-07-21 05:11:13', 'level': 'DEBUG', 'message': 'EVA ended (hatch closed)'}\n",
      "{'id': 43, 'date': '1969-07-21 02:39:33', 'level': 'DEBUG', 'message': 'EVA started (hatch open)'}\n",
      "{'id': 45, 'date': '1969-07-21 03:05:58', 'level': 'DEBUG', 'message': 'Contingency sample collection started (CDR)'}\n",
      "{'id': 47, 'date': '1969-07-21 05:11:13', 'level': 'DEBUG', 'message': 'EVA ended (hatch closed)'}\n",
      "{'id': 70, 'date': '1969-07-21 02:39:33', 'level': 'DEBUG', 'message': 'EVA started (hatch open)'}\n",
      "{'id': 72, 'date': '1969-07-21 03:05:58', 'level': 'DEBUG', 'message': 'Contingency sample collection started (CDR)'}\n",
      "{'id': 74, 'date': '1969-07-21 05:11:13', 'level': 'DEBUG', 'message': 'EVA ended (hatch closed)'}\n",
      "{'id': 13, 'date': '1969-07-20 20:10:22', 'level': 'ERROR', 'message': 'LM 1202 alarm'}\n",
      "{'id': 14, 'date': '1969-07-20 20:14:18', 'level': 'ERROR', 'message': 'LM 1201 alarm'}\n",
      "{'id': 40, 'date': '1969-07-20 20:10:22', 'level': 'ERROR', 'message': 'LM 1202 alarm'}\n",
      "{'id': 41, 'date': '1969-07-20 20:14:18', 'level': 'ERROR', 'message': 'LM 1201 alarm'}\n",
      "{'id': 67, 'date': '1969-07-20 20:10:22', 'level': 'ERROR', 'message': 'LM 1202 alarm'}\n",
      "{'id': 68, 'date': '1969-07-20 20:14:18', 'level': 'ERROR', 'message': 'LM 1201 alarm'}\n",
      "{'id': 11, 'date': '1969-07-20 17:44:00', 'level': 'INFO', 'message': 'CSM/LM undocked'}\n",
      "{'id': 19, 'date': '1969-07-21 03:15:16', 'level': 'INFO', 'message': 'LMP on lunar surface'}\n",
      "{'id': 22, 'date': '1969-07-21 21:35:00', 'level': 'INFO', 'message': 'CSM/LM docked'}\n",
      "{'id': 24, 'date': '1969-07-24 16:21:12', 'level': 'INFO', 'message': 'CM/SM separation'}\n",
      "{'id': 27, 'date': '1969-07-24 17:29:00', 'level': 'INFO', 'message': 'Crew egress'}\n",
      "{'id': 38, 'date': '1969-07-20 17:44:00', 'level': 'INFO', 'message': 'CSM/LM undocked'}\n",
      "{'id': 46, 'date': '1969-07-21 03:15:16', 'level': 'INFO', 'message': 'LMP on lunar surface'}\n",
      "{'id': 49, 'date': '1969-07-21 21:35:00', 'level': 'INFO', 'message': 'CSM/LM docked'}\n",
      "{'id': 51, 'date': '1969-07-24 16:21:12', 'level': 'INFO', 'message': 'CM/SM separation'}\n",
      "{'id': 54, 'date': '1969-07-24 17:29:00', 'level': 'INFO', 'message': 'Crew egress'}\n",
      "{'id': 65, 'date': '1969-07-20 17:44:00', 'level': 'INFO', 'message': 'CSM/LM undocked'}\n",
      "{'id': 73, 'date': '1969-07-21 03:15:16', 'level': 'INFO', 'message': 'LMP on lunar surface'}\n",
      "{'id': 76, 'date': '1969-07-21 21:35:00', 'level': 'INFO', 'message': 'CSM/LM docked'}\n",
      "{'id': 78, 'date': '1969-07-24 16:21:12', 'level': 'INFO', 'message': 'CM/SM separation'}\n",
      "{'id': 81, 'date': '1969-07-24 17:29:00', 'level': 'INFO', 'message': 'Crew egress'}\n",
      "{'id': 12, 'date': '1969-07-20 20:05:05', 'level': 'WARNING', 'message': 'LM powered descent engine ignition'}\n",
      "{'id': 15, 'date': '1969-07-20 20:17:39', 'level': 'WARNING', 'message': 'LM lunar landing'}\n",
      "{'id': 17, 'date': '1969-07-21 02:56:15', 'level': 'WARNING', 'message': '1st step taken lunar surface (CDR) \"That\\'s one small step for [a] man... one giant leap for mankind\"'}\n",
      "{'id': 21, 'date': '1969-07-21 17:54:00', 'level': 'WARNING', 'message': 'LM lunar liftoff ignition (LM APS)'}\n",
      "{'id': 23, 'date': '1969-07-22 04:55:42', 'level': 'WARNING', 'message': 'Transearth injection ignition (SPS)'}\n",
      "{'id': 25, 'date': '1969-07-24 16:35:05', 'level': 'WARNING', 'message': 'Entry'}\n",
      "{'id': 26, 'date': '1969-07-24 16:50:35', 'level': 'WARNING', 'message': 'Splashdown (went to apex-down)'}\n",
      "{'id': 39, 'date': '1969-07-20 20:05:05', 'level': 'WARNING', 'message': 'LM powered descent engine ignition'}\n",
      "{'id': 42, 'date': '1969-07-20 20:17:39', 'level': 'WARNING', 'message': 'LM lunar landing'}\n",
      "{'id': 44, 'date': '1969-07-21 02:56:15', 'level': 'WARNING', 'message': '1st step taken lunar surface (CDR) \"That\\'s one small step for [a] man... one giant leap for mankind\"'}\n",
      "{'id': 48, 'date': '1969-07-21 17:54:00', 'level': 'WARNING', 'message': 'LM lunar liftoff ignition (LM APS)'}\n",
      "{'id': 50, 'date': '1969-07-22 04:55:42', 'level': 'WARNING', 'message': 'Transearth injection ignition (SPS)'}\n",
      "{'id': 52, 'date': '1969-07-24 16:35:05', 'level': 'WARNING', 'message': 'Entry'}\n",
      "{'id': 53, 'date': '1969-07-24 16:50:35', 'level': 'WARNING', 'message': 'Splashdown (went to apex-down)'}\n",
      "{'id': 66, 'date': '1969-07-20 20:05:05', 'level': 'WARNING', 'message': 'LM powered descent engine ignition'}\n",
      "{'id': 69, 'date': '1969-07-20 20:17:39', 'level': 'WARNING', 'message': 'LM lunar landing'}\n",
      "{'id': 71, 'date': '1969-07-21 02:56:15', 'level': 'WARNING', 'message': '1st step taken lunar surface (CDR) \"That\\'s one small step for [a] man... one giant leap for mankind\"'}\n",
      "{'id': 75, 'date': '1969-07-21 17:54:00', 'level': 'WARNING', 'message': 'LM lunar liftoff ignition (LM APS)'}\n",
      "{'id': 77, 'date': '1969-07-22 04:55:42', 'level': 'WARNING', 'message': 'Transearth injection ignition (SPS)'}\n",
      "{'id': 79, 'date': '1969-07-24 16:35:05', 'level': 'WARNING', 'message': 'Entry'}\n",
      "{'id': 80, 'date': '1969-07-24 16:50:35', 'level': 'WARNING', 'message': 'Splashdown (went to apex-down)'}\n"
     ]
    }
   ],
   "source": [
    "SQL_CREATE_TABLE = \"\"\"\n",
    "    CREATE TABLE IF NOT EXISTS apollo (\n",
    "        id INTEGER PRIMARY KEY AUTOINCREMENT,\n",
    "        date DATETIME,\n",
    "        level TEXT,\n",
    "        message TEXT);\"\"\"\n",
    "\n",
    "SQL_INSERT = \"\"\"\n",
    "    INSERT INTO apollo VALUES (\n",
    "        NULL,\n",
    "        :date,\n",
    "        :level,\n",
    "        :message);\"\"\"\n",
    "\n",
    "SQL_SELECT = \"\"\"\n",
    "    SELECT * FROM apollo\n",
    "    WHERE date > \"1969-07-20\"\n",
    "    ORDER BY level\n",
    "\"\"\"\n",
    "\n",
    "result = []\n",
    "\n",
    "with open(FILE) as file:\n",
    "    for line in file:\n",
    "        line = line.strip()\n",
    "        \n",
    "        if len(line) == 0:\n",
    "            continue\n",
    "            \n",
    "        dt, lvl, msg  = line.split(maxsplit=2)\n",
    "        \n",
    "        result.append({\n",
    "            'date': datetime.fromisoformat(dt),\n",
    "            'level': lvl.replace('[', '').replace(']', ''),\n",
    "            'message': msg,\n",
    "        })\n",
    "        \n",
    "with sqlite3.connect(DATABASE) as db:\n",
    "    db.row_factory = sqlite3.Row\n",
    "    db.execute(SQL_CREATE_TABLE)\n",
    "    db.executemany(SQL_INSERT, result)\n",
    "    \n",
    "    for row in db.execute(SQL_SELECT):\n",
    "        print(dict(row))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Database SQLite Relations\n",
    "\n",
    "1. Użyj danych z sekcji \"Input\" (patrz poniżej)\n",
    "1. Stwórz bazę danych na podstawie danych wejściowych\n",
    "1. Dodaj obsługę dla wielu adresów\n",
    "1. Zapisz dane do bazy\n",
    "1. Wypisz dane z bazy wykorzystując relację JOIN\n",
    "\n",
    "Input:\n",
    "```text\n",
    "José, Jiménez\n",
    "    2101 E NASA Pkwy, 77058, Houston, Texas, USA\n",
    "    , Kennedy Space Center, 32899, Florida, USA\n",
    "\n",
    "Mark, Watney\n",
    "    4800 Oak Grove Dr, 91109, Pasadena, California, USA\n",
    "    2825 E Ave P, 93550, Palmdale, California, USA\n",
    "\n",
    "Иван, Иванович\n",
    "    Kosmodrom Bajkonur, Bajkonur, Kazachstan\n",
    "\n",
    "Melissa Lewis,\n",
    "    <NO ADDRESS>\n",
    "\n",
    "Alex Vogel\n",
    "    Linder Hoehe, 51147, Köln, Germany\n",
    "```\n",
    "\n",
    "```sql\n",
    "CREATE TABLE IF NOT EXISTS contact (\n",
    "    id INTEGER PRIMARY KEY AUTOINCREMENT,\n",
    "    created DATETIME,\n",
    "    modified DATETIME,\n",
    "    first_name TEXT,\n",
    "    last_name TEXT,\n",
    "    date_of_birth DATE\n",
    ");\n",
    "\n",
    "CREATE UNIQUE INDEX IF NOT EXISTS last_name_index ON contact (last_name);\n",
    "CREATE INDEX IF NOT EXISTS modified_index ON contact (modified);\n",
    "\n",
    "CREATE TABLE IF NOT EXISTS address (\n",
    "    id INTEGER PRIMARY KEY AUTOINCREMENT,\n",
    "    contact_id INTEGER,\n",
    "    street TEXT,\n",
    "    city TEXT,\n",
    "    state TEXT,\n",
    "    code INT,\n",
    "    country TEXT\n",
    ");\n",
    "\n",
    "INSERT INTO contact VALUES (\n",
    "    NULL,\n",
    "    :created,\n",
    "    :modified,\n",
    "    :first_name,\n",
    "    :last_name,\n",
    "    :date_of_birth\n",
    ");\n",
    "\n",
    "INSERT INTO address VALUES (\n",
    "    NULL,\n",
    "    :contact_id\n",
    "    :street,\n",
    "    :city,\n",
    "    :state,\n",
    "    :code,\n",
    "    :country\n",
    ");\n",
    "\n",
    "UPDATE contact SET\n",
    "    first_name=:firstname,\n",
    "    last_name=:lastname,\n",
    "    modified=:modified\n",
    "WHERE id=:id;\n",
    "\n",
    "SELECT * FROM contact;\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'id': 1, 'created': '2020-10-11 22:30:37.770205+00:00', 'modified': '2020-10-11 22:30:37.770216+00:00', 'first_name': 'José', 'last_name': 'Jiménez', 'date_of_birth': None, 'contact_id': 1, 'street': '2101 E NASA Pkwy', 'city': 'Houston', 'state': 'Texas', 'code': 77058, 'country': 'USA'}\n",
      "{'id': 1, 'created': '2020-10-11 22:30:37.770205+00:00', 'modified': '2020-10-11 22:30:37.770216+00:00', 'first_name': 'José', 'last_name': 'Jiménez', 'date_of_birth': None, 'contact_id': 1, 'street': None, 'city': 'Kennedy Space Center', 'state': 'Florida', 'code': 32899, 'country': 'USA'}\n",
      "{'id': 2, 'created': '2020-10-11 22:30:37.791502+00:00', 'modified': '2020-10-11 22:30:37.791509+00:00', 'first_name': 'Mark', 'last_name': 'Watney', 'date_of_birth': None, 'contact_id': 2, 'street': '4800 Oak Grove Dr', 'city': 'Pasadena', 'state': 'California', 'code': 91109, 'country': 'USA'}\n",
      "{'id': 2, 'created': '2020-10-11 22:30:37.791502+00:00', 'modified': '2020-10-11 22:30:37.791509+00:00', 'first_name': 'Mark', 'last_name': 'Watney', 'date_of_birth': None, 'contact_id': 2, 'street': '2825 E Ave P', 'city': 'Palmdale', 'state': 'California', 'code': 93550, 'country': 'USA'}\n",
      "{'id': 3, 'created': '2020-10-11 22:30:37.791569+00:00', 'modified': '2020-10-11 22:30:37.791571+00:00', 'first_name': 'Иван', 'last_name': 'Иванович', 'date_of_birth': None, 'contact_id': 3, 'street': 'Kosmodrom Bajkonur', 'city': 'Bajkonur', 'state': None, 'code': None, 'country': 'Kazachstan'}\n",
      "{'id': 5, 'created': '2020-10-11 22:30:37.791625+00:00', 'modified': '2020-10-11 22:30:37.791626+00:00', 'first_name': 'Alex', 'last_name': 'Vogel', 'date_of_birth': None, 'contact_id': 5, 'street': 'Linder Hoehe', 'city': 'Köln', 'state': None, 'code': 51147, 'country': 'Germany'}\n"
     ]
    }
   ],
   "source": [
    "SQL_CREATE_TABLE_CONTACT = \"\"\"\n",
    "    CREATE TABLE IF NOT EXISTS contact (\n",
    "        id INTEGER PRIMARY KEY AUTOINCREMENT,\n",
    "        created DATETIME,\n",
    "        modified DATETIME,\n",
    "        first_name TEXT,\n",
    "        last_name TEXT,\n",
    "        date_of_birth DATE);\"\"\"\n",
    "\n",
    "SQL_CREATE_INDEX_CONTACT_LASTNAME = \"\"\"\n",
    "    CREATE UNIQUE INDEX IF NOT EXISTS last_name_index ON contact (last_name);\"\"\"\n",
    "\n",
    "SQL_CREATE_INDEX_CONTACT_MODIFIED = \"\"\"\n",
    "    CREATE INDEX IF NOT EXISTS modified_index ON contact (modified);\"\"\"\n",
    "\n",
    "SQL_CREATE_TABLE_ADDRESS = \"\"\"\n",
    "    CREATE TABLE IF NOT EXISTS address (\n",
    "        id INTEGER PRIMARY KEY AUTOINCREMENT,\n",
    "        contact_id INTEGER,\n",
    "        street TEXT,\n",
    "        city TEXT,\n",
    "        state TEXT,\n",
    "        code INT,\n",
    "        country TEXT);\"\"\"\n",
    "\n",
    "SQL_INSERT_CONTACT = \"\"\"\n",
    "    INSERT INTO contact VALUES (\n",
    "        NULL,\n",
    "        :created,\n",
    "        :modified,\n",
    "        :first_name,\n",
    "        :last_name,\n",
    "        :date_of_birth);\"\"\"\n",
    "\n",
    "SQL_INSERT_ADDRESS = \"\"\"\n",
    "    INSERT INTO address VALUES (\n",
    "        NULL,\n",
    "        :contact_id,\n",
    "        :street,\n",
    "        :city,\n",
    "        :state,\n",
    "        :code,\n",
    "        :country);\"\"\"\n",
    "\n",
    "SQL_SELECT = \"\"\"\n",
    "    SELECT *\n",
    "    FROM contact\n",
    "    JOIN address\n",
    "    ON contact.id=address.contact_id;\"\"\"\n",
    "\n",
    "DATA = \"\"\"\n",
    "José, Jiménez\n",
    "    2101 E NASA Pkwy, 77058, Houston, Texas, USA\n",
    "    , Kennedy Space Center, 32899, Florida, USA\n",
    "\n",
    "Mark, Watney\n",
    "    4800 Oak Grove Dr, 91109, Pasadena, California, USA\n",
    "    2825 E Ave P, 93550, Palmdale, California, USA\n",
    "\n",
    "Иван, Иванович\n",
    "    Kosmodrom Bajkonur, Bajkonur, Kazachstan\n",
    "\n",
    "Melissa Lewis,\n",
    "    <NO ADDRESS>\n",
    "\n",
    "Alex Vogel\n",
    "    Linder Hoehe, 51147, Köln, Germany\n",
    "\"\"\"\n",
    "\n",
    "data = [\n",
    "    {'first_name': 'José', 'last_name': 'Jiménez', 'addresses': [\n",
    "        {'street': '2101 E NASA Pkwy', 'code': 77058, 'city': 'Houston', 'state': 'Texas', 'country': 'USA'},\n",
    "        {'street': None, 'code': 32899, 'city': 'Kennedy Space Center', 'state': 'Florida', 'country': 'USA'},\n",
    "    ]},\n",
    "    \n",
    "    {'first_name': 'Mark', 'last_name': 'Watney', 'addresses': [\n",
    "            {'street': '4800 Oak Grove Dr', 'code': 91109, 'city': 'Pasadena', 'state': 'California', 'country': 'USA'},\n",
    "            {'street': '2825 E Ave P', 'code': 93550, 'city': 'Palmdale', 'state': 'California', 'country': 'USA'},\n",
    "    ]},\n",
    "    \n",
    "    {'first_name': 'Иван', 'last_name': 'Иванович', 'addresses': [\n",
    "         {'street': 'Kosmodrom Bajkonur', 'code': None, 'city': 'Bajkonur', 'state': None, 'country': 'Kazachstan'},\n",
    "    ]},\n",
    "    \n",
    "    {'first_name': 'Melissa', 'last_name': 'Lewis', 'addresses': []},\n",
    "    \n",
    "    {'first_name': 'Alex', 'last_name': 'Vogel', 'addresses': [\n",
    "        {'street': 'Linder Hoehe', 'code': 51147, 'city': 'Köln', 'state': None, 'country': 'Germany'}]}\n",
    "]\n",
    "\n",
    "with sqlite3.connect(DATABASE) as connection:\n",
    "    db = connection.cursor()\n",
    "    db.row_factory = sqlite3.Row\n",
    "    \n",
    "    db.execute(SQL_CREATE_TABLE_CONTACT)\n",
    "    db.execute(SQL_CREATE_TABLE_ADDRESS)\n",
    "    db.execute(SQL_CREATE_INDEX_CONTACT_LASTNAME)\n",
    "    db.execute(SQL_CREATE_INDEX_CONTACT_MODIFIED)\n",
    "\n",
    "    for contact in data:\n",
    "        contact['created'] = datetime.now(timezone.utc)\n",
    "        contact['modified'] = datetime.now(timezone.utc)\n",
    "        contact['date_of_birth'] = None\n",
    "        addresses = contact.pop('addresses')\n",
    "        \n",
    "        db.execute(SQL_INSERT_CONTACT, contact)\n",
    "        contact_id = db.lastrowid\n",
    "        \n",
    "        for addr in addresses:\n",
    "            addr['contact_id'] = contact_id\n",
    "            db.execute(SQL_INSERT_ADDRESS, addr)\n",
    "            \n",
    "            \n",
    "    for row in db.execute(SQL_SELECT):\n",
    "        print(dict(row))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [],
   "source": [
    "!rm $DATABASE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "PRAGMA foreign_keys=OFF;\r\n",
      "BEGIN TRANSACTION;\r\n",
      "CREATE TABLE contact (\r\n",
      "        id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n",
      "        created DATETIME,\r\n",
      "        modified DATETIME,\r\n",
      "        first_name TEXT,\r\n",
      "        last_name TEXT,\r\n",
      "        date_of_birth DATE);\r\n",
      "INSERT INTO contact VALUES(1,'2020-10-11 22:30:37.770205+00:00','2020-10-11 22:30:37.770216+00:00','José','Jiménez',NULL);\r\n",
      "INSERT INTO contact VALUES(2,'2020-10-11 22:30:37.791502+00:00','2020-10-11 22:30:37.791509+00:00','Mark','Watney',NULL);\r\n",
      "INSERT INTO contact VALUES(3,'2020-10-11 22:30:37.791569+00:00','2020-10-11 22:30:37.791571+00:00','Иван','Иванович',NULL);\r\n",
      "INSERT INTO contact VALUES(4,'2020-10-11 22:30:37.791604+00:00','2020-10-11 22:30:37.791605+00:00','Melissa','Lewis',NULL);\r\n",
      "INSERT INTO contact VALUES(5,'2020-10-11 22:30:37.791625+00:00','2020-10-11 22:30:37.791626+00:00','Alex','Vogel',NULL);\r\n",
      "CREATE TABLE address (\r\n",
      "        id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n",
      "        contact_id INTEGER,\r\n",
      "        street TEXT,\r\n",
      "        city TEXT,\r\n",
      "        state TEXT,\r\n",
      "        code INT,\r\n",
      "        country TEXT);\r\n",
      "INSERT INTO address VALUES(1,1,'2101 E NASA Pkwy','Houston','Texas',77058,'USA');\r\n",
      "INSERT INTO address VALUES(2,1,NULL,'Kennedy Space Center','Florida',32899,'USA');\r\n",
      "INSERT INTO address VALUES(3,2,'4800 Oak Grove Dr','Pasadena','California',91109,'USA');\r\n",
      "INSERT INTO address VALUES(4,2,'2825 E Ave P','Palmdale','California',93550,'USA');\r\n",
      "INSERT INTO address VALUES(5,3,'Kosmodrom Bajkonur','Bajkonur',NULL,NULL,'Kazachstan');\r\n",
      "INSERT INTO address VALUES(6,5,'Linder Hoehe','Köln',NULL,51147,'Germany');\r\n",
      "DELETE FROM sqlite_sequence;\r\n",
      "INSERT INTO sqlite_sequence VALUES('contact',5);\r\n",
      "INSERT INTO sqlite_sequence VALUES('address',6);\r\n",
      "CREATE UNIQUE INDEX last_name_index ON contact (last_name);\r\n",
      "CREATE INDEX modified_index ON contact (modified);\r\n",
      "COMMIT;\r\n"
     ]
    }
   ],
   "source": [
    "!sqlite3 $DATABASE .dump"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'first_name': 'Mark',\n",
       "  'last_name': 'Watney',\n",
       "  'addresses': [{'street': '4800 Oak Grove Dr',\n",
       "    'code': 91109,\n",
       "    'city': 'Pasadena',\n",
       "    'state': 'California',\n",
       "    'country': 'USA'},\n",
       "   {'street': '2825 E Ave P',\n",
       "    'code': 93550,\n",
       "    'city': 'Palmdale',\n",
       "    'state': 'California',\n",
       "    'country': 'USA'}]},\n",
       " {'first_name': 'Иван',\n",
       "  'last_name': 'Иванович',\n",
       "  'addresses': [{'street': 'Kosmodrom Bajkonur',\n",
       "    'code': None,\n",
       "    'city': 'Bajkonur',\n",
       "    'state': None,\n",
       "    'country': 'Kazachstan'}]},\n",
       " {'first_name': 'Melissa', 'last_name': 'Lewis', 'addresses': []},\n",
       " {'first_name': 'Alex', 'last_name': 'Vogel', 'addresses': []}]"
      ]
     },
     "execution_count": 92,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "DATA = \"\"\"\n",
    "José, Jiménez\n",
    "    2101 E NASA Pkwy, 77058, Houston, Texas, USA\n",
    "    , Kennedy Space Center, 32899, Florida, USA\n",
    "\n",
    "Mark, Watney\n",
    "    4800 Oak Grove Dr, 91109, Pasadena, California, USA\n",
    "    2825 E Ave P, 93550, Palmdale, California, USA\n",
    "\n",
    "Иван, Иванович\n",
    "    Kosmodrom Bajkonur, Bajkonur, Kazachstan\n",
    "\n",
    "Melissa Lewis,\n",
    "    <NO ADDRESS>\n",
    "\n",
    "Alex Vogel\n",
    "    Linder Hoehe, 51147, Köln, Germany\n",
    "\"\"\"\n",
    "\n",
    "\n",
    "import re\n",
    "\n",
    "result = []\n",
    "\n",
    "for contact in re.split(r'\\n\\n', DATA):\n",
    "    contact, *addresses = contact.splitlines()\n",
    "    contact = contact.replace(',', '')\n",
    "    \n",
    "    if len(contact) == 0:\n",
    "        continue\n",
    "    \n",
    "    firstname, lastname = contact.split()\n",
    "    \n",
    "    address = []\n",
    "    for addr in addresses:\n",
    "        if addr == '<NO ADDRESS>':\n",
    "            address.append([])\n",
    "            continue\n",
    "        \n",
    "        addr = addr.strip().split(', ')\n",
    "        \n",
    "        if len(addr) == 5:\n",
    "            address.append({\n",
    "                'street': addr[0],\n",
    "                'code': int(addr[1]),\n",
    "                'city': addr[2],\n",
    "                'state': addr[3],\n",
    "                'country': addr[4]\n",
    "            })\n",
    "            \n",
    "        if len(addr) == 3:\n",
    "            address.append({\n",
    "                'street': addr[0],\n",
    "                'code': None,\n",
    "                'city': addr[1],\n",
    "                'state': None,\n",
    "                'country': addr[2]\n",
    "            })\n",
    "               \n",
    "    result.append({\n",
    "        'first_name': firstname,\n",
    "        'last_name': lastname,\n",
    "        'addresses': address,\n",
    "    })\n",
    "    \n",
    "    \n",
    "result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}