WOLFRAM

Wolfram Language & System Documentation Center

"SQL" (External Evaluation System)

Listing of Supported Databases »

Details

ExternalEvaluate Usage

  • ExternalEvaluate["SQL",code] executes a string of SQL in a database connection and returns the result as a Wolfram Language expression.
  • ExternalEvaluate["SQL"returntype,code] executes the SQL string and returns the result in the specified returntype. Possible specifications for returntype are "Dataset", "Tabular", "Rows", "NamedRows", "Columns" and "NamedColumns".
  • ExternalEvaluate[DatabaseReference[ref],code] is equivalent to ExternalEvaluate[{"SQL","Evaluator"DatabaseReference[ref]},code].
  • The possible settings for evaluator in ExternalEvaluate[{"SQL","Evaluator"evaluator},…] include:
  • "path"or File["path"]path to an SQLite database
    URL["url"] a connection specified in the form "backend://user:password@host:port/name"
    DatabaseReference[…]an SQL database connection
    SQLConnection[…]an SQL-JDBC database connection

Data Types

Usage Notes

  • String templates (<*…*>) can be used to evaluate and insert Wolfram Language expressions into the SQL code string.

Examples

open all close all

Basic Examples  (3)

Specify a reference to the demo database:

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

Evaluate a query in SQL and return the result:

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

To use SQL in an external language cell, you need to register a default database:

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

Type > and select SQL from the drop-down menu to get an SQL code cell:

SELECT * FROM employees LIMIT 10

Use the File wrapper to execute code contained in a file:

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

Deploy code using CloudDeploy, and then run the code directly from a CloudObject:

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

Use a URL wrapper to directly run code hosted online:

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

Scope  (20)

Define a database connection and open a connection:

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

Evaluate a query that is returning all tables:

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

Query a certain table using a limit:

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

Rename columns using an AS statement:

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

Close the session:

Wolfram Language code: DeleteObject[session]

By default, ExternalEvaluate returns data using Dataset:

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

"ReturnType" can be used to return data in a different form:

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"]

While using "Columns", numerical data is returned as a packed array when possible:

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

Start a session to a local PostgreSQL database (to evaluate this input, you would need to have an appropriate PostgreSQL database instance running):

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

When supported, ExternalEvaluate will return Wolfram Language expressions instead of strings:

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

Parameters are automatically normalized according to the database backend that is currently in use. Expressions like Integer, ByteArray, String, DateObject and TimeObject can be used if the back end supports them:

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

String templates can be used to insert Wolfram Language expressions into SQL code:

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

Set two variables:

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

The expression x^2+y^2 is evaluated in the Wolfram Language, and the result is converted and inserted into the SQL code string:

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

Manually provide arguments to the template by using an Association:

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

Use named arguments:

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

Session Options  (8)

"ReturnType"  (3)

For SQL, the default return type is "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" can be used to return data in a different form:

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"]

Regardless of the "ReturnType", basic types such as integers, reals and strings are automatically converted:

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

You can use connectionreturntype as a shorthand to specify the "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)

Evaluate SQL queries using a specified "Evaluator":

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

When using a string or a File wrapper, an SQLite connection will be opened:

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

"Evaluator" can also be specified by using a URL wrapper:

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" ]

This is equivalent to:

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)

Use "SessionProlog" to perform a side effect at the start of a session:

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

"SessionEpilog"  (1)

Use "SessionEpilog" to perform a side effect at the end of a session.

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)

Use "Prolog" to perform a side effect before every evaluation:

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)

Use "Epilog" to perform a side effect after every evaluation:

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'"]

Command Options  (8)

"Command"  (4)

When only a string is provided, the query is directly executed:

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

This is equivalent to writing the command using this form:

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

Use a File wrapper to run the code in file:

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

In most cases, you can omit the Association:

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

Use the URL wrapper to directly run code hosted online:

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

In most cases, you can omit the Association:

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

Put code in a CloudObject:

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

Evaluate directly from the cloud:

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

In most cases, you can omit the Association:

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

"ReturnType"  (1)

By default, the command is executed using the "ReturnType" specified during the session creation:

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

Specifying a "ReturnType" in the command overrides the "ReturnType" for the session:

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"|> } ]

Use "Tabular" to return a Tabular object.

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

"TemplateArguments"  (3)

When running a command, you can inline a TemplateExpression:

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

You can explicitly fill TemplateSlot using "TemplateArguments":

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

When a non-list argument is provided, a single template argument is passed to the template:

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

You can name template slots and use an Association to pass named arguments to the template:

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

Applications  (2)

DatabaseReference can also represent an in-memory SQLite database:

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

All operations done during an in-memory session are lost at the end of an ExternalEvaluate call:

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" ]

Operations that are doing side effects on the database typically return 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)" }]

For some back ends, it is possible to insert data and specify a return value:

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" }]

Possible Issues  (1)

Usually a database returns dates using the server time zone:

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

Change the query to return a column at a particular time zone:

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

Or "SessionProlog" can be used to set a default time zone:

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