Customize import options when importing data from a database table using the MySQL® native interface. 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 MySQL database version 5.7.22 with the MySQL Connector/C++ driver version 8.0.15.
Create a MySQL native interface database connection to a MySQL database.
Load patient information into the MATLAB® workspace.
Create the patients database table using the patient information.
Create an SQLImportOptions object using the patients database table and the databaseImportOptions function.
opts =
SQLImportOptions with properties:
ExcludeDuplicates: false
VariableNamingRule: 'preserve'
VariableNames: {'LastName', 'Gender', 'Age' ... and 7 more}
VariableTypes: {'string', 'string', 'double' ... and 7 more}
SelectedVariableNames: {'LastName', 'Gender', 'Age' ... and 7 more}
FillValues: { <missing>, <missing>, NaN ... and 7 more }
RowFilter: <unconstrained>
VariableOptions: Show all 10 VariableOptions
Display the current import options for the variables in the SelectedVariableNames property of the SQLImportOptions object.
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: 'string' | 'string' | 'double' | 'string' | 'double' | 'double' | 'logical' | 'double' | 'double' | 'string'
MissingRule: 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill'
FillValue: <missing> | <missing> | NaN | <missing> | NaN | NaN | 0 | NaN | NaN | <missing>
To access sub-properties of each variable, use getoptions
Change the data types for the Gender, Location, Smoker, and SelfAssessedHealthStatus 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 double. Then, display the updated import options.
varOpts =
1x4 SQLVariableImportOptions array with properties:
Variable Options:
(1) | (2) | (3) | (4)
Name: 'Gender' | 'Location' | 'Smoker' | 'SelfAssessedHealthStatus'
Type: 'categorical' | 'categorical' | 'double' | '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.
LastName Gender Age Location Height Weight Smoker Systolic Diastolic SelfAssessedHealthStatus
___________ ______ ___ _________________________ ______ ______ ______ ________ _________ ________________________
"Foster" Female 30 St. Mary's Medical Center 70 124 0 130 91 Fair
"Gonzales" Male 48 County General Hospital 71 174 0 123 79 Good
"Bryant" Female 48 County General Hospital 66 134 0 129 73 Excellent
"Alexander" Male 25 County General Hospital 69 171 1 128 99 Good
"Russell" Male 44 VA Hospital 69 188 1 124 92 Good
"Griffin" Male 49 County General Hospital 70 186 0 119 74 Fair
"Diaz" Male 45 County General Hospital 68 172 1 136 93 Good
"Hayes" Male 48 County General Hospital 66 177 0 114 86 Fair
Display a summary of the imported data. The sqlread function applies the import options to the variables in the imported data.
Variables:
LastName: 200×1 string
Gender: 200×1 categorical
Values:
Female 106
Male 94
Age: 200×1 double
Values:
Min 25
Median 39
Max 50
Location: 200×1 categorical
Values:
County General Hospital 78
St. Mary s Medical Center 48
VA Hospital 74
Height: 200×1 double
Values:
Min 60
Median 67
Max 72
Weight: 200×1 double
Values:
Min 111
Median 142.5
Max 202
Smoker: 200×1 double
Values:
Min 0
Median 0
Max 1
Systolic: 200×1 double
Values:
Min 109
Median 122
Max 138
Diastolic: 200×1 double
Values:
Min 68
Median 81.5
Max 99
SelfAssessedHealthStatus: 200×1 categorical
Values:
Excellent 68
Fair 30
Good 80
Poor 22
Now set the filter condition to import only data for patients older than 40 year and not taller than 68 inches.
opts =
SQLImportOptions with properties:
ExcludeDuplicates: false
VariableNamingRule: 'preserve'
VariableNames: {'LastName', 'Gender', 'Age' ... and 7 more}
VariableTypes: {'string', 'categorical', 'double' ... and 7 more}
SelectedVariableNames: {'LastName', 'Gender', 'Age' ... and 7 more}
FillValues: { <missing>, <undefined>, NaN ... and 7 more }
RowFilter: Height <= 68 & Age > 40
VariableOptions: Show all 10 VariableOptions
Again, import the patients database table using the sqlread function, and display a summary of the imported data.
Variables:
LastName: 48×1 string
Gender: 48×1 categorical
Values:
Female 34
Male 14
Age: 48×1 double
Values:
Min 41
Median 45.5
Max 50
Location: 48×1 categorical
Values:
County General Hospital 26
St. Mary s Medical Center 10
VA Hospital 12
Height: 48×1 double
Values:
Min 62
Median 66
Max 68
Weight: 48×1 double
Values:
Min 119
Median 137
Max 194
Smoker: 48×1 double
Values:
Min 0
Median 0
Max 1
Systolic: 48×1 double
Values:
Min 114
Median 121.5
Max 138
Diastolic: 48×1 double
Values:
Min 68
Median 81.5
Max 96
SelfAssessedHealthStatus: 48×1 categorical
Values:
Excellent 14
Fair 6
Good 20
Poor 8
Delete the patients database table using the execute function.
Close the database connection.
