Customize import options when importing data from the results of an SQL query on a MySQL® database using the MySQL native interface. Control the import options by creating an SQLImportOptions
object. Then, customize import options for different columns in the SQL query. Import data using the fetch
function.
This example uses the employees_database.mat
file, which contains the columns first_name
, hire_date
, and DEPARTMENT_NAME
. The example assumes that you are connecting to a MySQL database version 5.7.22 using the MySQL Connector/C++ driver version 8.0.15.
Create a MySQL native interface database connection to a MySQL database with a data source name, user name, and password.
Load employee information into the MATLAB® workspace.
Create the employees
and departments
database tables using the employee information.
Create an SQLImportOptions
object using an SQL query and the databaseImportOptions
function. This query retrieves all information for employees who are sales managers or programmers.
opts =
SQLImportOptions with properties:
ExcludeDuplicates: false
VariableNamingRule: 'preserve'
VariableNames: {'employee_id', 'first_name', 'last_name' ... and 13 more}
VariableTypes: {'double', 'string', 'string' ... and 13 more}
SelectedVariableNames: {'employee_id', 'first_name', 'last_name' ... and 13 more}
FillValues: { NaN, <missing>, <missing> ... and 13 more }
VariableOptions: Show all 16 VariableOptions
Display the current import options for the variables selected in the SelectedVariableNames
property of the SQLImportOptions
object.
varOpts =
1x16 SQLVariableImportOptions array with properties:
Variable Options:
(1) | (2) | (3) | (4) | (5) | (6) | (7) | (8) | (9) | (10) | (11) | (12) | (13) | (14) | (15) | (16)
Name: 'employee_id' | 'first_name' | 'last_name' | 'email' | 'phone_number' | 'hire_date' | 'job_id' | 'salary' | 'commission_pct' | 'manager_id' | 'department_id' | 'temporary' | 'DEPARTMENT_ID' | 'DEPARTMENT_NAME' | 'MANAGER_ID' | 'LOCATION_ID'
Type: 'double' | 'string' | 'string' | 'string' | 'string' | 'datetime' | 'string' | 'double' | 'double' | 'double' | 'double' | 'logical' | 'double' | 'string' | 'double' | 'double'
MissingRule: 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill'
FillValue: NaN | <missing> | <missing> | <missing> | <missing> | NaT | <missing> | NaN | NaN | NaN | NaN | 0 | NaN | <missing> | NaN | NaN
To access sub-properties of each variable, use getoptions
Change the data types for the hire_date
, DEPARTMENT_NAME
, and first_name
variables using the setoptions
function. Then, display the updated import options. For efficiency, change the data type of the hire_date
variable to string
. Because DEPARTMENT_NAME
designates a finite set of repeating values, change the data type of this variable to categorical
. Also, change the name of this variable to lowercase. Because first_name
stores text data, change the data type of this variable to char
.
varOpts =
1x16 SQLVariableImportOptions array with properties:
Variable Options:
(1) | (2) | (3) | (4) | (5) | (6) | (7) | (8) | (9) | (10) | (11) | (12) | (13) | (14) | (15) | (16)
Name: 'employee_id' | 'first_name' | 'last_name' | 'email' | 'phone_number' | 'hire_date' | 'job_id' | 'salary' | 'commission_pct' | 'manager_id' | 'department_id' | 'temporary' | 'DEPARTMENT_ID' | 'department_name' | 'MANAGER_ID' | 'LOCATION_ID'
Type: 'double' | 'char' | 'string' | 'string' | 'string' | 'string' | 'string' | 'double' | 'double' | 'double' | 'double' | 'logical' | 'double' | 'categorical' | 'double' | 'double'
MissingRule: 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill'
FillValue: NaN | '' | <missing> | <missing> | <missing> | <missing> | <missing> | NaN | NaN | NaN | NaN | 0 | NaN | <undefined> | NaN | NaN
To access sub-properties of each variable, use getoptions
Select the three modified variables using the SelectVariableNames
property.
Import and display the results of the SQL query using the fetch
function.
employees_data=10×3 table
first_name hire_date department_name
_____________ ____________________________ _______________
{'Alexander'} "2006-01-03 00:00:00.000000" IT
{'Bruce' } "2007-05-21 00:00:00.000000" IT
{'David' } "2005-06-25 00:00:00.000000" IT
{'Valli' } "2006-02-05 00:00:00.000000" IT
{'Diana' } "2007-02-07 00:00:00.000000" IT
{'John' } "2004-10-01 00:00:00.000000" Sales
{'Karen' } "2005-01-05 00:00:00.000000" Sales
{'Alberto' } "2005-03-10 00:00:00.000000" Sales
{'Gerald' } "2007-10-15 00:00:00.000000" Sales
{'Eleni' } "2008-01-29 00:00:00.000000" Sales
Delete the employees
and departments
database tables using the execute
function.
Close the database connection.