"SQL" (External Evaluation System)
Listing of Supported Databases »Details
- To configure a database for use with ExternalEvaluate, see details for each specific database in the listing of supported databases.
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", "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
- SQL data types are mapped to appropriate Wolfram Language expressions.
- Dates and times are typically converted to DateObject and TimeObject.
- Binary data is converted to ByteArray.
- A full list of supported data types is available for each supported database: SQLite, PostgreSQL, MySQL, MicrosoftSQL and Oracle.
Examples
open allclose allBasic 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:
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:
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:
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:
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)
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)
"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: