Define import options for database data
opts = databaseImportOptions(conn,source)SQLImportOptions
object using the database connection and a source, which is a database table name or SQL
query.
opts = databaseImportOptions(conn,source,Name,Value)'Catalog',"toystore_doc" retrieves data from the
toystore_doc database catalog.
Customize import options when importing data from a database table. Control the import options by creating an SQLImportOptions object. Then, customize import options for different database columns. Import data using the sqlread function.
This example uses the patients.xls file, which contains the columns Gender, Location, SelfAssessedHealthStatus, and Smoker. The example also uses a Microsoft® SQL Server® Version 11.00.2100 database and the Microsoft SQL Server Driver 11.00.5058.
Create a database connection to a Microsoft SQL Server database with Windows® authentication. Specify a blank user name and password.
datasource = 'MS SQL Server Auth'; conn = database(datasource,'','');
Load patient information into the MATLAB® workspace.
patients = readtable('patients.xls');Create the patients database table using the patient information.
tablename = 'patients';
sqlwrite(conn,tablename,patients)Create an SQLImportOptions object using the patients database table and the databaseImportOptions function.
opts = databaseImportOptions(conn,tablename)
opts =
SQLImportOptions with properties:
ExcludeDuplicates: false
VariableNamingRule: 'modify'
VariableNames: {'LastName', 'Gender', 'Age' ... and 7 more}
VariableTypes: {'char', 'char', 'double' ... and 7 more}
SelectedVariableNames: {'LastName', 'Gender', 'Age' ... and 7 more}
FillValues: {'', '', NaN ... and 7 more }
VariableOptions: Show all 10 VariableOptions
Display the current import options for the variables selected in the SelectedVariableNames property of the SQLImportOptions object.
vars = opts.SelectedVariableNames; varOpts = getoptions(opts,vars)
varOpts =
1x10 SQLVariableImportOptions array with properties:
Variable Options:
(1) | (2) | (3) | (4) | (5) | (6) | (7) | (8) | (9) | (10)
Name: 'LastName' | 'Gender' | 'Age' | 'Location' | 'Height' | 'Weight' | 'Smoker' | 'Systolic' | 'Diastolic' | 'SelfAssessedHealthStatus'
Type: 'char' | 'char' | 'double' | 'char' | 'double' | 'double' | 'double' | 'double' | 'double' | 'char'
MissingRule: 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill'
FillValue: '' | '' | NaN | '' | NaN | NaN | NaN | NaN | NaN | ''
To access sub-properties of each variable, use getoptions
Change the data types for the Gender, Location, SelfAssessedHealthStatus, and Smoker variables using the setoptions function. Because the Gender, Location, and SelfAssessedHealthStatus variables indicate a finite set of repeating values, change their data type to categorical. Because the Smoker variable stores the values 0 and 1, change its data type to logical. Then, display the updated import options.
opts = setoptions(opts,{'Gender','Location','SelfAssessedHealthStatus'}, ...
'Type','categorical');
opts = setoptions(opts,'Smoker','Type','logical');
varOpts = getoptions(opts,{'Gender','Location','Smoker', ...
'SelfAssessedHealthStatus'})varOpts =
1x4 SQLVariableImportOptions array with properties:
Variable Options:
(1) | (2) | (3) | (4)
Name: 'Gender' | 'Location' | 'Smoker' | 'SelfAssessedHealthStatus'
Type: 'categorical' | 'categorical' | 'logical' | 'categorical'
MissingRule: 'fill' | 'fill' | 'fill' | 'fill'
FillValue: <undefined> | <undefined> | 0 | <undefined>
To access sub-properties of each variable, use getoptions
Import the patients database table using the sqlread function, and display the last eight rows of the table.
data = sqlread(conn,tablename,opts); tail(data)
ans=8×10 table
LastName Gender Age Location Height Weight Smoker Systolic Diastolic SelfAssessedHealthStatus
_____________ ______ ___ _________________________ ______ ______ ______ ________ _________ ________________________
{'Foster' } Female 30 St. Mary's Medical Center 70 124 false 130 91 Fair
{'Gonzales' } Male 48 County General Hospital 71 174 false 123 79 Good
{'Bryant' } Female 48 County General Hospital 66 134 false 129 73 Excellent
{'Alexander'} Male 25 County General Hospital 69 171 true 128 99 Good
{'Russell' } Male 44 VA Hospital 69 188 true 124 92 Good
{'Griffin' } Male 49 County General Hospital 70 186 false 119 74 Fair
{'Diaz' } Male 45 County General Hospital 68 172 true 136 93 Good
{'Hayes' } Male 48 County General Hospital 66 177 false 114 86 Fair
Display a summary of the imported data. The sqlread function applies the import options to the variables in the imported data.
summary(data)
Variables:
LastName: 100×1 cell array of character vectors
Gender: 100×1 categorical
Values:
Female 53
Male 47
Age: 100×1 double
Values:
Min 25
Median 39
Max 50
Location: 100×1 categorical
Values:
County General Hospital 39
St. Mary s Medical Center 24
VA Hospital 37
Height: 100×1 double
Values:
Min 60
Median 67
Max 72
Weight: 100×1 double
Values:
Min 111
Median 142.5
Max 202
Smoker: 100×1 logical
Values:
True 34
False 66
Systolic: 100×1 double
Values:
Min 109
Median 122
Max 138
Diastolic: 100×1 double
Values:
Min 68
Median 81.5
Max 99
SelfAssessedHealthStatus: 100×1 categorical
Values:
Excellent 34
Fair 15
Good 40
Poor 11
Delete the patients database table using the execute function.
sqlquery = ['DROP TABLE ' tablename];
execute(conn,sqlquery)Close the database connection.
close(conn)
Customize import options when importing data from the results of an SQL query on a database. 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 also uses a Microsoft® SQL Server® Version 11.00.2100 database and the Microsoft SQL Server Driver 11.00.5058.
Create an ODBC database connection to a Microsoft SQL Server database with Windows® authentication. Specify a blank user name and password.
datasource = 'MS SQL Server Auth'; conn = database(datasource,'','');
Load employee information into the MATLAB® workspace.
employeedata = load('employees_database.mat');Create the employees and departments database tables using the employee information.
emps = employeedata.employees; depts = employeedata.departments; sqlwrite(conn,'employees',emps) sqlwrite(conn,'departments',depts)
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.
sqlquery = ['SELECT * from employees e join departments d ' ... 'on (e.department_id = d.department_id) where job_id ' ... 'in (''IT_PROG'',''SA_MAN'')']; opts = databaseImportOptions(conn,sqlquery)
opts =
SQLImportOptions with properties:
ExcludeDuplicates: false
VariableNamingRule: 'modify'
VariableNames: {'employee_id', 'first_name', 'last_name' ... and 13 more}
VariableTypes: {'double', 'char', 'char' ... and 13 more}
SelectedVariableNames: {'employee_id', 'first_name', 'last_name' ... and 13 more}
FillValues: { NaN, '', '' ... 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.
vars = opts.SelectedVariableNames; varOpts = getoptions(opts,vars)
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' | 'char' | 'char' | 'char' | 'char' | 'char' | 'double' | 'double' | 'double' | 'double' | 'double' | 'double' | 'char' | 'double' | 'double'
MissingRule: 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill'
FillValue: NaN | '' | '' | '' | '' | '' | '' | NaN | NaN | NaN | NaN | NaN | NaN | '' | 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. Because hire_date stores date and time data, change the data type of this variable to datetime. 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 string.
opts = setoptions(opts,'hire_date','Type','datetime'); opts = setoptions(opts,'DEPARTMENT_NAME','Name','department_name', ... 'Type','categorical'); opts = setoptions(opts,'first_name','Type','string'); vars = opts.SelectedVariableNames; varOpts = getoptions(opts,vars)
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' | 'char' | 'char' | 'char' | 'datetime' | 'char' | 'double' | 'double' | 'double' | 'double' | 'double' | 'double' | 'categorical' | 'double' | 'double'
MissingRule: 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill'
FillValue: NaN | <missing> | '' | '' | '' | NaT | '' | NaN | NaN | NaN | NaN | NaN | NaN | <undefined> | NaN | NaN
To access sub-properties of each variable, use getoptions
Select the three modified variables using the SelectVariableNames property.
opts.SelectedVariableNames = {'first_name','hire_date','department_name'};Import and display the results of the SQL query using the fetch function.
employees_data = fetch(conn,sqlquery,opts)
employees_data=10×3 table
first_name hire_date department_name
___________ ___________ _______________
"Alexander" 03-Jan-2006 IT
"Bruce" 21-May-2007 IT
"David" 25-Jun-2005 IT
"Valli" 05-Feb-2006 IT
"Diana" 07-Feb-2007 IT
"John" 01-Oct-2004 Sales
"Karen" 05-Jan-2005 Sales
"Alberto" 10-Mar-2005 Sales
"Gerald" 15-Oct-2007 Sales
"Eleni" 29-Jan-2008 Sales
Delete the employees and departments database tables using the execute function.
execute(conn,'DROP TABLE employees') execute(conn,'DROP TABLE departments')
Close the database connection.
close(conn)
Customize import options when importing data from a database table. Control the import options by creating an SQLImportOptions object. Specify the location of the database table by using the database catalog and schema. Then, customize import options for different database columns. Import data using the sqlread function.
This example uses the patients.xls file, which contains the columns Gender, Location, SelfAssessedHealthStatus, and Smoker. The example also uses a Microsoft® SQL Server® Version 11.00.2100 database and the Microsoft SQL Server Driver 11.00.5058.
Create a database connection to a Microsoft SQL Server database with Windows® authentication. Specify a blank user name and password.
datasource = 'MS SQL Server Auth'; conn = database(datasource,'','');
Load patient information into the MATLAB® workspace.
patients = readtable('patients.xls');Create the patients database table in the toy_store database catalog and dbo database schema using the patient information.
tablename = 'patients'; sqlwrite(conn,tablename,patients, ... 'Catalog','toy_store','Schema','dbo')
Create an SQLImportOptions object using the patients database table and the databaseImportOptions function. Specify the toy_store database catalog and dbo database schema for the location of the database table.
opts = databaseImportOptions(conn,tablename, ... 'Catalog','toy_store','Schema','dbo');
Display the current import options for the variables selected in the SelectedVariableNames property of the SQLImportOptions object.
vars = opts.SelectedVariableNames; varOpts = getoptions(opts,vars)
varOpts =
1x10 SQLVariableImportOptions array with properties:
Variable Options:
(1) | (2) | (3) | (4) | (5) | (6) | (7) | (8) | (9) | (10)
Name: 'LastName' | 'Gender' | 'Age' | 'Location' | 'Height' | 'Weight' | 'Smoker' | 'Systolic' | 'Diastolic' | 'SelfAssessedHealthStatus'
Type: 'char' | 'char' | 'double' | 'char' | 'double' | 'double' | 'double' | 'double' | 'double' | 'char'
MissingRule: 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill'
FillValue: '' | '' | NaN | '' | NaN | NaN | NaN | NaN | NaN | ''
To access sub-properties of each variable, use getoptions
Change the data types for the Gender, Location, SelfAssessedHealthStatus, and Smoker variables using the setoptions function. Because the Gender, Location, and SelfAssessedHealthStatus variables indicate a finite set of repeating values, change their data type to categorical. Because the Smoker variable stores the values 0 and 1, change its data type to logical. Then, display the updated import options.
opts = setoptions(opts,{'Gender','Location','SelfAssessedHealthStatus'}, ...
'Type','categorical');
opts = setoptions(opts,'Smoker','Type','logical');
varOpts = getoptions(opts,{'Gender','Location','Smoker', ...
'SelfAssessedHealthStatus'})varOpts =
1x4 SQLVariableImportOptions array with properties:
Variable Options:
(1) | (2) | (3) | (4)
Name: 'Gender' | 'Location' | 'Smoker' | 'SelfAssessedHealthStatus'
Type: 'categorical' | 'categorical' | 'logical' | 'categorical'
MissingRule: 'fill' | 'fill' | 'fill' | 'fill'
FillValue: <undefined> | <undefined> | 0 | <undefined>
To access sub-properties of each variable, use getoptions
Import the patients database table using the sqlread function, and display the last eight rows of the table.
data = sqlread(conn,tablename,opts,'Catalog','toy_store','Schema','dbo'); tail(data)
ans=8×10 table
LastName Gender Age Location Height Weight Smoker Systolic Diastolic SelfAssessedHealthStatus
_____________ ______ ___ _________________________ ______ ______ ______ ________ _________ ________________________
{'Foster' } Female 30 St. Mary's Medical Center 70 124 false 130 91 Fair
{'Gonzales' } Male 48 County General Hospital 71 174 false 123 79 Good
{'Bryant' } Female 48 County General Hospital 66 134 false 129 73 Excellent
{'Alexander'} Male 25 County General Hospital 69 171 true 128 99 Good
{'Russell' } Male 44 VA Hospital 69 188 true 124 92 Good
{'Griffin' } Male 49 County General Hospital 70 186 false 119 74 Fair
{'Diaz' } Male 45 County General Hospital 68 172 true 136 93 Good
{'Hayes' } Male 48 County General Hospital 66 177 false 114 86 Fair
Display a summary of the imported data. The sqlread function applies the import options to the variables in the imported data.
summary(data)
Variables:
LastName: 100×1 cell array of character vectors
Gender: 100×1 categorical
Values:
Female 53
Male 47
Age: 100×1 double
Values:
Min 25
Median 39
Max 50
Location: 100×1 categorical
Values:
County General Hospital 39
St. Mary s Medical Center 24
VA Hospital 37
Height: 100×1 double
Values:
Min 60
Median 67
Max 72
Weight: 100×1 double
Values:
Min 111
Median 142.5
Max 202
Smoker: 100×1 logical
Values:
True 34
False 66
Systolic: 100×1 double
Values:
Min 109
Median 122
Max 138
Diastolic: 100×1 double
Values:
Min 68
Median 81.5
Max 99
SelfAssessedHealthStatus: 100×1 categorical
Values:
Excellent 34
Fair 15
Good 40
Poor 11
Delete the patients database table from the toy_store database catalog and the dbo database schema by using the execute function.
sqlquery = ['DROP TABLE toy_store.dbo.' tablename];
execute(conn,sqlquery)Close the database connection.
close(conn)
conn — Database connectionconnection objectDatabase connection, specified as a connection object created with the
database function, connection
object created with the mysql
function, or connection
object created with the postgresql function.
source — SourceSource, specified as a character vector or string scalar. Use the
source input argument to specify the name of a database table or an
SQL query for importing data from a database.
Example: "inventorytable"
Example: "SELECT * FROM inventorytable"
Data Types: char | string
Specify optional
comma-separated pairs of Name,Value arguments. Name is
the argument name and Value is the corresponding value.
Name must appear inside quotes. You can specify several name and value
pair arguments in any order as
Name1,Value1,...,NameN,ValueN.
opts =
databaseImportOptions(conn,"inventorytable",'Catalog',"toystore_doc",'Schema',"dbo")
defines import options for importing data from the inventorytable
database table located in the toystore_doc catalog and
dbo schema.'Catalog' — Database catalog nameDatabase catalog name, specified as the comma-separated pair consisting of 'Catalog' and a character vector or string scalar. A catalog serves as the container for the schemas in a database and contains related metadata information. A database can have numerous catalogs.
Use the 'Catalog' name-value pair argument only when source is a database table.
Example: 'Catalog','toy_store'
Data Types: char | string
'Schema' — Database schema nameDatabase schema name, specified as the comma-separated pair consisting of 'Schema' and a character vector or string scalar. A schema defines the database tables, views, relationships among tables, and other elements. A database catalog can have numerous schemas.
Use the 'Schema' name-value pair argument only when source is a database table.
Example: 'Schema','dbo'
Data Types: char | string
opts — Database import optionsSQLImportOptions objectDatabase import options, returned as an SQLImportOptions object.
close | database | execute | fetch | getoptions | mysql | postgresql | reset | setoptions | sqlread | sqlwrite
You have a modified version of this example. Do you want to open this example with your edits?