d04-stdlib-bazy-danych-zadania.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Python Biblioteka Standardowa - bazy danych - zadania"
   ]
  },
  {
   "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": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "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": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "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": null,
   "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",
    "\"\"\""
   ]
  }
 ],
 "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.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}