d04-stdlib-bazy-danych-lekcja.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Python Biblioteka Standardowa - bazy danych - lekcja"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "SQL_CREATE_TABLE = \"\"\"\n",
    "    CREATE TABLE IF NOT EXISTS astronauts (\n",
    "        id INTEGER PRIMARY KEY AUTOINCREMENT,\n",
    "        created DATETIME NOT NULL DEFAULT (datetime('now', 'utc')),\n",
    "        pesel INTEGER UNIQUE,\n",
    "        firstname TEXT,\n",
    "        lastname TEXT,\n",
    "        agency TEXT)\"\"\"\n",
    "\n",
    "SQL_INSERT_LISTDICT = \"\"\"\n",
    "    INSERT INTO astronauts (pesel, firstname, lastname, agency)\n",
    "    VALUES (:pesel, :firstname, :lastname, :agency)\"\"\"\n",
    "\n",
    "SQL_INSERT_LISTTUPLE = \"\"\"\n",
    "    INSERT INTO astronauts (pesel, firstname, lastname, agency)\n",
    "    VALUES (?, ?, ?, ?)\"\"\"\n",
    "\n",
    "SQL_SELECT = 'SELECT * FROM astronauts'\n",
    "\n",
    "\n",
    "DATA_LISTTUPLE = [\n",
    "    (61041200001, 'José', 'Jiménez', 'NASA'),\n",
    "    (61041200002, 'Jan', 'Twardowski', 'POLSA'),\n",
    "    (61041200003, 'Ivan', 'Ivanovich', 'Roscosmos'),\n",
    "    (61041200004, 'Max', 'Peck', 'NASA'),\n",
    "    (61041200005, 'Melissa', 'Lewis', 'NASA'),\n",
    "    (61041200006, 'Alex', 'Vogel', 'NASA'),\n",
    "    (61041200007, 'Mark', 'Watney', 'NASA'),\n",
    "    (61041200008, 'Rick', 'Martinez', 'NASA'),\n",
    "    (61041200009, 'Beth', 'Johanssen', 'NASA'),\n",
    "    (61041200010, 'Chris', 'Beck', 'NASA'),\n",
    "    (61041200011, 'Matt', 'Kowalsky', 'NASA'),\n",
    "    (61041200012, 'Ryan', 'Stone', 'NASA'),\n",
    "    (61041200013, 'Robert', 'Foucault', 'IMSF'),\n",
    "    (61041200014, 'Javier', 'Delgado', 'IMSF'),\n",
    "    (61041200015, 'Amelie', 'Durand', 'IMSF'),\n",
    "    (61041200016, 'Hana', 'Seung', 'IMSF'),\n",
    "    (61041200017, 'Ben', 'Sawyer', 'IMSF'),\n",
    "    (61041200018, 'Marta', 'Kamen', 'IMSF'),\n",
    "]\n",
    "\n",
    "DATA_LISTDICT = [\n",
    "    {'pesel': 61041200001, 'firstname': 'José', 'lastname': 'Jiménez', 'agency': 'NASA'},\n",
    "    {'pesel': 61041200002, 'firstname': 'Jan', 'lastname': 'Twardowski', 'agency': 'POLSA'},\n",
    "    {'pesel': 61041200003, 'firstname': 'Ivan', 'lastname': 'Ivanovich', 'agency': 'Roscosmos'},\n",
    "    {'pesel': 61041200004, 'firstname': 'Max', 'lastname': 'Peck', 'agency': 'NASA'},\n",
    "    {'pesel': 61041200005, 'firstname': 'Melissa', 'lastname': 'Lewis', 'agency': 'NASA'},\n",
    "    {'pesel': 61041200006, 'firstname': 'Alex', 'lastname': 'Vogel', 'agency': 'NASA'},\n",
    "    {'pesel': 61041200007, 'firstname': 'Mark', 'lastname': 'Watney', 'agency': 'NASA'},\n",
    "    {'pesel': 61041200008, 'firstname': 'Rick', 'lastname': 'Martinez', 'agency': 'NASA'},\n",
    "    {'pesel': 61041200009, 'firstname': 'Beth', 'lastname': 'Johanssen', 'agency': 'NASA'},\n",
    "    {'pesel': 61041200010, 'firstname': 'Chris', 'lastname': 'Beck', 'agency': 'NASA'},\n",
    "    {'pesel': 61041200011, 'firstname': 'Matt', 'lastname': 'Kowalsky', 'agency': 'NASA'},\n",
    "    {'pesel': 61041200012, 'firstname': 'Ryan', 'lastname': 'Stone', 'agency': 'NASA'},\n",
    "    {'pesel': 61041200013, 'firstname': 'Robert', 'lastname': 'Foucault', 'agency': 'IMSF'},\n",
    "    {'pesel': 61041200014, 'firstname': 'Javier', 'lastname': 'Delgado', 'agency': 'IMSF'},\n",
    "    {'pesel': 61041200015, 'firstname': 'Amelie', 'lastname': 'Durand', 'agency': 'IMSF'},\n",
    "    {'pesel': 61041200016, 'firstname': 'Hana', 'lastname': 'Seung', 'agency': 'IMSF'},\n",
    "    {'pesel': 61041200017, 'firstname': 'Ben', 'lastname': 'Sawyer', 'agency': 'IMSF'},\n",
    "    {'pesel': 61041200018, 'firstname': 'Marta', 'lastname': 'Kamen', 'agency': 'IMSF'},\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Python DB API\n",
    "\n",
    "* ``connect()``\n",
    "* ``cursor()``\n",
    "* ``execute()``\n",
    "* ``executemany()``\n",
    "* ``fetchone()``\n",
    "* ``fetchmany(size=cursor.arraysize)``\n",
    "* ``fetchall()``\n",
    "* ``commit()``\n",
    "* ``rollback()``\n",
    "* ``close()``"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## SQLite 3\n",
    "\n",
    "* Typy danych:\n",
    "\n",
    "    * ``INTEGER`` - `int`\n",
    "    * ``REAL`` - `float`\n",
    "    * ``TEXT`` - `str`\n",
    "    * ``BLOB`` - `bytes`\n",
    "    * ``NULL`` - `None`\n",
    "    \n",
    "* Więzy:\n",
    "\n",
    "    * ``NOT NULL``\n",
    "    * ``UNIQUE``\n",
    "    * ``PRIMARY KEY``\n",
    "    * ``FOREIGN KEY``\n",
    "    * ``DEFAULT``\n",
    "    * ``INDEX``\n",
    "    \n",
    "* Wyjątki:\n",
    "\n",
    "    * ``sqlite3.Warning``\n",
    "    * ``sqlite3.Error``\n",
    "    * ``sqlite3.IntegrityError`` - np. błąd więzów\n",
    "    * ``sqlite3.ProgrammingError`` - np. błąd składniowy w SQL lub brak tabeli\n",
    "    * ``sqlite3.OperationalError`` - np. nieoczekiwane rozłączenie z bazą\n",
    "    * ``sqlite3.NotSupportedError`` - np. gdy wywołano rollback a połączenie ma wyłączone transakcje\n",
    "    \n",
    "* Połączenie do ``FILE`` lub ``:memory:``\n",
    "* Wyniki jako słowniki\n",
    "\n",
    "* ``iterdump()`` - podobne do `.dump`\n",
    "* ``backup(target, *, pages=0, progress=None, name=\"main\", sleep=0.250)`` - nawet w trakcie połączenia"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'id': 1, 'created': '2020-08-03 00:06:34', 'pesel': 61041200001, 'firstname': 'José', 'lastname': 'Jiménez', 'agency': 'NASA'}\n",
      "{'id': 2, 'created': '2020-08-03 00:06:34', 'pesel': 61041200002, 'firstname': 'Jan', 'lastname': 'Twardowski', 'agency': 'POLSA'}\n",
      "{'id': 3, 'created': '2020-08-03 00:06:34', 'pesel': 61041200003, 'firstname': 'Ivan', 'lastname': 'Ivanovich', 'agency': 'Roscosmos'}\n",
      "{'id': 4, 'created': '2020-08-03 00:06:34', 'pesel': 61041200004, 'firstname': 'Max', 'lastname': 'Peck', 'agency': 'NASA'}\n",
      "{'id': 5, 'created': '2020-08-03 00:06:34', 'pesel': 61041200005, 'firstname': 'Melissa', 'lastname': 'Lewis', 'agency': 'NASA'}\n",
      "{'id': 6, 'created': '2020-08-03 00:06:34', 'pesel': 61041200006, 'firstname': 'Alex', 'lastname': 'Vogel', 'agency': 'NASA'}\n",
      "{'id': 7, 'created': '2020-08-03 00:06:34', 'pesel': 61041200007, 'firstname': 'Mark', 'lastname': 'Watney', 'agency': 'NASA'}\n",
      "{'id': 8, 'created': '2020-08-03 00:06:34', 'pesel': 61041200008, 'firstname': 'Rick', 'lastname': 'Martinez', 'agency': 'NASA'}\n",
      "{'id': 9, 'created': '2020-08-03 00:06:34', 'pesel': 61041200009, 'firstname': 'Beth', 'lastname': 'Johanssen', 'agency': 'NASA'}\n",
      "{'id': 10, 'created': '2020-08-03 00:06:34', 'pesel': 61041200010, 'firstname': 'Chris', 'lastname': 'Beck', 'agency': 'NASA'}\n",
      "{'id': 11, 'created': '2020-08-03 00:06:34', 'pesel': 61041200011, 'firstname': 'Matt', 'lastname': 'Kowalsky', 'agency': 'NASA'}\n",
      "{'id': 12, 'created': '2020-08-03 00:06:34', 'pesel': 61041200012, 'firstname': 'Ryan', 'lastname': 'Stone', 'agency': 'NASA'}\n",
      "{'id': 13, 'created': '2020-08-03 00:06:34', 'pesel': 61041200013, 'firstname': 'Robert', 'lastname': 'Foucault', 'agency': 'IMSF'}\n",
      "{'id': 14, 'created': '2020-08-03 00:06:34', 'pesel': 61041200014, 'firstname': 'Javier', 'lastname': 'Delgado', 'agency': 'IMSF'}\n",
      "{'id': 15, 'created': '2020-08-03 00:06:34', 'pesel': 61041200015, 'firstname': 'Amelie', 'lastname': 'Durand', 'agency': 'IMSF'}\n",
      "{'id': 16, 'created': '2020-08-03 00:06:34', 'pesel': 61041200016, 'firstname': 'Hana', 'lastname': 'Seung', 'agency': 'IMSF'}\n",
      "{'id': 17, 'created': '2020-08-03 00:06:34', 'pesel': 61041200017, 'firstname': 'Ben', 'lastname': 'Sawyer', 'agency': 'IMSF'}\n",
      "{'id': 18, 'created': '2020-08-03 00:06:34', 'pesel': 61041200018, 'firstname': 'Marta', 'lastname': 'Kamen', 'agency': 'IMSF'}\n"
     ]
    }
   ],
   "source": [
    "DATABASE = r':memory:'\n",
    "\n",
    "with sqlite3.connect(DATABASE) as db:\n",
    "    db.execute(SQL_CREATE_TABLE)\n",
    "    db.executemany(SQL_INSERT_LISTDICT, DATA_LISTDICT)\n",
    "    \n",
    "    db.row_factory = sqlite3.Row\n",
    "    \n",
    "    for row in db.execute(SQL_SELECT):\n",
    "        print(dict(row))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'agency': 'POLSA'}\n",
      "{'agency': 'Roscosmos'}\n"
     ]
    }
   ],
   "source": [
    "DATABASE = r'db.sqlite3'\n",
    "\n",
    "with sqlite3.connect(DATABASE) as db:\n",
    "    db.execute(SQL_CREATE_TABLE)\n",
    "    \n",
    "    \n",
    "    try:\n",
    "        db.executemany(SQL_INSERT_LISTDICT, DATA_LISTDICT)\n",
    "    except sqlite3.IntegrityError:\n",
    "        pass\n",
    "    \n",
    "    db.row_factory = sqlite3.Row\n",
    "    \n",
    "    \n",
    "#     SQL_SELECT = \"\"\"\n",
    "#         SELECT id, firstname as fname, lastname as lname, agency\n",
    "#         FROM astronauts\n",
    "#         WHERE agency IN (\n",
    "#             SELECT agency\n",
    "#             FROM astronauts\n",
    "#             GROUP BY agency\n",
    "#             HAVING count(*) < 5\n",
    "#         )\n",
    "#     \"\"\"\n",
    "\n",
    "    SQL_SELECT = \"\"\"\n",
    "    SELECT agency\n",
    "            FROM astronauts\n",
    "            GROUP BY agency\n",
    "            HAVING count(*) < 5\"\"\"\n",
    "    \n",
    "    for row in db.execute(SQL_SELECT):\n",
    "        print(dict(row))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'agency': 'POLSA'}\n",
      "{'agency': 'Roscosmos'}\n"
     ]
    }
   ],
   "source": [
    "DATABASE = r':memory:'\n",
    "\n",
    "with sqlite3.connect(DATABASE) as db:\n",
    "    db.execute(SQL_CREATE_TABLE)\n",
    "    db.executemany(SQL_INSERT_LISTDICT, DATA_LISTDICT)\n",
    "    db.row_factory = sqlite3.Row\n",
    "    cursor = db.cursor()\n",
    "    \n",
    "    for row in cursor.execute(SQL_SELECT):\n",
    "        print(dict(row))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## SQL\n",
    "\n",
    "* klauzula projekcji ``SELECT``\n",
    "* klauzula selekcji ``WHERE``\n",
    "* distinct ``DISTINCT``\n",
    "* zliczanie ``COUNT()``\n",
    "* aliasy ``as``\n",
    "* sortowanie ``ORDER BY``\n",
    "* limitowanie ``LIMIT``\n",
    "* podzapytania ``IN``\n",
    "* grupowanie ``GROUP BY``\n",
    "* heaving ``HAVING``"
   ]
  },
  {
   "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.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}