{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Povezava do podatkovne baze iz Pythona preko ODBC\n",
    "\n",
    "Potrebujemo:\n",
    "- ODBC gonilnik, 64 ali 32-bitni, odvisno od sistema\n",
    "- pyodbc paket za Python"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Uvoz modula\n",
    "import pyodbc"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Specifikacija povezave\n",
    "- Povezovalni niz: komponente ločene s podpičji\n",
    "- DSN: Data Source Name\n",
    "- USER ali UID: uporabniško ime\n",
    "- PASSWORD ali PWD: geslo\n",
    "- DATABASE: baza ali shema\n",
    "- ... in še mnogo drugih\n",
    "\n",
    "Vir: https://www.connectionstrings.com/connection-strings-explained/"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### MariaDB ali MySQL preko Data Source upravljalca"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "MySQL 5.5.5-10.1.18-MariaDB-1~trusty\n"
     ]
    }
   ],
   "source": [
    "ConnectionString = 'DSN=Vaje'\n",
    "c1 = pyodbc.connect(ConnectionString)\n",
    "print(c1.getinfo(pyodbc.SQL_DBMS_NAME),c1.getinfo(pyodbc.SQL_DBMS_VER))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### MariaDB ali MySQL - eksplicitna specifikacija povezovalnega niza"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "MySQL 5.5.5-10.1.18-MariaDB-1~trusty\n"
     ]
    }
   ],
   "source": [
    "ConnectionString = 'Driver={MySQL ODBC 5.3 UNICODE Driver}; \\\n",
    "                    Server=pb.fri.uni-lj.si;Database=tup; \\\n",
    "                    User=tup;Password=tupvaje'\n",
    "c1 = pyodbc.connect(ConnectionString)\n",
    "print(c1.getinfo(pyodbc.SQL_DBMS_NAME),c1.getinfo(pyodbc.SQL_DBMS_VER))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### PostgreSQL preko Data Source upravljalca"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "PostgreSQL 9.6.0\n"
     ]
    }
   ],
   "source": [
    "ConnectionString = 'DSN=Vaje-PG'\n",
    "c1 = pyodbc.connect(ConnectionString)\n",
    "print(c1.getinfo(pyodbc.SQL_DBMS_NAME),c1.getinfo(pyodbc.SQL_DBMS_VER))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Testiranje povezave (MariaDB in PostgreSQL)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "cur1 = c1.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "rez1 = cur1.execute('SELECT * FROM jadralec')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<pyodbc.Cursor object at 0x0000011D110033F0>\n"
     ]
    }
   ],
   "source": [
    "print(rez1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": false
   },
   "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)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "c1.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "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
}