WOLFRAM

Wolfram 语言与系统参考资料中心

"SQL" (外部计算系统)

支持的数据库列表 »

更多信息

ExternalEvaluate 用法

  • ExternalEvaluate["SQL",code] 在数据库连接中执行 SQL 字符串并以 Wolfram 语言表达式的形式返回结果.
  • ExternalEvaluate["SQL"returntype,code] 执行 SQL 字符串并以指定 returntype 的形式返回结果. returntype 的可能规格包括 "Dataset"、"Tabular"、"Rows"、"NamedRows"、"Columns" 和 "NamedColumns".
  • ExternalEvaluate[DatabaseReference[ref],code] 等价于 ExternalEvaluate[{"SQL","Evaluator"DatabaseReference[ref]},code].
  • ExternalEvaluate[{"SQL","Evaluator"target},…] 中 target 的可能设置包括：
  • "path"or File["path"]访问 SQLite 数据库的路径
    URL["url"]以形式 "backend://user:password@host:port/name" 指定的连接
    DatabaseReference[…]SQL 数据库连接
    SQLConnection[…]SQL-JDBC 数据库连接

数据类型

使用说明

  • 字符串模板 (<*…*>) 可用于计算并将 Wolfram 语言表达式插入 SQL 代码字符串.

范例

打开所有单元 关闭所有单元

基本范例  (3)

为演示数据库指定一个参考信息：

Wolfram Language code: db = DatabaseReference[FindFile["ExampleData/ecommerce-database.sqlite"]]

在 SQL 中执行一条查询并返回结果：

Wolfram Language code: ExternalEvaluate[db, "SELECT * FROM offices"]

若想要在外部语言单元格中使用 SQL，则需要注册一个默认数据库：

Wolfram Language code: db = DatabaseReference[FindFile["ExampleData/ecommerce-database.sqlite"]]
Wolfram Language code: RegisterExternalEvaluator["SQL", db]

类型 > 并在下拉菜单中选择 SQL 可得到一个 SQL 代码单元格：

SELECT * FROM employees LIMIT 10

用 File 封装运行文件中的代码：

Wolfram Language code: path = Export[CreateFile[], "select 21 as AGE", "String"]
Wolfram Language code: ExternalEvaluate[DatabaseReference[FindFile["ExampleData/ecommerce-database.sqlite"]], File[path] ]

用 CloudDeploy 部署代码，从 CloudObject 直接运行代码：

Wolfram Language code: cobj = CloudExport["select 21 as AGE", "String"]
Wolfram Language code: ExternalEvaluate[DatabaseReference[FindFile["ExampleData/ecommerce-database.sqlite"]], cobj]

用 URL 封装直接运行线上的代码：

Wolfram Language code: query = URL["https://exampledata.wolfram.com/script.sql"]
Wolfram Language code: ExternalEvaluate[DatabaseReference[FindFile["ExampleData/ecommerce-database.sqlite"]], query ]

范围  (20)

对于一个数据库连接并打开连接：

Wolfram Language code: db = DatabaseReference[FindFile["ExampleData/ecommerce-database.sqlite"]]
Wolfram Language code: session = StartExternalSession[db]

执行返回所有表格的查询：

Wolfram Language code: ExternalEvaluate[session, "SELECT name FROM sqlite_master WHERE name NOT LIKE 'sqlite_%'"]

使用 limit 查询特定表格：

Wolfram Language code: ExternalEvaluate[session, "SELECT * FROM offices limit 4"]

使用 AS 语句对列重命名：

Wolfram Language code: ExternalEvaluate[session, "SELECT officeCode AS id, addressLine1 || ' - ' || addressLine2 AS address FROM offices"]

关闭会话：

Wolfram Language code: DeleteObject[session]

默认情况下， ExternalEvaluate 使用 Dataset 返回数据：

Wolfram Language code: db = DatabaseReference[FindFile["ExampleData/ecommerce-database.sqlite"]]
Wolfram Language code: ExternalEvaluate[db, "SELECT officeCode AS pk, phone FROM offices"]

"ReturnType" 可用于返回不同形式的数据：

Wolfram Language code: ExternalEvaluate[db -> "Rows", "SELECT officeCode AS pk, phone FROM offices"]
Wolfram Language code: ExternalEvaluate[db -> "NamedRows", "SELECT officeCode AS pk, phone FROM offices"]
Wolfram Language code: ExternalEvaluate[db -> "Columns", "SELECT officeCode AS pk, phone FROM offices"]
Wolfram Language code: ExternalEvaluate[db -> "NamedColumns", "SELECT officeCode AS pk, phone FROM offices"]
Wolfram Language code: ExternalEvaluate[db -> "Tabular", "SELECT officeCode AS pk, phone FROM offices"]

