databaselink的使用詳解

一、databaselinks

databaselinks 是Mathematica中的一個連接資料庫的工具。在Mathematica中用於訪問關係型資料庫，它可以連接許多不同類型的資料庫，如ORACLE，MySQL，CSV等。可以利用 databaselinks 在Mathematica中讀取、操作、寫入資料庫中的信息。

連接資料庫，使用函數 OpenSQLConnection。

conn = OpenSQLConnection[JDBC["MySQL(Simple)", "localhost/mydatabase"], "Username" -> "root", "Password" -> "root"]

其中，JDBC[“MySQL(Simple)”, “localhost/mydatabase”] 表示要連接MySQL資料庫，”localhost/mydatabase” 是資料庫地址與名稱。

“Username” -> “root”, “Password” -> “root” 是訪問資料庫需要身份驗證的用戶名和密碼。在 OpenSQLConnection 中可以設置更多的選項，具體可以使用 documentation 查看。

二、databaselink的創建

使用databaselink前，需要先載入它的包，使用以下命令：

Needs["DatabaseLink`"]

CreateSQLTable函數可以創建一張資料庫表。

CreateSQLTable[conn, 
  SQLTable["table_test", {SQLColumn["id", "DataTypeName" -> "INT", "PrimaryKey" -> True], 
   SQLColumn["name", "DataTypeName" ->"VARCHAR(45)"], 
   SQLColumn["age", "DataTypeName" -> "INT"], 
   SQLColumn["gender", "DataTypeName" -> "VARCHAR(6)"]}], 
 "Columns" -> {"id", "name", "age", "gender"}, "Indexes" -> None
]

其中，conn是之前打開的連接，SQLTable[“table_test”,{…}]是你要新建的表名與表結構。在表結構中，每一個 SQLColumn 表示表中的一項內容，”DataTypeName” -> “INT” 表示該項的數據類型是 INT，”PrimaryKey” -> True 表示該項是該表的主鍵。

“Columns” -> {“id”, “name”, “age”, “gender”} 表示這個表的所有列名，”Indexes” -> None 表示不創建索引。

三、databaselink查詢

使用 SQLSelect 函數查詢數據：

SQLSelect[conn, "table_test"]

將會顯示這個表的所有記錄，可以指定查詢的列名或查詢條件。

使用 SQLExecute 函數執行 SQL 語句：

SQLExecute[conn, "INSERT INTO table_test (id,name,age,gender) VALUES (1,'Tom',18,'Male')"]
SQLExecute[conn, "SELECT * FROM table_test WHERE age<20"]
SQLExecute[conn, "UPDATE table_test SET age=20 WHERE name='Tom'"]
SQLExecute[conn, "DELETE FROM table_test WHERE name='Tom'"]

四、databaselink及匿名選取

在SQL語句中，使用 ? 代表需要傳入參數，使用SQLExecute函數的第二個參數將傳入的參數傳入。

SQLExecute[conn, "SELECT * FROM table_test WHERE age<?", {20}]

匿名選取可以進一步防止SQL注入攻擊。

databaselink可以配合各種演算法、數據處理任務使用。比如以下代碼是使用 Amazon SageMaker的 XGBoost 演算法訓練數據集、預測結果，並將其保存到 MySQL 資料庫中：

Needs["DatabaseLink`"]
Needs["AWSClient`"]

bucket = "my-bucket"
trainingFile = "train.csv"
endpoint = "xgboost-2019-06-04-10-03-57-666"
modelArn = "slim-xgboost-model/1"
databaseConn = "mysql://user:password@ip:3306/test"

s3 = AWSS3Client[]
s3["PutObject", {"Bucket" -> bucket, "Key" -> trainingFile, "Body" -> Import["train.csv"]}]
trainingDataUri = s3["GetObject", {"Bucket" -> bucket, "Key" -> trainingFile}]["Body"]

ep = AWSMLClient[endpointsUrl -> "https://runtime.sagemaker.us-east-1.amazonaws.com", authKey -> AWSAuthV4[method -> "POST"]]
endpointResult = ep[endpoint,  "application/json"|>, trainingDataUri, "xgboost:1"]

resultJson = ImportString[endpointResult, "JSON"]
result = Map[First, resultJson["predictions"]]

(* save the result to MySql *)
conn = OpenSQLConnection[JDBC["MySQL(Simple)", databaseConn]]
CreateSQLTable[conn, SQLTable["xgboost_prediction", {SQLColumn["index", "DataTypeName" -> "INT"], SQLColumn["label", "DataTypeName" -> "DOUBLE"], SQLColumn["score", "DataTypeName" -> "DOUBLE"]}]]
SQLExecute[conn, "TRUNCATE TABLE xgboost_prediction"]
SQLExecute[conn, "INSERT INTO xgboost_prediction (`index`, `label`, `score`) VALUES (?, ?, ?)", #] & /@ Table[{i, result[[i, 1]], result[[i, 2]]}, {i, 1, Length[result]}]

五、總結

databaselink 是連接資料庫的重要工具，它可以輕鬆地與許多資料庫（ORACLE，MySQL，CSV等）進行通信，向資料庫寫入數據並查詢它們。databaselink是數學軟體Mathematica作為連接資料庫的一種方式，大大簡化了程序員在使用Mathematica中數據存儲與使用的流程。databaselink的使用實例涵蓋了資料庫的基本操作，包括連接資料庫、查詢、插入、更新、刪除、匿名選取、創建表等。其作用不僅在於讓程序員用同樣的方式，即動態生成代碼進行資料庫操作，而且通過Mathematica函數式編程的特性，非常方便的導入導出大量數據、數據處理和數據可視化等。