"SQL" (外部評価システム)
-
関連項目
- 関数
- ExternalEvaluate
- StartExternalSession
- DatabaseReference
-
- データベース接続
- SQLite
- MicrosoftSQL
- MySQL
- Oracle
- PostgreSQL
-
- 外部評価システム
- SQL-JDBC
- 関連するガイド
"SQL" (外部評価システム)
サポートされているデータベースのリスト »詳細
- ExternalEvaluateでデータベースが使えるよう設定するためには，サポートされているデータベースのリストに記載されているそれぞれのデータベースの詳細を参照のこと．
ExternalEvaluateの使用法
- ExternalEvaluate["SQL",code] はデータベース接続でSQLの列を実行し，Wolfram言語式として結果を返す．
- ExternalEvaluate["SQL"returntype,code] はSQLの列を実行し，指定された returntype で結果を返す．returntype に指定できるのは"Dataset"，"Rows"，"NamedRows"，"Columns"，"NamedColumns"である．
- ExternalEvaluate[DatabaseReference[ref],code] はExternalEvaluate[{"SQL","Evaluator"DatabaseReference[ref]},code]と同等である．
- ExternalEvaluate[{"SQL","Evaluator"evaluator},…] の evaluator として可能な設定：
-
"path"またはFile["path"] SQLiteデータベースへのパス URL["url"] "backend://user:password@host:port/name"の形式で指定された接続 DatabaseReference[…] SQLデータベース接続 SQLConnection[…] SQL-JDBCデータベース接続
データ型
- SQLのデータ型は適切なWolfram言語式にマップされる．
- 日付と時間は，通常DateObjectとTimeObjectに変換される．
- バイナリデータはByteArrayに変換される．
- サポートされているデータベースSQLite，PostgreSQL，MySQL，MicrosoftSQL，Oracleのそれぞれについてサポートされるデータ型の完全リストが利用できる．
例題
すべて開く すべて閉じる例 (3)
db = DatabaseReference[FindFile["ExampleData/ecommerce-database.sqlite"]]ExternalEvaluate[db, "SELECT * FROM offices"]外部言語のセルでSQLを使うためには，デフォルトのデータベースを登録する必要がある：
db = DatabaseReference[FindFile["ExampleData/ecommerce-database.sqlite"]]RegisterExternalEvaluator["SQL", db]>をタイプしてドロップダウンメニューからSQLを選ぶと，SQLコードのセルが得られる：
Fileラッパーを使ってファイルに含まれるコードを実行する：
path = Export[CreateFile[], "select 21 as AGE", "String"]ExternalEvaluate[DatabaseReference[FindFile["ExampleData/ecommerce-database.sqlite"]], File[path]
]CloudDeployを使ってコードを配備し，CloudObjectから直接コードを実行する：
cobj = CloudExport["select 21 as AGE", "String"]ExternalEvaluate[DatabaseReference[FindFile["ExampleData/ecommerce-database.sqlite"]], cobj]URLラッパーを使ってオンラインでホストされたコードを直接実行する：
query = URL["https://exampledata.wolfram.com/script.sql"]ExternalEvaluate[DatabaseReference[FindFile["ExampleData/ecommerce-database.sqlite"]], query
]スコープ (20)
db = DatabaseReference[FindFile["ExampleData/ecommerce-database.sqlite"]]session = StartExternalSession[db]ExternalEvaluate[session, "SELECT name FROM sqlite_master WHERE name NOT LIKE 'sqlite_%'"]ExternalEvaluate[session, "SELECT * FROM offices limit 4"]ExternalEvaluate[session, "SELECT officeCode AS id, addressLine1 || ' - ' || addressLine2 AS address FROM offices"]DeleteObject[session]デフォルトではExternalEvaluateはDatasetを使ってデータを返す：
db = DatabaseReference[FindFile["ExampleData/ecommerce-database.sqlite"]]ExternalEvaluate[db, "SELECT officeCode AS pk, phone FROM offices"]別の形式でデータを返すためには"ReturnType"を使うことができる：
ExternalEvaluate[db -> "Rows", "SELECT officeCode AS pk, phone FROM offices"]ExternalEvaluate[db -> "NamedRows", "SELECT officeCode AS pk, phone FROM offices"]ExternalEvaluate[db -> "Columns", "SELECT officeCode AS pk, phone FROM offices"]ExternalEvaluate[db -> "NamedColumns", "SELECT officeCode AS pk, phone FROM offices"]ExternalEvaluate[db -> "Tabular", "SELECT officeCode AS pk, phone FROM offices"]"Columns"を使うと，数値データは可能な場合はパックアレーとして返される：
data = ExternalEvaluate[db -> "Columns", "SELECT employeeNumber AS id FROM employees limit 5"]Developer`PackedArrayQ[First[data]]ローカルのPostgreSQLデータベースのセッションを開始する（この入力を評価するためには，適切なPostgreSQLデータベースインスタンスが実行している必要がある）：
db = DatabaseReference[URL["postgres://localhost/template1"]]サポートされる場合は，ExternalEvaluateは文字列の代りにWolfram言語式を返す：
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等の式が使える：
ExternalEvaluate[DatabaseReference["postgresql://localhost/template1"], <|
"Command" -> "SELECT `` AS kernel_now, NOW() AS db_now",
"TemplateArguments" -> {Now}
|>]列のテンプレートを使うと，Wolfram言語式をSQLコードに挿入することができる：
db = DatabaseReference[FindFile["ExampleData/ecommerce-database.sqlite"]];x = 3;
y = 2;式 x^2+y^2はWolfram言語内で評価され，結果は変換されてSQLのコード列に挿入される：
ExternalEvaluate[db, "SELECT <* x^2 + y^2 *> AS value"]Associationを使って，手動でテンプレートに引数を与える：
ExternalEvaluate[db, <|
"Command" -> "SELECT city, addressLine1, country FROM offices WHERE country = `1`",
"TemplateArguments" -> {"USA"}
|>]ExternalEvaluate[db, <|
"Command" -> "SELECT officeCode, city, addressLine1 FROM offices WHERE officeCode < `n`",
"TemplateArguments" -> <|"n" -> 4|>
|>]セッションオプション (8)
"ReturnType" (3)
SQLでは，デフォルトの戻り型は"Dataset"である：
db = DatabaseReference[FindFile["ExampleData/ecommerce-database.sqlite"]]ExternalEvaluate[db, "SELECT 2"] === ExternalEvaluate[{db, "ReturnType" -> "Dataset"}, "SELECT 2"]ExternalEvaluate[db, "select officeCode as pk, phone from offices"]"ReturnType"はデータを異なる形式で戻すために使うことができる：
ExternalEvaluate[{db, "ReturnType" -> "Rows"}, "select officeCode as pk, phone from offices"]ExternalEvaluate[{db, "ReturnType" -> "NamedRows"}, "select officeCode as pk, phone from offices"]ExternalEvaluate[{db, "ReturnType" -> "Columns"}, "select officeCode as pk, phone from offices"]ExternalEvaluate[{db, "ReturnType" -> "NamedColumns"}, "select officeCode as pk, phone from offices"]"ReturnType"にかかわらず，整数，実数，文字列等の基本的な型は自動的に変換される：
ExternalEvaluate[DatabaseReference[FindFile["ExampleData/ecommerce-database.sqlite"]], "SELECT 'John' as name, 22 as age, 78.3 as weight"
]"ReturnType"を指定する際に，省略形として connectionreturntype を使うことができる：
db = DatabaseReference[FindFile["ExampleData/ecommerce-database.sqlite"]];ExternalEvaluate[db -> "NamedColumns", "select officeCode as pk, phone from offices"]"Evaluator" (1)
指定された"Evaluator"を使ってSQLクエリを評価する：
ExternalEvaluate[{"SQL", "Evaluator" -> FindFile["ExampleData/ecommerce-database.sqlite"]}, "select 2+2 as result"
]文字列またはFileラッパーを使うとき，SQLite接続が開く：
ExternalEvaluate[{"SQL", "Evaluator" -> File[FindFile["ExampleData/ecommerce-database.sqlite"]]}, "select 2+2 as result"
]URLラッパーを使うことによって，"Evaluator"を指定することもできる：
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"
]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)
"SessionEpilog" (1)
"SessionEpilog"を使って，セッションの最後に副作用を実行する：
makeLoggerCommand[s_String] := <|"Command" -> "INSERT INTO db_access(unixtime, type) VALUES (``, ``)", "TemplateArguments" :> {UnixTime[], s}|>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"]
};ExternalEvaluate[connection, "SELECT 'hello' as msg"]ExternalEvaluate[connection, "SELECT * from db_access where type == 'SessionEpilog'"]"Prolog" (1)
"Prolog"を使って，それぞれの評価の前に副作用を実行する：
makeLoggerCommand[s_String] := <|"Command" -> "INSERT INTO db_access(unixtime, type) VALUES (``, ``)", "TemplateArguments" :> {UnixTime[], s}|>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"]
};ExternalEvaluate[connection, "SELECT * from db_access where type == 'Prolog'"]"Epilog" (1)
"Epilog"を使ってそれぞれの評価の後に副作用を実行する：
makeLoggerCommand[s_String] := <|"Command" -> "INSERT INTO db_access(unixtime, type) VALUES (``, ``)", "TemplateArguments" :> {UnixTime[], s}|>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"]
};ExternalEvaluate[connection, "select 'hello' as msg"]ExternalEvaluate[connection, "SELECT * from db_access where type == 'Epilog'"]コマンドオプション (8)
"Command" (4)
ExternalEvaluate["SQL", "SELECT 21 as age"]ExternalEvaluate["SQL", <|"Command" -> "SELECT 21 as age"|>]Fileラッパーを使ってファイルのコードを実行する：
path = Export[CreateFile[], "select 21 as age", "Text"]ExternalEvaluate["SQL", <|"Command" -> File[path]|>]ほとんどの場合，Associationは省略できる：
ExternalEvaluate["SQL", File[path]]URLラッパーを使って，オンラインでホストされるコードを直接実行する：
query = URL["https://exampledata.wolfram.com/script.sql"]ExternalEvaluate["SQL", <|"Command" -> query|>]ほとんどの場合，Associationは省略できる：
ExternalEvaluate["SQL", query]CloudObjectにコードを置く：
cloudObj = CloudExport["select 21 as age", "Text", CloudObject["hello-world-sql"]]ExternalEvaluate["SQL", <|"Command" -> cloudObj|>]ほとんどの場合，Associationは省略できる：
ExternalEvaluate["SQL", cloudObj]"ReturnType" (1)
デフォルトでは，コマンドはセッションの生成中に指定される"ReturnType"を使って実行される：
ExternalEvaluate[
{"SQL", "ReturnType" -> "Rows"},
"select 21 as age, 'john' as name"
]コマンドで"ReturnType"を指定すると，セッションの"ReturnType"が上書きされる：
ExternalEvaluate[
{"SQL", "ReturnType" -> "Rows"},
{
<|"Command" -> "select 21 as age, 'john' as name"|>,
<|"Command" -> "select 21 as age, 'john' as name", "ReturnType" -> "NamedColumns"|>
}
]"Tabular"を使うとTabularオブジェクトが戻る：
ExternalEvaluate[
{"SQL", "ReturnType" -> "Tabular"},
"select 21 as age, 'john' as name"
]"TemplateArguments" (3)
コマンドを実行するとき，TemplateExpressionを行内に埋め込むことができる：
x = RandomReal[]
ExternalEvaluate["SQL", "SELECT <* x *> as real"]"TemplateArguments"を使ってTemplateSlotを明示的に埋めることができる：
ExternalEvaluate[
"SQL",
<|"Command" -> "SELECT `1` as real, `2` as integer", "TemplateArguments" -> {RandomReal[], RandomInteger[]}|>
]リスト以外の引数が与えられると，テンプレートには単独のテンプレート引数が渡される：
ExternalEvaluate[
"SQL",
<|"Command" -> "SELECT `` as real", "TemplateArguments" -> RandomReal[]|>
]テンプレートスロットに名前を付け，Associationを使うと，テンプレートに名前付きの引数を渡すことができる：
ExternalEvaluate[
"SQL",
<|"Command" -> "SELECT `r` as real, `int` as integer", "TemplateArguments" -> <|"r" -> RandomReal[], "int" -> RandomInteger[]|>|>
]アプリケーション (2)
DatabaseReferenceはインメモリのSQLiteデータベースを表すこともできる：
ExternalEvaluate[
DatabaseReference[<|"Backend" -> "sqlite"|>], "SELECT time('now') as now, sqlite_version() AS version"
]インメモリセッションで行った操作はすべてExternalEvaluate呼出しの最後に失われる：
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"
}]ExternalEvaluate[
DatabaseReference[<|"Backend" -> "sqlite"|>],
"SELECT * FROM t"
]データベースに二次的作用を及ぼしている操作は通常Nullを返す：
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)"
}]バックエンドによっては，データを挿入し戻り値を指定することができるものもある：
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)
db = DatabaseReference[URL["postgres://localhost/template1"]];ExternalEvaluate[db -> "Rows", "SELECT NOW()"]ExternalEvaluate[db -> "Rows", "SELECT NOW() AT TIME ZONE 'UTC'"]"SessionProlog"を使ってデフォルトの時刻帯を設定することもできる：
ExternalEvaluate[<|"Evaluator" -> db, "System" -> "SQL", "SessionProlog" -> "SET TIME ZONE 'UTC'", "ReturnType" -> "Rows"|>, "SELECT NOW()"]関連項目
ExternalEvaluate StartExternalSession DatabaseReference
データベース接続: SQLite MicrosoftSQL MySQL Oracle PostgreSQL
外部評価システム: SQL-JDBC
関連するガイド
履歴
2020 で導入 (12.2)