09-databaze-perzistence.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# Pickle\n",
    "\n",
    "Umožňuje binárně \"zmrazit\" prakticky libovolná data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "import pickle"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "# struktury\n",
    "data = {\n",
    "    \"velmi\": \"slozity\",\n",
    "    \"slovnik\": [\"slozeny\", \"z\", 1.2, \"klicu\"],\n",
    "    \"a\": {\"dalsich\", \"divnych\", \"veci\"},\n",
    "    (1, 2): False,\n",
    "    5: None,\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "# zápis do souboru\n",
    "with open(\"data/pickle.p\", mode=\"wb\") as f:\n",
    "    pickle.dump(data, f)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "# zapiklení do objektu `bytes`\n",
    "pickle.dumps(data)[:64]  # zkráceno .."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "# načtení ze souboru\n",
    "with open(\"data/pickle.p\", mode=\"rb\") as f:\n",
    "    print(pickle.load(f))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "**Co lze piklit?**\n",
    "\n",
    "- `None`, `True`, `False`\n",
    "- celá, reálná a komplexní čísla\n",
    "- řetězce, `bytes` a `bytearray`\n",
    "- n-tice, seznamy, množiny a slovníky s piklitelnými daty\n",
    "- běžné funkce (ne uzávěry, ne lambdy)\n",
    "- třídy (ne vnořené)\n",
    "- instance tříd, jejichž atribut `__dict__` nebo návratová hodnota metody `__getstate__()` je piklitelná"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "U tříd a funkcí se piklí jenom *plné jméno objektu, ne kód*!   U instancí navíc jejich data.\n",
    "\n",
    "Co to znamená?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "def udelej_vec():\n",
    "    print(\"Dělám věc.\")\n",
    "\n",
    "zapiklena_funkce = pickle.dumps(udelej_vec)\n",
    "\n",
    "# -------- přenos dat --------\n",
    "\n",
    "def udelej_vec():\n",
    "    print(\"Dělám VELMI ZLOU věc.\")\n",
    "\n",
    "odpiklena_funkce = pickle.loads(zapiklena_funkce)\n",
    "odpiklena_funkce()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "# chyby\n",
    "try:\n",
    "    pickle.dumps(lambda x: x ** 2)\n",
    "\n",
    "except pickle.PickleError as e:\n",
    "    print(e)\n",
    "\n",
    "except RecursionError:\n",
    "    print(\"We cannot go deeper.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "**Nikdy nerozpiklujte cizí data.**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# Shelve\n",
    "\n",
    "= Automaticky piklící slovník."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "import shelve"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "# zápis\n",
    "with shelve.open(\"data/storage\") as storage:\n",
    "    storage[\"description\"] = \"dangerous content\"\n",
    "    storage[\"function\"] = abs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "# čtení = ten samý protokol! :-)\n",
    "with shelve.open(\"data/storage\") as storage:\n",
    "    print(dict(storage))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "**Nikdy nerozpiklujte cizí data.**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# [PEP249](https://www.python.org/dev/peps/pep-0249/) – Python Database API Specification 2.0"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "Každý DB modul by měl splňovat:\n",
    "\n",
    "- `modul.connect()` – vytvoření spojení\n",
    "- `connection.cursor()` – vytvoření kurzoru\n",
    "- `cursor.execute()` – vykonání dotazu\n",
    "- `cursor.fetchall()` – vytažení výsledků dotazu\n",
    "- `cursor.rowcount` – počet ovlivněných (vybraných) řádků dotazem\n",
    "- `connection.close()` – uzavření spojení"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# SQLite3\n",
    "\n",
    "= Nenáročná, relativně hloupá DB."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "import sqlite3  # splňuje PEP249 - DB-API 2.0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "# vytvoření spojení a kurzoru\n",
    "connection = sqlite3.connect(\":memory:\")\n",
    "c = connection.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "# vytvoření tabulky DB\n",
    "c.execute(\"\"\"\n",
    "CREATE TABLE IF NOT EXISTS samples\n",
    "(id INTEGER PRIMARY KEY AUTOINCREMENT, value REAL);\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "from random import random\n",
    "\n",
    "# naplnění\n",
    "for i in range(10):\n",
    "    params = (random(), ) # tuple\n",
    "    c.execute(\"INSERT INTO samples (value) VALUES (?)\",  params)\n",
    "\n",
    "# potvrzení transakce\n",
    "connection.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "# výběr\n",
    "c.execute(\"SELECT * FROM samples\")\n",
    "\n",
    "# SQLite3 implementuje metodu `__iter__` u kurzoru, jinak => `fetchall`\n",
    "for sample in c:\n",
    "    print(sample)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "# jiný obal pro vracená data\n",
    "connection.row_factory = sqlite3.Row\n",
    "\n",
    "# musí být před vytvořením kurzoru\n",
    "advanced_cursor = connection.cursor()\n",
    "\n",
    "advanced_cursor.execute(\"SELECT * FROM samples\")\n",
    "for sample in advanced_cursor:\n",
    "    print(dict(sample))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "# kontextový manažer vytvoří transakci\n",
    "with connection:\n",
    "    # `connection.execute` = zkratka bez vytváření kurzoru\n",
    "    connection.execute(\"INSERT INTO samples (value) VALUES (0)\")  # nebude vloženo\n",
    "    connection.execute(\"INVALID QUERY\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# ORM\n",
    "\n",
    "= Objektově relační mapování. Není ve standardní knihovně.\n",
    "\n",
    "- [SQLAlchemy](http://www.sqlalchemy.org/)\n",
    "- [peewee](http://peewee.readthedocs.io/en/latest/index.html)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Peewee"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "import peewee\n",
    "from datetime import date"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "db = peewee.SqliteDatabase(\"data/people.db\")\n",
    "\n",
    "# společný předek pro vlastní modely\n",
    "class Model(peewee.Model):\n",
    "    class Meta:\n",
    "        database = db\n",
    "\n",
    "# vlastní model\n",
    "class Person(Model):\n",
    "    name = peewee.CharField()\n",
    "    birthday = peewee.DateField()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "db.connect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [],
   "source": [
    "# toto se běžně nedělá\n",
    "if Person.table_exists():\n",
    "    Person.drop_table()\n",
    "Person.create_table()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "# vytvoření záznamů\n",
    "Person.create(name=\"David\", birthday=date.today())\n",
    "luke = Person(name=\"Luke\", birthday=date(2002, 1, 3))\n",
    "luke.save()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "# výběr\n",
    "for person in Person.select():\n",
    "    print(person.name, person.birthday)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "# aktualizace\n",
    "luke.birthday = date.today()\n",
    "luke.save()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "# filtrovaný výběr\n",
    "for person in Person.select().where(Person.name == \"Luke\"):\n",
    "    print(person.birthday)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "**Podporované backendy:**\n",
    "\n",
    "- SQLite (pomocí standardní knihovny)\n",
    "- MySQL – `pymysql`\n",
    "- PostgreQSL - `psycopg2`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# Příklad\n",
    "\n",
    "- Přečtěte soubor `data/books.xml` a data z něj vložte do SQLite databáze `data/books.sqlite3`.\n",
    "- Vypište knihy, kde: `price > 10.0`.\n",
    "- *Profi varianta:* nainstalujte a použijte ORM."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "\n",
    "create_query = r\"\"\"\n",
    "CREATE TABLE IF NOT EXISTS books (\n",
    "    id INTEGER PRIMARY KEY,\n",
    "    title TEXT,\n",
    "    author TEXT,\n",
    "    genre TEXT,\n",
    "    price FLOAT,\n",
    "    publish_date DATE,\n",
    "    description TEXT\n",
    ");\n",
    "\"\"\"\n",
    "\n",
    "# TODO"
   ]
  }
 ],
 "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.5.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}