<jupyter_start><jupyter_text>IntegratedML applied to biomedical data, using PyODBCThis notebook demonstrates the following:- Connecting to InterSystems IRIS via PyODBC connection- Creating, Training and Executing (PREDICT() function) an IntegratedML machine learning model, applied to breast cancer tumor diagnoses- INSERTING machine learning predictions into a new SQL table- Executing a relatively complex SQL query containing IntegratedML PREDICT() and PROBABILITY() functions, and flexibly using the results to filter and sort the output  ODBC and pyODBC ResourcesOften, 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!https://gettingstarted.intersystems.com/development-setup/odbc-connections/https://irisdocs.intersystems.com/irislatest/csp/docbook/DocBook.UI.Page.cls?KEY=BNETODBC_supportBNETODBC_support_pyodbchttps://stackoverflow.com/questions/46405777/connect-docker-python-to-sql-server-with-pyodbchttps://stackoverflow.com/questions/44527452/cant-open-lib-odbc-driver-13-for-sql-server-sym-linking-issue<jupyter_code># make the notebook full screen
from IPython.core.display import display, HTML
display(HTML("<style>.container { width:100% !important; }</style>"))<jupyter_output><empty_output><jupyter_text>1. Install system packages for ODBC<jupyter_code>!apt-get update
!apt-get install gcc
!apt-get install -y tdsodbc unixodbc-dev
!apt install unixodbc-bin -y
!apt-get clean<jupyter_output>Get:1 http://security.ubuntu.com/ubuntu bionic-security InRelease [88.7 kB]    
Get:2 http://archive.ubuntu.com/ubuntu bionic InRelease [242 kB]
Get:3 http://archive.ubuntu.com/ubuntu bionic-updates InRelease [88.7 kB]
Get:4 http://archive.ubuntu.com/ubuntu bionic-backports InRelease [74.6 kB]
Get:5 http://security.ubuntu.com/ubuntu bionic-security/universe amd64 Packages [869 kB]
Get:6 http://archive.ubuntu.com/ubuntu bionic/multiverse amd64 Packages [186 kB]
Get:7 http://security.ubuntu.com/ubuntu bionic-security/main amd64 Packages [1003 kB]
Get:8 http://archive.ubuntu.com/ubuntu bionic/main amd64 Packages [1344 kB]    
Get:9 http://security.ubuntu.com/ubuntu bionic-security/multiverse amd64 Packages [9282 B]
Get:10 http://security.ubuntu.com/ubuntu bionic-security/restricted amd64 Packages [89.0 kB]
Get:11 http://archive.ubuntu.com/ubuntu bionic/universe amd64 Packages [11.3 MB]
Get:12 http://archive.ubuntu.com/ubuntu bionic/restricted amd64 Packages [13.5 kB]
Get:13 http://archive[...]<jupyter_text>Use this command to troubleshoot a failed pyodbc installation:!pip install --upgrade --global-option=build_ext --global-option="-I/usr/local/include"  --global-option="-L/usr/local/lib" pyodbc<jupyter_code>!pip install pyodbc
!rm /etc/odbcinst.ini
!rm /etc/odbc.ini
!ln -s /tf/odbcinst.ini /etc/odbcinst.ini
!ln -s /tf/odbc.ini /etc/odbc.ini
!cat /tf/odbcinst.ini
!cat /tf/odbc.ini
!odbcinst -j<jupyter_output>unixODBC 2.3.4
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /root/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8<jupyter_text>2. Verify you see "InterSystems ODBC35" in the drivers list<jupyter_code>import pyodbc
print(pyodbc.drivers())<jupyter_output>['InterSystems ODBC35']<jupyter_text>3. Get an ODBC connection<jupyter_code>import pyodbc 
import time


#input("Hit any key to start")
dsn = 'IRIS QuickML demo via PyODBC'
server = 'irisimlsvr' #'192.168.99.101' 
port = '51773' #'9091'
database = 'USER' 
username = 'SUPERUSER' 
password = '<PASSWORD>' 
cnxn = pyodbc.connect('DRIVER={InterSystems ODBC35};SERVER='+server+';PORT='+port+';DATABASE='+database+';UID='+username+';PWD='+ password)

### Ensure it read strings correctly.
cnxn.setdecoding(pyodbc.SQL_CHAR, encoding='utf8')
cnxn.setdecoding(pyodbc.SQL_WCHAR, encoding='utf8')
cnxn.setencoding(encoding='utf8')<jupyter_output><empty_output><jupyter_text>4. Get a cursor; start the timer<jupyter_code>cursor = cnxn.cursor()
start= time.clock()<jupyter_output><empty_output><jupyter_text>5. specify the training data, and give a model name<jupyter_code>dataTable = 'SQLUser.BreastCancer'
dataTablePredict = 'Result02'
dataColumn =  'Diagnosis'
dataColumnPredict = "PredictedDiagnosis"
modelName = "bc" #chose a name - must be unique in server end<jupyter_output><empty_output><jupyter_text>6. Train and predict<jupyter_code>cursor.execute("CREATE MODEL %s PREDICTING (%s)  FROM %s" % (modelName, dataColumn, dataTable))
cursor.execute("TRAIN MODEL %s FROM %s" % (modelName, dataTable))
cursor.execute("Create Table %s (%s VARCHAR(100), %s VARCHAR(100))" % (dataTablePredict, dataColumnPredict, dataColumn))
cursor.execute("INSERT INTO %s  SELECT TOP 20 PREDICT(%s) AS %s, %s FROM %s" % (dataTablePredict, modelName, dataColumnPredict, dataColumn, dataTable)) 
cnxn.commit()<jupyter_output><empty_output><jupyter_text>7. Show the predict result<jupyter_code>import pandas as pd
from IPython.display import display

df1 = pd.read_sql("SELECT * from %s ORDER BY ID" % dataTablePredict, cnxn)
display(df1)<jupyter_output><empty_output><jupyter_text>8. Show a complicated queryIntegratedML function PREDICT() and PROBABILITY() can appear virtually anywhere in a SQL query, for maximal flexibility!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.<jupyter_code>df2 = pd.read_sql("SELECT ID, PROBABILITY(bc FOR 'M') AS Probability, Diagnosis FROM %s \
                    WHERE MeanArea BETWEEN 300 AND 600 AND MeanRadius > 5 AND PREDICT(%s) = 'M' \
                    ORDER BY Probability" % (dataTable, modelName),cnxn)         
display(df2)<jupyter_output><empty_output><jupyter_text>9. Close and clean<jupyter_code>cnxn.close()
end= time.clock()
print ("Total elapsed time: ")
print (end-start)
#input("Hit any key to end")<jupyter_output>Total elapsed time: 
0.6454539999999991
{'fraction_non_alphanumeric': 0.11778769525023908, 'fraction_numerical': 0.027892891297417916, 'mean_word_length': 5.522869022869023, 'pattern_counts': {'":': 0, '<': 44, '<?xml version=': 0, '>': 45, 'https://': 4, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 1, 'substrings_counts': 1, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '11', 'dir': 'jupyter-structured-clean-dedup', 'id': '11928', 'license': 'MIT', 'n_tokens_mistral': 2319, 'n_tokens_neox': 2093, 'n_words': 566, 'path': 'jupyter-samples/biomedical-integratedml-PyODBC-Copy1.ipynb', 'repo_name': 'jrpereirajr/iris-sample-rest-angular', 'size': '6267'}