jupyter-samples/biomedical-integratedml-PyODBC.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# IntegratedML applied to biomedical data, using PyODBC\n",
    "This notebook demonstrates the following:\n",
    "- Connecting to InterSystems IRIS via PyODBC connection\n",
    "- Creating, Training and Executing (PREDICT() function) an IntegratedML machine learning model, applied to breast cancer tumor diagnoses\n",
    "- INSERTING machine learning predictions into a new SQL table\n",
    "- Executing a relatively complex SQL query containing IntegratedML PREDICT() and PROBABILITY() functions, and flexibly using the results to filter and sort the output"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### ODBC and pyODBC Resources\n",
    "Often, connecting to a database is more than half the battle when developing SQL-heavy applications, especially if you are not familiar with the tools, or more importantly the particular database system. If this is the case, and you are just getting started using PyODBC and InterSystems IRIS, this notebook and these resources below may help you get up to speed!\n",
    "\n",
    "https://gettingstarted.intersystems.com/development-setup/odbc-connections/\n",
    "\n",
    "https://irisdocs.intersystems.com/irislatest/csp/docbook/DocBook.UI.Page.cls?KEY=BNETODBC_support#BNETODBC_support_pyodbc\n",
    "\n",
    "https://stackoverflow.com/questions/46405777/connect-docker-python-to-sql-server-with-pyodbc\n",
    "\n",
    "https://stackoverflow.com/questions/44527452/cant-open-lib-odbc-driver-13-for-sql-server-sym-linking-issue"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style>.container { width:100% !important; }</style>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# make the notebook full screen\n",
    "from IPython.core.display import display, HTML\n",
    "display(HTML(\"<style>.container { width:100% !important; }</style>\"))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1. Install system packages for ODBC"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Hit:1 http://archive.ubuntu.com/ubuntu bionic InRelease\n",
      "Get:2 http://archive.ubuntu.com/ubuntu bionic-updates InRelease [88.7 kB]\n",
      "Get:3 http://security.ubuntu.com/ubuntu bionic-security InRelease [88.7 kB]    \n",
      "Get:4 http://archive.ubuntu.com/ubuntu bionic-backports InRelease [74.6 kB]    \n",
      "Fetched 252 kB in 1s (322 kB/s)                                                \n",
      "Reading package lists... Done\n",
      "Reading package lists... Done\n",
      "Building dependency tree       \n",
      "Reading state information... Done\n",
      "gcc is already the newest version (4:7.4.0-1ubuntu2.3).\n",
      "0 upgraded, 0 newly installed, 0 to remove and 103 not upgraded.\n",
      "Reading package lists... Done\n",
      "Building dependency tree       \n",
      "Reading state information... Done\n",
      "unixodbc-dev is already the newest version (2.3.4-1.1ubuntu3).\n",
      "tdsodbc is already the newest version (1.00.82-2ubuntu0.1).\n",
      "0 upgraded, 0 newly installed, 0 to remove and 103 not upgraded.\n",
      "Reading package lists... Done\n",
      "Building dependency tree       \n",
      "Reading state information... Done\n",
      "unixodbc-bin is already the newest version (2.3.0-4build1).\n",
      "0 upgraded, 0 newly installed, 0 to remove and 103 not upgraded.\n"
     ]
    }
   ],
   "source": [
    "!apt-get update\n",
    "!apt-get install gcc\n",
    "!apt-get install -y tdsodbc unixodbc-dev\n",
    "!apt install unixodbc-bin -y\n",
    "!apt-get clean "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Use this command to troubleshoot a failed pyodbc installation:\n",
    "!pip install --upgrade --global-option=build_ext --global-option=\"-I/usr/local/include\"  --global-option=\"-L/usr/local/lib\" pyodbc"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: pyodbc in /usr/local/lib/python3.6/dist-packages (4.0.32)\n",
      "\u001b[33mWARNING: Running pip as the 'root' user can result in broken permissions and conflicting behaviour with the system package manager. It is recommended to use a virtual environment instead: https://pip.pypa.io/warnings/venv\u001b[0m\n"
     ]
    }
   ],
   "source": [
    "!pip install pyodbc"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "!rm /etc/odbcinst.ini\n",
    "!rm /etc/odbc.ini"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "!ln -s /tf/odbcinst.ini /etc/odbcinst.ini\n",
    "!ln -s /tf/odbc.ini /etc/odbc.ini"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[InterSystems ODBC35]\r\n",
      "UsageCount=1\r\n",
      "Driver=/tf/libirisodbcu35.so\r\n",
      "Setup=/tf/libirisodbcu35.so\r\n",
      "SQLLevel=1\r\n",
      "FileUsage=0\r\n",
      "DriverODBCVer=02.10\r\n",
      "ConnectFunctions=YYN\r\n",
      "APILevel=1\r\n",
      "DEBUG=1\r\n",
      "CPTimeout=<not pooled>\r\n",
      "\r\n"
     ]
    }
   ],
   "source": [
    "!cat /tf/odbcinst.ini"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[user]\r\n",
      "Driver=InterSystems ODBC35\r\n",
      "Protocol=TCP\r\n",
      "Host=irisimlsvr\r\n",
      "Port=51773\r\n",
      "Namespace=USER\r\n",
      "UID=SUPERUSER\r\n",
      "Password=SYS\r\n",
      "Description=Sample namespace\r\n",
      "Query Timeout=0\r\n",
      "Static Cursors=0\r\n",
      "\r\n"
     ]
    }
   ],
   "source": [
    "!cat /tf/odbc.ini"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "unixODBC 2.3.4\r\n",
      "DRIVERS............: /etc/odbcinst.ini\r\n",
      "SYSTEM DATA SOURCES: /etc/odbc.ini\r\n",
      "FILE DATA SOURCES..: /etc/ODBCDataSources\r\n",
      "USER DATA SOURCES..: /root/.odbc.ini\r\n",
      "SQLULEN Size.......: 8\r\n",
      "SQLLEN Size........: 8\r\n",
      "SQLSETPOSIROW Size.: 8\r\n"
     ]
    }
   ],
   "source": [
    "!odbcinst -j"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2. Verify you see \"InterSystems ODBC35\" in the drivers list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['InterSystems ODBC35']\n"
     ]
    }
   ],
   "source": [
    "import pyodbc\n",
    "print(pyodbc.drivers())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3. Get an ODBC connection "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pyodbc \n",
    "import time\n",
    "\n",
    "\n",
    "#input(\"Hit any key to start\")\n",
    "dsn = 'IRIS IntegratedML demo via PyODBC'\n",
    "server = 'irisimlsvr' #'192.168.99.101' \n",
    "port = '1972' #'9091'\n",
    "database = 'USER' \n",
    "username = 'SUPERUSER' \n",
    "password = 'SYS' \n",
    "cnxn = pyodbc.connect('DRIVER={InterSystems ODBC35};SERVER='+server+';PORT='+port+';DATABASE='+database+';UID='+username+';PWD='+ password)\n",
    "\n",
    "### Ensure it read strings correctly.\n",
    "cnxn.setdecoding(pyodbc.SQL_CHAR, encoding='utf8')\n",
    "cnxn.setdecoding(pyodbc.SQL_WCHAR, encoding='utf8')\n",
    "cnxn.setencoding(encoding='utf8')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4. Get a cursor; start the timer"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor = cnxn.cursor()\n",
    "start= time.clock()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 5. Specify the training data, and give a model name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "dataTable = 'Biomedical.BreastCancer'\n",
    "dataTablePredict = 'Result02'\n",
    "dataColumn =  'Diagnosis'\n",
    "dataColumnPredict = \"PredictedDiagnosis\"\n",
    "modelName = \"bc\" #chose a name - must be unique in server end"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    " ### Cleaning before retrying"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "#If we re-run the notebook just drop model and table\n",
    "#cursor.execute(\"DROP MODEL %s\" % modelName)\n",
    "#cursor.execute(\"DROP TABLE %s\" % dataTablePredict)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 6. Train and predict"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute(\"CREATE MODEL %s PREDICTING (%s)  FROM %s\" % (modelName, dataColumn, dataTable))\n",
    "cursor.execute(\"TRAIN MODEL %s FROM %s\" % (modelName, dataTable))\n",
    "cursor.execute(\"Create Table %s (%s VARCHAR(100), %s VARCHAR(100))\" % (dataTablePredict, dataColumnPredict, dataColumn))\n",
    "cursor.execute(\"INSERT INTO %s  SELECT TOP 20 PREDICT(%s) AS %s, %s FROM %s\" % (dataTablePredict, modelName, dataColumnPredict, dataColumn, dataTable)) \n",
    "cnxn.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 7. Show the predict result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>PredictedDiagnosis</th>\n",
       "      <th>Diagnosis</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>M</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>M</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>M</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>M</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>M</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>M</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>M</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>M</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>M</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>M</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>M</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>M</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>M</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>M</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>M</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>M</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>M</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>M</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>M</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>B</td>\n",
       "      <td>B</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   PredictedDiagnosis Diagnosis\n",
       "0                   M         M\n",
       "1                   M         M\n",
       "2                   M         M\n",
       "3                   M         M\n",
       "4                   M         M\n",
       "5                   M         M\n",
       "6                   M         M\n",
       "7                   M         M\n",
       "8                   M         M\n",
       "9                   M         M\n",
       "10                  M         M\n",
       "11                  M         M\n",
       "12                  M         M\n",
       "13                  M         M\n",
       "14                  M         M\n",
       "15                  M         M\n",
       "16                  M         M\n",
       "17                  M         M\n",
       "18                  M         M\n",
       "19                  B         B"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "from IPython.display import display\n",
    "\n",
    "df1 = pd.read_sql(\"SELECT * from %s ORDER BY ID\" % dataTablePredict, cnxn)\n",
    "display(df1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 8. Show a complicated query\n",
    "IntegratedML function PREDICT() and PROBABILITY() can appear virtually anywhere in a SQL query, for maximal flexibility!\n",
    "Below we are SELECTing columns as well as the result of the PROBABILITY function, and then filtering on the result of the PREDICT function. To top it off, ORDER BY is using the output of PROBSBILITY for sorting."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>Probability</th>\n",
       "      <th>diagnosis</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>74</td>\n",
       "      <td>0.508227</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>298</td>\n",
       "      <td>0.675269</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>216</td>\n",
       "      <td>0.863261</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>42</td>\n",
       "      <td>0.955022</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>147</td>\n",
       "      <td>0.961170</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>101</td>\n",
       "      <td>0.994392</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>45</td>\n",
       "      <td>0.995220</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>6</td>\n",
       "      <td>0.995779</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>40</td>\n",
       "      <td>0.996360</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>194</td>\n",
       "      <td>0.998938</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>8</td>\n",
       "      <td>0.999320</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>127</td>\n",
       "      <td>0.999456</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>502</td>\n",
       "      <td>0.999470</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>172</td>\n",
       "      <td>0.999546</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>15</td>\n",
       "      <td>0.999659</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>44</td>\n",
       "      <td>0.999668</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>436</td>\n",
       "      <td>0.999765</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>106</td>\n",
       "      <td>0.999839</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>48</td>\n",
       "      <td>0.999894</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>230</td>\n",
       "      <td>0.999894</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>9</td>\n",
       "      <td>0.999991</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>513</td>\n",
       "      <td>0.999994</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>197</td>\n",
       "      <td>0.999995</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>65</td>\n",
       "      <td>0.999995</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>32</td>\n",
       "      <td>0.999999</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>4</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>380</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>10</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>204</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     ID  Probability diagnosis\n",
       "0    74     0.508227         M\n",
       "1   298     0.675269         M\n",
       "2   216     0.863261         M\n",
       "3    42     0.955022         M\n",
       "4   147     0.961170         M\n",
       "5   101     0.994392         M\n",
       "6    45     0.995220         M\n",
       "7     6     0.995779         M\n",
       "8    40     0.996360         M\n",
       "9   194     0.998938         M\n",
       "10    8     0.999320         M\n",
       "11  127     0.999456         M\n",
       "12  502     0.999470         M\n",
       "13  172     0.999546         M\n",
       "14   15     0.999659         M\n",
       "15   44     0.999668         M\n",
       "16  436     0.999765         M\n",
       "17  106     0.999839         M\n",
       "18   48     0.999894         M\n",
       "19  230     0.999894         M\n",
       "20    9     0.999991         M\n",
       "21  513     0.999994         M\n",
       "22  197     0.999995         M\n",
       "23   65     0.999995         M\n",
       "24   32     0.999999         M\n",
       "25    4     1.000000         M\n",
       "26  380     1.000000         M\n",
       "27   10     1.000000         M\n",
       "28  204     1.000000         M"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "df2 = pd.read_sql(\"SELECT ID, PROBABILITY(bc FOR 'M') AS Probability, Diagnosis FROM %s \\\n",
    "                    WHERE Mean_Area BETWEEN 300 AND 600 AND Mean_Radius > 5 AND PREDICT(%s) = 'M' \\\n",
    "                    ORDER BY Probability\" % (dataTable, modelName),cnxn)         \n",
    "display(df2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 9. Close and clean "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total elapsed time: \n",
      "0.731681\n"
     ]
    }
   ],
   "source": [
    "cnxn.close()\n",
    "end= time.clock()\n",
    "print (\"Total elapsed time: \")\n",
    "print (end-start)\n",
    "#input(\"Hit any key to end\")"
   ]
  }
 ],
 "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.6.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}