使用 "Columns" 时，如果可能的话数字数据会以打包数组的形式呈现：

Wolfram Language code: data = ExternalEvaluate[db -> "Columns", "SELECT employeeNumber AS id FROM employees limit 5"]
Wolfram Language code: Developer`PackedArrayQ[First[data]]

开启本地 PostgreSQL 数据库的会话（要执行这个输入，你需要有一个正常运行的 PostgreSQL 数据库实例）：

Wolfram Language code: db = DatabaseReference[URL["postgres://localhost/template1"]]

在支持的情况下，ExternalEvaluate 会返回 Wolfram 语言表达式而非字符串：

Wolfram Language code: ExternalEvaluate[db, "SELECT NOW() AS now, NOW()::date AS date, interval '1 day' AS interval, '1.23'::decimal AS decimal"]

会根据当前使用的数据库后端自动对参数进行规范化. 如果后端支持，可使用如 Integer、ByteArray、String、DateObject 和 TimeObject 这样的表达式：

Wolfram Language code: ExternalEvaluate[DatabaseReference["postgresql://localhost/template1"], <| "Command" -> "SELECT `` AS kernel_now, NOW() AS db_now", "TemplateArguments" -> {Now} |>]

字符串模板可用于将 Wolfram 语言表达式插入 SQL 代码：

Wolfram Language code: db = DatabaseReference[FindFile["ExampleData/ecommerce-database.sqlite"]];

设置两个变量：

Wolfram Language code: x = 3; y = 2;

表达式 x^2+y^2 在 Wolfram 语言中计算，得到的结果在被转换后插入到 SQL 代码字符串中：

Wolfram Language code: ExternalEvaluate[db, "SELECT <* x^2 + y^2 *> AS value"]

使用 Association 可手动向模板提供参数：

Wolfram Language code: ExternalEvaluate[db, <| "Command" -> "SELECT city, addressLine1, country FROM offices WHERE country = `1`", "TemplateArguments" -> {"USA"} |>]

使用有名称的参数：

Wolfram Language code: ExternalEvaluate[db, <| "Command" -> "SELECT officeCode, city, addressLine1 FROM offices WHERE officeCode < `n`", "TemplateArguments" -> <|"n" -> 4|> |>]

会话的选项  (8)

"ReturnType"  (3)

对于 SQL，默认的返回类型为 "Dataset"：

Wolfram Language code: db = DatabaseReference[FindFile["ExampleData/ecommerce-database.sqlite"]]
Wolfram Language code: ExternalEvaluate[db, "SELECT 2"] === ExternalEvaluate[{db, "ReturnType" -> "Dataset"}, "SELECT 2"]
Wolfram Language code: ExternalEvaluate[db, "select officeCode as pk, phone from offices"]

"ReturnType" 可用于以不同形式返回数据：

Wolfram Language code: ExternalEvaluate[{db, "ReturnType" -> "Rows"}, "select officeCode as pk, phone from offices"]
Wolfram Language code: ExternalEvaluate[{db, "ReturnType" -> "NamedRows"}, "select officeCode as pk, phone from offices"]
Wolfram Language code: ExternalEvaluate[{db, "ReturnType" -> "Columns"}, "select officeCode as pk, phone from offices"]
Wolfram Language code: ExternalEvaluate[{db, "ReturnType" -> "NamedColumns"}, "select officeCode as pk, phone from offices"]

无论是何种 "ReturnType"，整数、实数和字符串等基本类型都会自动转换：

Wolfram Language code: ExternalEvaluate[DatabaseReference[FindFile["ExampleData/ecommerce-database.sqlite"]], "SELECT 'John' as name, 22 as age, 78.3 as weight" ]

可用 connectionreturntype 作为指定 "ReturnType" 的便捷方法：

Wolfram Language code: db = DatabaseReference[FindFile["ExampleData/ecommerce-database.sqlite"]];
Wolfram Language code: ExternalEvaluate[db -> "NamedColumns", "select officeCode as pk, phone from offices"]

"Evaluator"  (1)

用指定的 "Evaluator" 运行 SQL 查询：

Wolfram Language code: ExternalEvaluate[{"SQL", "Evaluator" -> FindFile["ExampleData/ecommerce-database.sqlite"]}, "select 2+2 as result" ]

使用字符串或 File 封装时，将打开一个 SQLite 连接：

Wolfram Language code: ExternalEvaluate[{"SQL", "Evaluator" -> File[FindFile["ExampleData/ecommerce-database.sqlite"]]}, "select 2+2 as result" ]

也可以用 URL 封装指定 "Evaluator"：

Wolfram Language code: ExternalEvaluate[{"SQL", "Evaluator" -> URL["postgres://postgres:postgres@localhost/template1"]}, "SELECT NOW() as now, NOW()::date as date, interval '1 day' as interval, '1.23'::decimal as decimal" ]

相当于：

Wolfram Language code: ExternalEvaluate[{"SQL", "Evaluator" -> DatabaseReference[<|"Backend" -> "postgres", "Name" -> "template1", "Port" -> None, "Host" -> "localhost", "Username" -> "postgres", "Password" -> "postgres"|>]}, "SELECT NOW() as now, NOW()::date as date, interval '1 day' as interval, '1.23'::decimal as decimal" ]

"SessionProlog"  (1)

用 "SessionProlog" 在会话开始时执行一些小操作：

Wolfram Language code: ExternalEvaluate[{"SQL", "Evaluator" -> URL["postgres://postgres:postgres@localhost/template1"], "SessionProlog" -> "SET TIME ZONE 'UTC'", "ReturnType" -> "Rows"}, "SELECT NOW()"]

"SessionEpilog"  (1)

用 "SessionEpilog" 在会话结束时执行一些小操作：

Wolfram Language code: makeLoggerCommand[s_String] := <|"Command" -> "INSERT INTO db_access(unixtime, type) VALUES (``, ``)", "TemplateArguments" :> {UnixTime[], s}|>
Wolfram Language code: connection = { "SQL", "Evaluator" -> FindFile["ExampleData/ecommerce-database.sqlite"], "SessionProlog" -> "CREATE TABLE IF NOT EXISTS db_access (id INTEGER PRIMARY KEY AUTOINCREMENT, unixtime INTEGER, type STRING);", "SessionEpilog" -> makeLoggerCommand["SessionEpilog"] };
Wolfram Language code: ExternalEvaluate[connection, "SELECT 'hello' as msg"]
Wolfram Language code: ExternalEvaluate[connection, "SELECT * from db_access where type == 'SessionEpilog'"]

"Prolog"  (1)

用 "Prolog" 在每次计算开始前执行一些小操作：

Wolfram Language code: makeLoggerCommand[s_String] := <|"Command" -> "INSERT INTO db_access(unixtime, type) VALUES (``, ``)", "TemplateArguments" :> {UnixTime[], s}|>
Wolfram Language code: connection = { "SQL", "Evaluator" -> FindFile["ExampleData/ecommerce-database.sqlite"], "SessionProlog" -> "CREATE TABLE IF NOT EXISTS db_access (id INTEGER PRIMARY KEY AUTOINCREMENT, unixtime INTEGER, type STRING);", "Prolog" -> makeLoggerCommand["Prolog"] };
Wolfram Language code: ExternalEvaluate[connection, "SELECT * from db_access where type == 'Prolog'"]

"Epilog"  (1)

用 "Epilog" 在每次计算结束后执行一些小操作：

Wolfram Language code: makeLoggerCommand[s_String] := <|"Command" -> "INSERT INTO db_access(unixtime, type) VALUES (``, ``)", "TemplateArguments" :> {UnixTime[], s}|>
Wolfram Language code: connection = { "SQL", "Evaluator" -> FindFile["ExampleData/ecommerce-database.sqlite"], "SessionProlog" -> "CREATE TABLE IF NOT EXISTS db_access (id INTEGER PRIMARY KEY AUTOINCREMENT, unixtime INTEGER, type STRING);", "Epilog" -> makeLoggerCommand["Epilog"] };
Wolfram Language code: ExternalEvaluate[connection, "select 'hello' as msg"]
Wolfram Language code: ExternalEvaluate[connection, "SELECT * from db_access where type == 'Epilog'"]

指令的选项  (8)

"Command"  (4)

只提供字符串时，直接执行查询：

Wolfram Language code: ExternalEvaluate["SQL", "SELECT 21 as age"]

这相当于用以下形式编写命令：

Wolfram Language code: ExternalEvaluate["SQL", <|"Command" -> "SELECT 21 as age"|>]

用 File 封装运行文件中的代码：

Wolfram Language code: path = Export[CreateFile[], "select 21 as age", "Text"]
Wolfram Language code: ExternalEvaluate["SQL", <|"Command" -> File[path]|>]

大多数情况下可以省去 Association：

Wolfram Language code: ExternalEvaluate["SQL", File[path]]

用 URL 封装直接运行线上的代码：

Wolfram Language code: query = URL["https://exampledata.wolfram.com/script.sql"]
Wolfram Language code: ExternalEvaluate["SQL", <|"Command" -> query|>]

大多数情况下可以省去 Association：

Wolfram Language code: ExternalEvaluate["SQL", query]

将代码放入 CloudObject：

Wolfram Language code: cloudObj = CloudExport["select 21 as age", "Text", CloudObject["hello-world-sql"]]

从云端直接计算：

Wolfram Language code: ExternalEvaluate["SQL", <|"Command" -> cloudObj|>]

大多数情况下可以省去 Association：

Wolfram Language code: ExternalEvaluate["SQL", cloudObj]

"ReturnType"  (1)

默认情况下，使用会话创建期间指定的 "ReturnType" 执行该指令：

Wolfram Language code: ExternalEvaluate[ {"SQL", "ReturnType" -> "Rows"}, "select 21 as age, 'john' as name" ]

在指令中指定 "ReturnType" 将覆盖会话的 "ReturnType"：

Wolfram Language code: ExternalEvaluate[ {"SQL", "ReturnType" -> "Rows"}, { <|"Command" -> "select 21 as age, 'john' as name"|>, <|"Command" -> "select 21 as age, 'john' as name", "ReturnType" -> "NamedColumns"|> } ]

用 "Tabular" 返回 Tabular 对象：

Wolfram Language code: ExternalEvaluate[ {"SQL", "ReturnType" -> "Tabular"}, "select 21 as age, 'john' as name" ]

"TemplateArguments"  (3)

运行指令时，可内嵌 TemplateExpression：

Wolfram Language code: x = RandomReal[] ExternalEvaluate["SQL", "SELECT <* x *> as real"]

可用 "TemplateArguments" 填充 TemplateSlot：

Wolfram Language code: ExternalEvaluate[ "SQL", <|"Command" -> "SELECT `1` as real, `2` as integer", "TemplateArguments" -> {RandomReal[], RandomInteger[]}|> ]

如果提供的是非列表参数，将单个模板参数传递给模板：

Wolfram Language code: ExternalEvaluate[ "SQL", <|"Command" -> "SELECT `` as real", "TemplateArguments" -> RandomReal[]|> ]

可以命名模板插槽并使用 Association 将命名参数传递给模板：

Wolfram Language code: ExternalEvaluate[ "SQL", <|"Command" -> "SELECT `r` as real, `int` as integer", "TemplateArguments" -> <|"r" -> RandomReal[], "int" -> RandomInteger[]|>|> ]

应用  (2)

DatabaseReference 可以表示一个内存 SQLite 数据库：

Wolfram Language code: ExternalEvaluate[ DatabaseReference[<|"Backend" -> "sqlite"|>], "SELECT time('now') as now, sqlite_version() AS version" ]

所有在内存会话期间进行的操作会在 ExternalEvaluate 调用的最后丢失：

Wolfram Language code: ExternalEvaluate[ DatabaseReference[<|"Backend" -> "sqlite"|>], { "CREATE TABLE t(x INTEGER PRIMARY KEY ASC, y, z);", "INSERT INTO t(x, y, z) VALUES (1, 2, 3)", "INSERT INTO t(x, y, z) VALUES (4, 5, 6)", "SELECT * FROM t" }]
Wolfram Language code: ExternalEvaluate[ DatabaseReference[<|"Backend" -> "sqlite"|>], "SELECT * FROM t" ]

数据库中有副效应的操作通常返回 Null：

Wolfram Language code: ExternalEvaluate[ DatabaseReference[<|"Backend" -> "sqlite"|>], { "CREATE TEMPORARY TABLE t(x INTEGER PRIMARY KEY ASC, y, z);", "INSERT INTO t(x, y, z) VALUES (1, 2, 3)" }]

对于某些后端，还可以插入数据并指定返回值：

Wolfram Language code: ExternalEvaluate[ DatabaseReference["postgresql://localhost/template1"], { "CREATE TEMPORARY TABLE test_data( id SERIAL PRIMARY KEY, name VARCHAR NOT NULL )", "INSERT INTO test_data(name) VALUES ('john') RETURNING(\"id\") as pk", "SELECT id, name FROM test_data" }]

可能存在的问题  (1)

通常数据库使用服务器时区返回日期：

Wolfram Language code: db = DatabaseReference[URL["postgres://localhost/template1"]];
Wolfram Language code: ExternalEvaluate[db -> "Rows", "SELECT NOW()"]

更改查询以返回位于特定时区的列：

Wolfram Language code: ExternalEvaluate[db -> "Rows", "SELECT NOW() AT TIME ZONE 'UTC'"]

或可使用 "SessionProlog" 设置默认时区：

Wolfram Language code: ExternalEvaluate[<|"Evaluator" -> db, "System" -> "SQL", "SessionProlog" -> "SET TIME ZONE 'UTC'", "ReturnType" -> "Rows"|>, "SELECT NOW()"]