{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Izračun skladnosti jadralcev in čolnov \n",
    "\n",
    "Povezava na bazo (eksplicitna, brez DSN)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import pyodbc\n",
    "\n",
    "conn = \"DRIVER={MySQL ODBC 5.3 Unicode Driver};SERVER=pb.fri.uni-lj.si;DATABASE=tup;UID=tup;PWD=tupvaje\"\n",
    "cnxn = pyodbc.connect(conn)\n",
    "cursor = cnxn.cursor()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Na fakultetnem MariaDB strežniku imamo dostop do dveh podatkovnih baz:\n",
    "* `tup`: samo za branje\n",
    "* `sandbox`: vse pravice\n",
    "    \n",
    "Dostop: `ime_baze.ime_tabele`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "cursor.execute(\"DROP TABLE IF EXISTS sandbox.optimal\")\n",
    "cursor.execute(\"CREATE TABLE sandbox.optimal ( jid INTEGER, cid INTEGER, \"\n",
    "               \"skladnost INTEGER, PRIMARY KEY (jid));\")\n",
    "cursor.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Izvedemo poizvedbo in prenesemo vse rezultate, ter jih obdelamo.\n",
    "Alternativna rešitev: uporaba dveh kurzorjev."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "jadralci = cursor.execute(\"SELECT jid FROM Jadralec\").fetchall()\n",
    "for jadralec in jadralci:\n",
    "    result = cursor.execute(\n",
    "        \"SELECT cid, MOD(?+cid, 11) as skladnost FROM Coln \"\n",
    "        \"ORDER BY skladnost DESC LIMIT 1\", jadralec.jid).fetchone()\n",
    "    \n",
    "    jid = jadralec.jid\n",
    "    cid = result.cid\n",
    "    skladnost = result.skladnost\n",
    "\n",
    "    cursor.execute(\"INSERT INTO sandbox.optimal VALUES (?,?,?)\", jid, cid, skladnost)\n",
    "    cursor.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Izpis vsebine dobljene tabele `optimal`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(22, 104, 5)\n",
      "(29, 102, 10)\n",
      "(31, 104, 3)\n",
      "(32, 104, 4)\n",
      "(58, 104, 8)\n",
      "(64, 104, 3)\n",
      "(71, 104, 10)\n",
      "(74, 101, 10)\n",
      "(85, 101, 10)\n",
      "(95, 102, 10)\n"
     ]
    }
   ],
   "source": [
    "optimal = cursor.execute(\"SELECT * FROM sandbox.optimal\")\n",
    "for r in optimal:\n",
    "    print (r)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ne pozabite: ***vedno*** zapremo povezavo!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "cnxn.close()"
   ]
  }
 ],
 "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.4.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}