"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 allclose all

Basic Examples  (3)

Specify a reference to the demo database:

Evaluate a query in SQL and return the result:

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

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:

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

Use a URL wrapper to directly run code hosted online:

Scope  (20)

Define a database connection and open a connection:

Evaluate a query that is returning all tables:

Query a certain table using a limit:

Rename columns using an AS statement:

Close the session:

By default, ExternalEvaluate returns data using Dataset:

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

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

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

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

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:

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

Set two variables:

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

Manually provide arguments to the template by using an Association:

Use named arguments:

Session Options  (8)

"ReturnType"  (3)

For SQL, the default return type is "Dataset":

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

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

You can use connectionreturntype as a shorthand to specify the "ReturnType":

"Evaluator"  (1)

Evaluate SQL queries using a specified "Evaluator":

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

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

This is equivalent to:

"SessionProlog"  (1)

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

"SessionEpilog"  (1)

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

"Prolog"  (1)

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

"Epilog"  (1)

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

Command Options  (8)

"Command"  (4)

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

This is equivalent to writing the command using this form:

Use a File wrapper to run the code in file:

In most cases, you can omit the Association:

Use the URL wrapper to directly run code hosted online:

In most cases, you can omit the Association:

Put code in a CloudObject:

Evaluate directly from the cloud:

In most cases, you can omit the Association:

"ReturnType"  (1)

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

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

Use "Tabular" to return a Tabular object.

"TemplateArguments"  (3)

When running a command, you can inline a TemplateExpression:

You can explicitly fill TemplateSlot using "TemplateArguments":

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

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

Applications  (2)

DatabaseReference can also represent an in-memory SQLite database:

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

Operations that are doing side effects on the database typically return Null:

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

Possible Issues  (1)

Usually a database returns dates using the server time zone:

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

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