"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", "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:
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.
Usage Notes
- String templates (<*…*>) can be used to evaluate and insert Wolfram Language expressions into the SQL code string.
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:
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)
"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:
See Also
ExternalEvaluate StartExternalSession DatabaseReference
Database Connections: SQLite MicrosoftSQL MySQL Oracle PostgreSQL
External Evaluation Systems: SQL-JDBC