databaseImportOptions
Define import options for database data
Description
creates an opts
= databaseImportOptions(conn
,source
)SQLImportOptions
object using the database connection and a source, which is a database table name or SQL
query.
specifies additional options using one or more name-value pair arguments. For example,
opts
= databaseImportOptions(conn
,source
,Name,Value
)'Catalog',"toystore_doc"
retrieves data from the
toystore_doc
database catalog.
Examples
Import Data from Database Table Using Import Options
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 username 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 } RowFilter: <unconstrained> 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)
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
Now set the filter condition to import only data for patients older than 40 years and not taller than 68 inches.
opts.RowFilter = opts.RowFilter.Age > 40 & opts.RowFilter.Height <= 68
opts = SQLImportOptions with properties: ExcludeDuplicates: false VariableNamingRule: 'modify' VariableNames: {'LastName', 'Gender', 'Age' ... and 7 more} VariableTypes: {'char', 'categorical', 'double' ... and 7 more} SelectedVariableNames: {'LastName', 'Gender', 'Age' ... and 7 more} FillValues: {'', <undefined>, NaN ... and 7 more } RowFilter: Age > 40 & Height <= 68 VariableOptions: Show all 10 VariableOptions
Again, import the patients
database table using the sqlread
function, and display a summary of the imported data.
data = sqlread(conn,tablename,opts); summary(data)
Variables: LastName: 24×1 cell array of character vectors Gender: 24×1 categorical Values: Female 17 Male 7 Age: 24×1 double Values: Min 41 Median 45.5 Max 50 Location: 24×1 categorical Values: County General Hospital 13 St. Mary s Medical Center 5 VA Hospital 6 Height: 24×1 double Values: Min 62 Median 66 Max 68 Weight: 24×1 double Values: Min 119 Median 137 Max 194 Smoker: 24×1 logical Values: True 8 False 16 Systolic: 24×1 double Values: Min 114 Median 121.5 Max 138 Diastolic: 24×1 double Values: Min 68 Median 81.5 Max 96 SelfAssessedHealthStatus: 24×1 categorical Values: Excellent 7 Fair 3 Good 10 Poor 4
Delete the patients
database table using the execute
function.
sqlquery = ['DROP TABLE ' tablename];
execute(conn,sqlquery)
Close the database connection.
close(conn)
Import Data from SQL Query Using Import Options
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 a database connection to a Microsoft SQL Server database with Windows® authentication. Specify a blank username 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 = strcat("SELECT * from employees e join departments d ", ... "on (e.department_id = d.department_id) WHERE ", ... "(job_id = 'IT_PROG' or job_id = '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 } RowFilter: <unconstrained> 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"];
Set the filter condition to import only the data for the employees hired before January 1, 2006.
opts.RowFilter = opts.RowFilter.hire_date < datetime(2006,01,01)
opts = SQLImportOptions with properties: ExcludeDuplicates: false VariableNamingRule: 'modify' VariableNames: {'employee_id', 'first_name', 'last_name' ... and 13 more} VariableTypes: {'double', 'string', 'char' ... and 13 more} SelectedVariableNames: {'first_name', 'hire_date', 'department_name'} FillValues: { NaN, <missing>, '' ... and 13 more } RowFilter: hire_date < 01-Jan-2006 VariableOptions: Show all 16 VariableOptions
Import and display the results of the SQL query using the fetch
function.
employees_data = fetch(conn,sqlquery,opts)
employees_data=4×3 table
first_name hire_date department_name
__________ ___________ _______________
"David" 25-Jun-2005 IT
"John" 01-Oct-2004 Sales
"Karen" 05-Jan-2005 Sales
"Alberto" 10-Mar-2005 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)
Import Data from Database Table Using Import Options with Specified Catalog and Schema
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)
Input Arguments
conn
— Database connection
connection
object
Database connection, specified as a connection
object created with the
database
function, connection
object created with the mysql
function, connection
object created with the postgresql
function, or sqlite
object.
Create a parallelizable databaseDatastore
object by first creating
a parallel pool constant. You can use the getSecret
function to retrieve your user credentials when you create this constant.
Example: conn =
parallel.pool.Constant(@()postgresql(getSecret("PostgreSQL.username"),getSecret("Postgresql.password"),"Server","localhost","DatabaseName","toy_store"),@close);
source
— Source
character vector | string scalar
Source, 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
Name-Value Arguments
Specify optional pairs of arguments as
Name1=Value1,...,NameN=ValueN
, where Name
is
the argument name and Value
is the corresponding value.
Name-value arguments must appear after other arguments, but the order of the
pairs does not matter.
Before R2021a, use commas to separate each name and value, and enclose
Name
in quotes.
Example: 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 name
character vector | string scalar
Database 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 name
character vector | string scalar
Database 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
Output Arguments
opts
— Database import options
SQLImportOptions
object
Database import options, returned as an SQLImportOptions
object.
Version History
Introduced in R2018b
See Also
setoptions
| getoptions
| reset
| close
| database
| execute
| sqlwrite
| sqlread
| fetch
| mysql
| postgresql
External Websites
MATLAB 명령
다음 MATLAB 명령에 해당하는 링크를 클릭했습니다.
명령을 실행하려면 MATLAB 명령 창에 입력하십시오. 웹 브라우저는 MATLAB 명령을 지원하지 않습니다.
Select a Web Site
Choose a web site to get translated content where available and see local events and offers. Based on your location, we recommend that you select: .
You can also select a web site from the following list:
How to Get Best Site Performance
Select the China site (in Chinese or English) for best site performance. Other MathWorks country sites are not optimized for visits from your location.
Americas
- América Latina (Español)
- Canada (English)
- United States (English)
Europe
- Belgium (English)
- Denmark (English)
- Deutschland (Deutsch)
- España (Español)
- Finland (English)
- France (Français)
- Ireland (English)
- Italia (Italiano)
- Luxembourg (English)
- Netherlands (English)
- Norway (English)
- Österreich (Deutsch)
- Portugal (English)
- Sweden (English)
- Switzerland
- United Kingdom (English)