"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","Target"DatabaseReference[ref]},code].
- The possible settings for target in ExternalEvaluate[{"SQL","Target"target},…] 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 (5)
Define a database connection and open a connection:
Evaluate a query that is returning all tables:
Query a certain table using 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 backend 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:
Command Options (1)
"Command" (1)
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 backends, it is possible to insert data and specify a return value: