{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "## Povezava do podatkovne baze\n",
    "\n",
    "- modul pyodbc \n",
    "- povezovalni niz (eksplicitno, ali z uporabo DSN)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false,
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [],
   "source": [
    "import pyodbc\n",
    "from __future__ import print_function   # Kompatibilnost s Pythonom 2.7 in 3.x\n",
    "\n",
    "# Eksplicitna prijava brez DSN\n",
    "ConnectionStringEX = 'Driver={MySQL ODBC 5.3 UNICODE Driver}; \\\n",
    "                    Server=pb.fri.uni-lj.si;Database=tup; \\\n",
    "                    User=tup;Password=tupvaje'\n",
    "cnxnEX = pyodbc.connect(ConnectionStringEX)\n",
    "\n",
    "# Privzete vrednosti DSN\n",
    "ConnectionStringPG = 'DSN=Vaje-PG'\n",
    "cnxnPG = pyodbc.connect(ConnectionStringPG)\n",
    "# cnxnPG.cursor().execute(\"SET SCHEMA 'tup'\")  # Lahko izvedemo v definiciji DSN pod \"connect settings\"\n",
    "\n",
    "ConnectionStringMA = 'DSN=Vaje'\n",
    "cnxnMA = pyodbc.connect(ConnectionStringMA)\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Kurzor \n",
    "- Osnovni element interakcije s PB. \n",
    "- Naredimo ga na osnovi konkretne povezave.\n",
    "- Istočasno imamo lahko več kurzorjev."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "cursor = cnxnMA.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false,
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "outputs": [],
   "source": [
    "rez = cursor.execute(\"SELECT * FROM jadralec\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false,
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "jid\time\trating\tstarost\t\n",
      "22\tDarko\t7\t45.0\t\n",
      "29\tBorut\t1\t33.0\t\n",
      "31\tLojze\t8\t55.5\t\n",
      "32\tAndrej\t8\t25.5\t\n",
      "58\tRajko\t10\t35.0\t\n",
      "64\tHenrik\t7\t35.0\t\n",
      "71\tZdravko\t10\t16.0\t\n",
      "74\tHenrik\t9\t35.0\t\n",
      "85\tAnze\t3\t25.5\t\n",
      "95\tBine\t3\t63.5\t\n"
     ]
    }
   ],
   "source": [
    "# Glava in vsebina (prvi poskus)\n",
    "for g in rez.description:\n",
    "    print(g[0],end=\"\\t\")\n",
    "print()\n",
    "for r in rez:\n",
    "    for a in r:\n",
    "        print(a,end=\"\\t\")\n",
    "    print()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": false,
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "outputs": [],
   "source": [
    "rez = cursor.execute(\"SELECT * FROM jadralec\")\n",
    "telo = rez.fetchall()\n",
    "#telo = rez.fetchone()\n",
    "#telo = rez.fetchmany(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false,
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "jid\time\trating\tstarost\t\n"
     ]
    }
   ],
   "source": [
    "# Glava\n",
    "for g in rez.description:\n",
    "    print(g[0],end=\"\\t\")\n",
    "print()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false,
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "22 <class 'int'>\tDarko <class 'str'>\t7 <class 'int'>\t45.0 <class 'float'>\t\n",
      "29 <class 'int'>\tBorut <class 'str'>\t1 <class 'int'>\t33.0 <class 'float'>\t\n",
      "31 <class 'int'>\tLojze <class 'str'>\t8 <class 'int'>\t55.5 <class 'float'>\t\n",
      "32 <class 'int'>\tAndrej <class 'str'>\t8 <class 'int'>\t25.5 <class 'float'>\t\n",
      "58 <class 'int'>\tRajko <class 'str'>\t10 <class 'int'>\t35.0 <class 'float'>\t\n",
      "64 <class 'int'>\tHenrik <class 'str'>\t7 <class 'int'>\t35.0 <class 'float'>\t\n",
      "71 <class 'int'>\tZdravko <class 'str'>\t10 <class 'int'>\t16.0 <class 'float'>\t\n",
      "74 <class 'int'>\tHenrik <class 'str'>\t9 <class 'int'>\t35.0 <class 'float'>\t\n",
      "85 <class 'int'>\tAnze <class 'str'>\t3 <class 'int'>\t25.5 <class 'float'>\t\n",
      "95 <class 'int'>\tBine <class 'str'>\t3 <class 'int'>\t63.5 <class 'float'>\t\n"
     ]
    }
   ],
   "source": [
    "# Vsebina in tipi\n",
    "for r in telo:\n",
    "    for a in r:\n",
    "        print(a,type(a),end=\"\\t\")\n",
    "    print()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": false,
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Vseh vrstic je 10\n",
      "jid\time\trating\tstarost\t\n",
      "-------------------------------\n",
      "22\tDarko\t7\t45.0\t\n",
      "29\tBorut\t1\t33.0\t\n",
      "31\tLojze\t8\t55.5\t\n",
      "32\tAndrej\t8\t25.5\t\n",
      "58\tRajko\t10\t35.0\t\n",
      "64\tHenrik\t7\t35.0\t\n",
      "71\tZdravko\t10\t16.0\t\n",
      "74\tHenrik\t9\t35.0\t\n",
      "85\tAnze\t3\t25.5\t\n",
      "95\tBine\t3\t63.5\t\n"
     ]
    }
   ],
   "source": [
    "# Glava in vsebina (drugi poskus)\n",
    "print(\"Vseh vrstic je\", rez.rowcount)\n",
    "for g in rez.description:\n",
    "    print(g[0],end=\"\\t\")\n",
    "print(\"\\n\"+\"-\"*31)\n",
    "# Vsebina\n",
    "for r in telo:\n",
    "    for a in r:\n",
    "        print(a,end=\"\\t\")\n",
    "    print()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true,
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "source": [
    "## Življenjska doba vsebine kurzorja\n",
    "\n",
    "- le ena iteracija!\n",
    "\n",
    "Metode kurzorja:\n",
    "- fetchall(): vrne seznam vseh vrstic\n",
    "- fetchone(): vrne naslednjo neprebrano vrstico (**pozor: to ni seznam!**)\n",
    "- fetchmany(*n*): vrne naslednjih *n* neprebranih vrstic\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true,
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "source": [
    "## Naloga: poišči šifre najkrajših čolnov!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": false,
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "101 34\n"
     ]
    }
   ],
   "source": [
    "naj = cursor.execute(\"SELECT cid, dolzina FROM coln\")\n",
    "\n",
    "mind = 1000 # Nekaj velikega\n",
    "mins = -1   # Nekaj neveljavnega \n",
    "\n",
    "for (s,d) in naj:\n",
    "    if d < mind:\n",
    "        (mins, mind) = (s,d)\n",
    "        \n",
    "print (mins, mind) "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Bolje: kombinacija SQL in Pythona"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "101 34\n",
      "102 34\n"
     ]
    }
   ],
   "source": [
    "naj = cursor.execute(\"\"\"\n",
    "        SELECT cid, dolzina \n",
    "        FROM coln \n",
    "        WHERE dolzina = (SELECT MIN(dolzina) FROM coln) \n",
    "      \"\"\")\n",
    "\n",
    "\n",
    "for (s,d) in naj:       \n",
    "   print (s, d) "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "## Obravnava izjem\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "naj = cursor.execute(\"\"\"\n",
    "        SELECT cid, dolzina \n",
    "        FROM coln \n",
    "        WHERE dolzina = (SELECT MIN(dolzina) FROM coln) \n",
    "      \"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "NAPAKA v povezav!\n",
      " ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')\n",
      "NAPAKA v poizvedbi!\n",
      " ('42000', \"[42000] [MySQL][ODBC 5.3(w) Driver][mysqld-5.5.5-10.1.18-MariaDB-1~trusty]You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WHEN dolzina = (SELECT MIN(dolzina)                                           FR' at line 1 (1064) (SQLExecDirectW)\")\n"
     ]
    }
   ],
   "source": [
    "try:\n",
    "    ConnectionStringBLA = 'Bla Bla'\n",
    "    cnxnPG = pyodbc.connect(ConnectionStringBLA)\n",
    "except Exception as e:\n",
    "    print(\"NAPAKA v povezav!\\n\",e)\n",
    "\n",
    "try:\n",
    "    naj = cursor.execute(naj)\n",
    "except pyodbc.DatabaseError as e:\n",
    "    print(\"NAPAKA v poizvedbi!\\n\",e)\n",
    "    \n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Zaključek dela: vedno zaprite povezave (po možnosti z obravnavo napak)!\n",
    "Poženi dvakrat.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": false,
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Zapiranje povezave: Attempt to use a closed connection.\n",
      "Zapiranje povezave: Attempt to use a closed connection.\n",
      "Zapiranje povezave: Attempt to use a closed connection.\n"
     ]
    }
   ],
   "source": [
    "try:\n",
    "    cnxnEX.close()\n",
    "except Exception as e:\n",
    "    print(\"Zapiranje povezave:\",e)\n",
    "\n",
    "try:\n",
    "    cnxnPG.close()\n",
    "except Exception as e:\n",
    "    print(\"Zapiranje povezave:\",e)\n",
    "\n",
    "try:\n",
    "    cnxnMA.close()\n",
    "except Exception as e:\n",
    "    print(\"Zapiranje povezave:\",e)\n",
    "\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "celltoolbar": "Slideshow",
  "kernelspec": {
   "display_name": "Python [default]",
   "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
}