Details

Use DataConnectionObject to represent data located in a MySQL or MariaDB database in Wolfram Language.
Import can be used to retrieve data from a DataConnectionObject .
RelationalDatabase can be used to represent the schema of a "MySQL" database.

Properties

DataConnectionObject [assoc] creates a reference to a data source. The source does not need to exist yet.

General keys typically found in assoc are:

"ConnectionName" "MySQL"

"Location" SQL query given as a string

"Redownload" whether to rerun the query and overwrite cached results

Required keys in assoc are:

"DatabaseName" string giving the database to access

"Host" the host servicing the database

Typical keys in assoc are:

"Port" the server port to access the database

Possible forms of "Location" when connecting to a MySQL database include:

"query" SQL query given as a string

Supported Types

BOOLEAN True or False

BINARY, BLOB, LONGBLOB, MEDIUMBLOB, TINYBLOB, VARBINARY ByteArray

DATE DateObject with day granularity

DATETIME DateObject with instant granularity

TIMESTAMP DateObject with instant granularity and time zone support

DECIMAL, NUMERIC Real with fixed precision

BIGINT, INTEGER, MEDIUMINT, SMALLINT, TINYINT, YEAR Integer

DOUBLE, FLOAT, REAL Real

CHAR, LONGTEXT, MEDIUMTEXT, NCHAR, NVARCHAR, TEXT, TINYTEXT, VARCHAR String

TIME TimeObject

Account Setting & Authentication

Possible values for Authentication are:

Automatic attempt to use saved credentials or give a dialog

"Dialog" give authentication parameters via a dialog

SystemCredential ["key"] retrieve authentication information from secure storage

SystemCredentialData […] stored authentication data

assoc provide explicit credentials

Use the form Authentication :>SystemCredential ["key"] to delay evaluating securely stored credentials.

The following keys are accepted by Authentication assoc:

"Username" username

"Password" password

Examples

Basic Examples (2)

Create a DataConnectionObject to data from a MySQL query:

Wolfram Language code:

DataConnectionObject[<|"ConnectionName" -> "MySQL", "DatabaseName" -> "dbname", "Host" -> "my.host.com", "Location" -> "SELECT * FROM users"|>, Authentication -> <|"Username" -> "reader1", "Password" -> "reader1"|>]

Retrieve the data using Import :

Wolfram Language code:

Import@DataConnectionObject[<|"ConnectionName" -> "MySQL", "DatabaseName" -> "dbname", "Host" -> "my.host.com", "Location" -> "SELECT * FROM table"|>, Authentication -> <|"Username" -> "user", "Password" -> "pass"|>]