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函数式编程的特性，非常方便的导入导出大量数据、数据处理和数据可视化等。