infsys_serie-3.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Pony ORM by Example: Informationssysteme Serie 3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "PonyORM lässt sich recht unkompliziert mit `pip install pony` installieren."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from pony.orm import *\n",
    "\n",
    "db = Database()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "class L(db.Entity):\n",
    "    # Lieferant\n",
    "    lnr = PrimaryKey(str)\n",
    "    lname = Required(str)\n",
    "    status = Required(int)\n",
    "    sitz = Required(str)\n",
    "    ltps = Set(\"LTP\")\n",
    "\n",
    "class T(db.Entity):\n",
    "    # Teil\n",
    "    tnr = PrimaryKey(str)\n",
    "    tname = Required(str)\n",
    "    farbe = Required(str)\n",
    "    gewicht = Required(int)\n",
    "    ltps = Set(\"LTP\")\n",
    "    \n",
    "class P(db.Entity):\n",
    "    # Projekt\n",
    "    pnr = PrimaryKey(str)\n",
    "    pname = Required(str)\n",
    "    ort = Required(str)\n",
    "    ltps = Set(\"LTP\")\n",
    "\n",
    "class LTP(db.Entity):\n",
    "    lnr = Required(L)\n",
    "    tnr = Required(T)\n",
    "    pnr = Required(P)\n",
    "    menge = Required(int)\n",
    "    PrimaryKey(lnr, tnr, pnr)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "db.bind(provider='sqlite', filename=':memory:')\n",
    "\n",
    "db.generate_mapping(create_tables=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Diese Daten kommen direkt aus dem Arbeitsblatt:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "lnr = \"\"\"L1,MEIER,20,WETTER\n",
    "L2,MULLER,10,BERLIN\n",
    "L3,SCHMIDT,50,BERLIN\n",
    "L4,SCHULZ,30,WETTER\n",
    "L5,KRAUSE,40,MESCHEDE\"\"\"\n",
    "\n",
    "tnr = \"\"\"T1,STECKER,ROT,15\n",
    "T2,KABEL,BLAU,27\n",
    "T3,SCHALTER,WEISS,5\n",
    "T4,8080,ROT,2\n",
    "T5,DISKETTE,BLAU,12\n",
    "T6,SCHRAUBE,ROT,3\"\"\"\n",
    "\n",
    "pnr = \"\"\"P1,UFO,BERLIN\n",
    "P2,PLEITE,BONN\n",
    "P3,CPU,MESCHEDE\n",
    "P4,KAESE,MESCHEDE\n",
    "P5,POST,WETTER\n",
    "P6,SOFTWARE,ESSEN\n",
    "P7,KNALL,WETTER\n",
    "P8,UMZUG,BERLIN\"\"\"\n",
    "\n",
    "ltp = \"\"\"L1,T1,P8,1200\n",
    "L1,T1,P1,200\n",
    "L1,T1,P4,700\n",
    "L1,T4,P1,300\n",
    "L1,T6,P1,200\n",
    "L2,T3,P1,400\n",
    "L2,T3,P2,200\n",
    "L2,T3,P3,200\n",
    "L2,T3,P4,500\n",
    "L2,T3,P5,600\n",
    "L2,T3,P6,400\n",
    "L2,T3,P7,800\n",
    "L2,T3,P8,300\n",
    "L2,T5,P2,100\n",
    "L3,T3,P1,200\n",
    "L3,T4,P2,500\n",
    "L4,T6,P3,300\n",
    "L4,T6,P7,300\n",
    "L5,T2,P2,200\n",
    "L5,T2,P4,100\n",
    "L5,T5,P5,500\n",
    "L5,T5,P7,100\n",
    "L5,T6,P2,200\n",
    "L5,T1,P4,1000\n",
    "L5,T3,P4,1200\n",
    "L5,T4,P4,800\n",
    "L5,T5,P4,400\n",
    "L5,T6,P4,500\"\"\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Alle Rohdaten von oben mit den passenden Attributnamen versehen, damit man sie danach besser einfügen kann:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "(lnr_dicts, tnr_dicts, pnr_dicts, ltp_dicts) = ([\n",
    "    {str(db_object._attrs_[line.split(',').index(entry)]).split('.')[1]: entry for entry in line.split(',')}\n",
    "    for line in dataset.split() \n",
    "] for (dataset, db_object) in zip((lnr, tnr, pnr, ltp), (L, T, P, LTP)))\n",
    "\n",
    "with db_session:\n",
    "    insert_set = []\n",
    "    for (datadicts, db_object) in zip((lnr_dicts, tnr_dicts, pnr_dicts, ltp_dicts), (L, T, P, LTP)):\n",
    "        [db_object(**entry) for entry in datadicts]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Aufgaben 1 - 4 in Pony ORM"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Kann man hier oder weiter oben schon machen, wenn man den SQL-Output sehen will:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "set_sql_debug(True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "query1 = select((p.pname, p.ort) for p in P if p.ort == \"BERLIN\")\n",
    "query1.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "query2 = select((ltp.tnr.tname, ltp.pnr.ort) for ltp in LTP if ltp.pnr.ort == \"BERLIN\")\n",
    "query2.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "query3 = select(\n",
    "    (t.tnr, t.tname) \n",
    "    for t in T \n",
    "    for ltp in LTP if t == ltp.tnr\n",
    "    for l in L  if l.lname == \"SCHULZ\" and ltp.lnr == l\n",
    ")\n",
    "query3.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "query4 = select((ltp.lnr.lname) for ltp in LTP if ltp.lnr.sitz == \"MESCHEDE\" and ltp.pnr.ort == \"WETTER\")\n",
    "query4.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Pony ORM untersucht den Bytecode, um daraus das SQL zu basteln\n",
    "\n",
    "Mehr Infos hat der Autor Ponys mal hier gegeben: https://stackoverflow.com/a/16118756 Es lassen sich aber sonst auch Quellen für für die Decompile-Strategien Ponys finden."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from dis import dis\n",
    "from pony.orm.decompiling import decompile"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q1_gen = ((p.pname, p.ort) for p in P if p.ort == \"BERLIN\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dis(q1_gen.gi_frame.f_code)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "decompile(q1_gen)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Aufgaben 2 - 5 mit rohen SQL-Abfragen"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "db.select('''SELECT DISTINCT T.tname FROM LTP\n",
    "    JOIN P ON P.pnr = LTP.pnr \n",
    "    JOIN T ON T.tnr = LTP.tnr \n",
    "    WHERE P.ort = \"BERLIN\"''')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "db.select('''SELECT DISTINCT T.TNAME, T.TNR FROM LTP\n",
    "    JOIN T ON T.TNR = LTP.TNR\n",
    "    JOIN L ON L.LNR = LTP.LNR\n",
    "    WHERE L.LNAME = \"SCHULZ\"''')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "db.select('''SELECT DISTINCT L.lname from LTP\n",
    "    JOIN P ON P.pnr = LTP.pnr \n",
    "    JOIN L ON L.lnr = LTP.lnr\n",
    "    WHERE (L.sitz = \"MESCHEDE\" AND P.ort = \"WETTER\")''')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "db.select('''SELECT DISTINCT P.PNR, P.ORT FROM LTP\n",
    "  JOIN T ON T.TNR = LTP.TNR\n",
    "  JOIN P ON P.PNR = LTP.PNR\n",
    "  WHERE (T.FARBE = \"ROT\" AND T.GEWICHT > 5)''')"
   ]
  }
 ],
 "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.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}

requirements.txt

pony