{
 "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": 1,
   "metadata": {
    "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 8.0 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": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor = cnxnMA.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "MySQL 5.5.5-10.3.14-MariaDB-1:10.3.14+maria~trusty-log\n"
     ]
    }
   ],
   "source": [
    "print(cnxnMA.getinfo(pyodbc.SQL_DBMS_NAME),cnxnMA.getinfo(pyodbc.SQL_DBMS_VER))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<pyodbc.Cursor object at 0x000001FAB71930B0>\n"
     ]
    }
   ],
   "source": [
    "cur1=cnxnMA.cursor()\n",
    "rez1=cur1.execute('select * from jadralec')\n",
    "print(rez1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(22, 'Darko', 7, 45.0)\n",
      "(29, 'Borut', 1, 33.0)\n",
      "(31, 'Lojze', 8, 55.5)\n",
      "(32, 'Andrej', 8, 25.5)\n",
      "(58, 'Rajko', 10, 35.0)\n",
      "(64, 'Henrik', 7, 35.0)\n",
      "(71, 'Zdravko', 10, 16.0)\n",
      "(74, 'Henrik', 9, 35.0)\n",
      "(85, 'Anze', 3, 25.5)\n",
      "(95, 'Bine', 3, 63.5)\n"
     ]
    }
   ],
   "source": [
    "for v in rez1:\n",
    "    print(v)\n",
    "cur1.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "outputs": [],
   "source": [
    "rez = cursor.execute(\"SELECT * FROM jadralec\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "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": 18,
   "metadata": {
    "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": 19,
   "metadata": {
    "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": 20,
   "metadata": {
    "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": 21,
   "metadata": {
    "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": 22,
   "metadata": {
    "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": 23,
   "metadata": {
    "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": 24,
   "metadata": {},
   "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": 25,
   "metadata": {},
   "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); [IM002] [Microsoft][ODBC Driver Manager] Invalid connection string attribute (0)')\n"
     ]
    },
    {
     "ename": "TypeError",
     "evalue": "The first argument to execute must be a string or unicode query.",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mTypeError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-25-87f91e6043e7>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      6\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      7\u001b[0m \u001b[1;32mtry\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 8\u001b[1;33m     \u001b[0mnaj\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mcursor\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mnaj\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      9\u001b[0m \u001b[1;32mexcept\u001b[0m \u001b[0mpyodbc\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mDatabaseError\u001b[0m \u001b[1;32mas\u001b[0m \u001b[0me\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     10\u001b[0m     \u001b[0mprint\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m\"NAPAKA v poizvedbi!\\n\"\u001b[0m\u001b[1;33m,\u001b[0m\u001b[0me\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mTypeError\u001b[0m: The first argument to execute must be a string or unicode query."
     ]
    }
   ],
   "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": 26,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [],
   "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,
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "celltoolbar": "Slideshow",
  "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.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}