关键字：Python，JDBC，SQL，IRIS，Jupyter Notebook，Pandas，Numpy ，机器学习
1. 目的
这是一个用于演示的 5 分钟快速笔记，通过 Jupyter Notebook 中的 Python 3 调用 IRIS JDBC 驱动程序，以经由 SQL 语法从 IRIS 数据库实例读取数据和向 IRIS 数据库实例写入数据。
去年，我发表了关于将 Python 绑定到 Cache 数据库的简要笔记（第 4.7 节）。 如何使用 Python 挂入 IRIS 数据库以将其数据读入 Pandas 数据框和 NumPy 数组进行常规分析，然后再将一些经过预处理或标准化的数据写回 IRIS 中，准备进一步用于 ML/DL 管道，现在可能是时候回顾一些选项和讨论了。
一些立即浮现的快速选项：
- ODBC：Python 3 和原生 SQL 的 PyODBC？
- JDBC：Pyhton 3 和原生 SQL 的 JayDeBeApi？
- Spark：PySpark 和 SQL？
- Python Native API for IRIS：超越先前的 Python Binding for Cache？
- ** IPtyhon Magic SQL %%sql**? 它可以支持 IRIS 了吗？
这里有漏掉其他选项吗？ 我有兴趣尝试任何选项。
2. 范围
我们是不是应该从普通的 JDBC 方法开始？ 下一个快速笔记将总结 ODBC、Spark 和 Python Native API。
范围内：
此快速演示涉及以下常见组件：
Anaconda
Jupyter Notebook
Python 3
JayDeBeApi
JPyPe
Pandas
NumPy
一个 IRIS 2019.x 实例
范围外：
本快速笔记不会涉及以下内容，但它们也很重要，可以使用特定的站点解决方案、部署和服务单独解决：
安全端到端。
非功能性能等。
问题排查和支持。
许可。
3. 演示
3.1 运行 IRIS 实例：
我只运行了一个 IRIS 2019.4 容器，作为“远程”数据库服务器。 您可以使用任何您有权利访问的 IRIS 实例。
zhongli@UKM5530ZHONGLI MINGW64 /c/Program Files/Docker Toolbox
$ docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
d86be69a03ab quickml-demo "/iris-main" 3 days ago Up 3 days (healthy) 0.0.0.0:9091->51773/tcp, 0.0.0.0:9092->52773/tcp quickml
3.2 Anaconda 和 Jupyter Notebook：
我们将在笔记本电脑中重用相同的设置方法，这里对应 Anaconda（第 4.1 节），这里对应 Jupyter Notebook（第 4 节）。 Python 3.x 在这一步安装。
3.3 安装 JayDeBeApi 和 JPyPe：
启动 JupyterNotebook，然后在其单元格中运行以下内容设置 Python-to-JDBC/Java 桥：
!conda install --yes -c conda-forge jaydebeapi
JayDeBeApi 在撰写本文时（2020 年 1 月）使用 JPype 0.7，该版本由于一个已知错误无法运行，必须降级为 0.6.3
!conda install --yes -c conda-forge JPype1=0.6.3 --force-reinstall
3.4 通过 JDBC 连接到 IRIS 数据库
这里有一个正式的使用 JDBC 连接到 IRIS 的文档。
对于通过 JDBC 执行 Python SQL，我以下面的代码为例。 它连接到此 IRIS 实例的“USER”命名空间内的数据表“DataMining.IrisDataset”。
### 1. Set environment variables, if necessary<br>#import os<br>#os.environ['JAVA_HOME']='C:\Progra~1\Java\jdk1.8.0_241'<br>#os.environ['CLASSPATH'] = 'C:\interSystems\IRIS20194\dev\java\lib\JDK18\intersystems-jdbc-3.0.0.jar'<br>#os.environ['HADOOP_HOME']='C:\hadoop\bin' #winutil binary must be in Hadoop's Home
### 2. Get jdbc connection and cursor<br><strong>import jaydebeapi<br>url = "jdbc:IRIS://192.168.99.101:9091/USER"<br>driver = 'com.intersystems.jdbc.IRISDriver'<br>user = "SUPERUSER"<br>password = "SYS"</strong><br>#libx = "C:/InterSystems/IRIS20194/dev/java/lib/JDK18"<br><strong>jarfile = "C:/InterSystems/IRIS20194/dev/java/lib/JDK18/intersystems-jdbc-3.0.0.jar"</strong>
conn = jaydebeapi.connect(driver, url, [user, password], jarfile)<br>curs = conn.cursor()
### 3. specify the source data table<br><strong>dataTable = 'DataMining.IrisDataset'</strong>
### 4. Get the result and display<br><strong>curs.execute("select TOP 20 * from %s" % dataTable)<br>result = curs.fetchall()<br>print("Total records: " + str(len(result)))<br>for i in range(len(result)):<br> print(result[i])</strong>
### 5. CLose and clean - I keep them open for next accesses.<br><strong>#curs.close()<br>#conn.close()</strong>
Total records: 150
(1, 1.4, 0.2, 5.1, 3.5, 'Iris-setosa')
(2, 1.4, 0.2, 4.9, 3.0, 'Iris-setosa')
(3, 1.3, 0.2, 4.7, 3.2, 'Iris-setosa')
... ...
(49, 1.5, 0.2, 5.3, 3.7, 'Iris-setosa')
(50, 1.4, 0.2, 5.0, 3.3, 'Iris-setosa')
(51, 4.7, 1.4, 7.0, 3.2, 'Iris-versicolor')
... ...
(145, 5.7, 2.5, 6.7, 3.3, 'Iris-virginica')
... ...
(148, 5.2, 2.0, 6.5, 3.0, 'Iris-virginica')
(149, 5.4, 2.3, 6.2, 3.4, 'Iris-virginica')
(150, 5.1, 1.8, 5.9, 3.0, 'Iris-virginica')
测试表明 JDBC 上的 Python 可以正常运行。 以下只是常规 ML 管道的一些常规数据分析和预处理，由于我们可能会在后续的演示和比较中反复涉及，因此为方便起见在这里附上。
3.5 将 SQL 结果转换为 Pandas DataFrame，再转换为 NumPy 数组
如果还没有安装 Pandas 和 NumPy 软件包，可以通过 Conda 安装，类似于上面 3.3 节。
然后运行以下示例：
### transform SQL results "sqlData"to Pandas dataframe "df", then further to NumPy array "arrayN" for further ML pipelines
import pandas as pd
sqlData = "SELECT * from DataMining.IrisDataset"
df= pd.io.sql.read_sql(sqlData, conn)
df = df.drop('ID', 1)
df = df[['SepalLength', 'SepalWidth', 'PetalLength', 'PetalWidth', 'Species']]
# set the labels to 0, 1, 2, for NumPy matrix
df.replace('Iris-setosa', 0, inplace=True)
df.replace('Iris-versicolor', 1, inplace=True)
df.replace('Iris-virginica', 2, inplace=True)
# turn dataframe into Numpy array
arrayN = df.to_numpy()
### 6. CLose and clean - if connection is not needed anymore?
#curs.close()
#conn.close()
我们例行查看一下当前数据：
df.head(5)
df.describe()
现在，我们得到了一个 DataFrame，以及一个来自源数据表的标准化 NumPy 数组。
当然，我们在这里可以尝试各种常规分析，一个 ML 人员会按照下述步骤开始，在 Python 中替换 R（链接）。
数据源引自此处
3.6 拆分数据并通过 SQL 写回 IRIS 数据库：
当然，我们可以像往常一样将数据拆分为训练集和验证集或测试集，然后将它们写回临时数据库表，实现 IRIS 一些即将推出的精彩 ML 功能：
import numpy as np
from matplotlib import pyplot
from sklearn.model_selection import train_test_split
# keep e.g. 20% = 30 rows as test data; trained on another e.g. 80% = 120 rows
X = arrayN[:,0:4]
y = arrayN[:,4]
X_train, X_validation, Y_train, Y_validation = train_test_split(X, y, test_size=0.20, random_state=1, shuffle=True)
# make 80% of random rows into a Train set
labels1 = np.reshape(Y_train,(120,1))
train = np.concatenate([X_train, labels1],axis=-1)
# make 20% of left rows into Test set
lTest1 = np.reshape(Y_validation,(30,1))
test = np.concatenate([X_validation, lTest1],axis=-1)
# write the train data set into a Pandas frame
dfTrain = pd.DataFrame({'SepalLength':train[:, 0], 'SepalWidth':train[:, 1], 'PetalLength':train[:, 2], 'PetalWidth':train[:, 3], 'Species':train[:, 4]})
dfTrain['Species'].replace(0, 'Iris-setosa', inplace=True)
dfTrain['Species'].replace(1, 'Iris-versicolor', inplace=True)
dfTrain['Species'].replace(2, 'Iris-virginica', inplace=True)
# write the test data into another Pandas frame
dfTest = pd.DataFrame({'SepalLength':test[:, 0], 'SepalWidth':test[:, 1], 'PetalLength':test[:, 2], 'PetalWidth':test[:, 3], 'Species':test[:, 4]})
dfTest['Species'].replace(0, 'Iris-setosa', inplace=True)
dfTest['Species'].replace(1, 'Iris-versicolor', inplace=True)
dfTest['Species'].replace(2, 'Iris-virginica', inplace=True)
### 3. specify temp table names
#dataTable = 'DataMining.IrisDataset'
dtTrain = 'TRAIN02'
dtTest = "TEST02"
### 4. Create 2 temporary tables - you can try drop tables then re-create them every time
curs.execute("Create Table %s (%s DOUBLE, %s DOUBLE, %s DOUBLE, %s DOUBLE, %s VARCHAR(100))" % (dtTrain, dfTrain.columns[0], dfTrain.columns[1], dfTrain.columns[2], dfTrain.columns[3], dfTrain.columns[4]))
curs.execute("Create Table %s (%s DOUBLE, %s DOUBLE, %s DOUBLE, %s DOUBLE, %s VARCHAR(100))" % (dtTest, dfTest.columns[0], dfTest.columns[1], dfTest.columns[2], dfTest.columns[3], dfTest.columns[4]))
### 5. write Train set and Test set into the tales. You can try to delete old record then insert everytime.
curs.fast_executemany = True
curs.executemany( "INSERT INTO %s (SepalLength, SepalWidth, PetalLength, PetalWidth, Species) VALUES (?, ?, ?, ? ,?)" % dtTrain,
list(dfTrain.itertuples(index=False, name=None)) )
curs.executemany( "INSERT INTO %s (SepalLength, SepalWidth, PetalLength, PetalWidth, Species) VALUES (?, ?, ?, ? ,?)" % dtTest,
list(dfTest.itertuples(index=False, name=None)) )
### 6. CLose and clean - if connection is not needed anymore?
#curs.close()
#conn.close()
现在，如果切换到 IRIS 管理控制台或终端 SQL 控制台，应该看到已创建 2 个临时表：120 行的 TRAIN02 和 30 行的 TEST02。
那么本篇快速笔记到这里就结束了。
4. 注意事项
5. 未来计划
我们将使用 IRIS 的 PyODBC、PySPark 和 Python Native API 替换第 3.3 和 3.4 节。除非有人愿意帮忙编写一篇快速笔记，我也将对此不胜感激。
.png)
.png)
.png)
.png)
.png)
.png)
.png)
.png)
.png)
.png)