sqlread

Import data into MATLAB from MySQL database table

Syntax

data = sqlread(conn,tablename)

data = sqlread(conn,tablename,opts)

data = sqlread(___,Name,Value)

[data,metadata] = sqlread(___)

Description

data = sqlread(conn,tablename) returns a table by importing data into MATLAB^® from a MySQL^® database table. Executing this function is the equivalent of writing a SELECT * FROM tablename SQL statement in ANSI SQL.

example

data = sqlread(conn,tablename,opts) customizes options for importing data from a database table using the SQLImportOptions object.

example

data = sqlread(___,Name,Value) specifies additional options using one or more name-value arguments with any of the previous input argument combinations. For example, specify Catalog = "cat" to import data from a database table stored in the "cat" catalog.

example

[data,metadata] = sqlread(___) also returns the metadata table, which contains metadata information about the imported data.

example

Examples

collapse all

Import Data from Database Table Using MySQL Native Interface

Open Live Script

Use a MySQL® native interface database connection to import product data from a database table into MATLAB® using a MySQL database. Then, perform a simple data analysis.

Create a MySQL native interface database connection to a MySQL database. The database contains the table productTable.

datasource = "MySQLNative";
username = "root";
password = "matlab";

conn = mysql(datasource,username,password);

Import data from the database table productTable. The sqlread function returns a MATLAB table that contains the product data.

tablename = "productTable";
data = sqlread(conn,tablename);

Display the first five rows of product data.

head(data,5)

    productNumber    stockNumber    supplierNumber    unitCost    productDescription
    _____________    ___________    ______________    ________    __________________

          9          1.2597e+05          1003            13        "Victorian Doll" 
          8          2.1257e+05          1001             5        "Train Set"      
          7          3.8912e+05          1007            16        "Engine Kit"     
          2          4.0031e+05          1002             9        "Painting Set"   
          4          4.0034e+05          1008            21        "Space Cruiser"

Now, import the data using a row filter. The filter condition is that unitCost must be less than 15.

rf = rowfilter("unitCost");
rf = rf.unitCost < 15;
data = sqlread(conn,tablename,"RowFilter",rf);

Again, display the first five rows of product data.

head(data,5)

    productNumber    stockNumber    supplierNumber    unitCost    productDescription
    _____________    ___________    ______________    ________    __________________

          9          1.2597e+05          1003            13       "Victorian Doll"  
          8          2.1257e+05          1001             5       "Train Set"       
          2          4.0031e+05          1002             9       "Painting Set"    
          1          4.0034e+05          1001            14       "Building Blocks" 
          5          4.0046e+05          1005             3       "Tin Soldier"

Close the database connection.

close(conn)

Import Data from Database Table Using Import Options

Open Live Script

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.

datasource = "MySQLNative";
username = "root";
password = "matlab";

conn = mysql(datasource,username,password);

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: '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.

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:   '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.

opts = setoptions(opts,{'Gender','Location','SelfAssessedHealthStatus'}, ...
    'Type','categorical');
opts = setoptions(opts,'Smoker','Type','double');

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' | '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.

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        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.

summary(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.RowFilter = opts.RowFilter.Age > 40 & opts.RowFilter.Height <= 68

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.

data = sqlread(conn,tablename,opts);
summary(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.

sqlquery = strcat("DROP TABLE ",tablename);
execute(conn,sqlquery)

Close the database connection.

close(conn)

Limit Number of Rows in Imported Data

Open Live Script

Use a MySQL® native interface database connection to import a limited number of rows of product data from a database table into MATLAB®. Then, sort and filter the rows in the imported data, and perform a simple data analysis.

Create a MySQL® native interface database connection to a MySQL database using the data source name, user name, and password. The database contains the table productTable.

datasource = "MySQLNative";
username = "root";
password = "matlab";
conn = mysql(datasource,username,password);

Import data from the table productTable. Limit the number of rows by setting the 'MaxRows' name-value pair argument to 10. The data table contains the product data.

tablename = "productTable";
data = sqlread(conn,tablename,'MaxRows',10);

Display the first few rows of product data.

data(1:3,:)

ans=3×5 table
    productNumber    stockNumber    supplierNumber    unitCost    productDescription
    _____________    ___________    ______________    ________    __________________

          9          1.2597e+05          1003            13        "Victorian Doll" 
          8          2.1257e+05          1001             5        "Train Set"      
          7          3.8912e+05          1007            16        "Engine Kit"

Display the first few product descriptions.

data.productDescription(1:3)

ans = 3×1 string
    "Victorian Doll"
    "Train Set"
    "Engine Kit"

Sort the rows in data by the product description column in alphabetical order.

column = "productDescription";
data = sortrows(data,column);

Display the first few product descriptions after sorting.

data.productDescription(1:3)

ans = 3×1 string
    "Building Blocks"
    "Engine Kit"
    "Painting Set"

Close the database connection.

close(conn)

Retrieve Metadata Information About Imported Data

Open Live Script

Retrieve metadata information when importing data from a database table using the MySQL® native interface. Import data using the sqlread function and explore the metadata information by using dot notation.

This example uses the outages.csv file, which contains outage data. 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 using the data source name, user name, and password.

datasource = "MySQLNative";
username = "root";
password = "matlab";
conn = mysql(datasource,username,password);

Load outage information into the MATLAB® workspace.

outages = readtable("outages.csv");

Create the outages database table using the outage information. Use the 'ColumnType' name-value pair argument to specify the data types of the variables in the MATLAB® table.

tablename = "outages";
sqlwrite(conn,tablename,outages, ...
    'ColumnType',["varchar(120)","datetime","numeric(38,16)", ...
    "numeric(38,16)","datetime","varchar(150)"])

Import the data into the MATLAB workspace and return metadata information about the imported data.

[data,metadata] = sqlread(conn,tablename);

View the names of the variables in the imported data.

metadata.Properties.RowNames

ans = 6×1 cell
    {'Region'         }
    {'OutageTime'     }
    {'Loss'           }
    {'Customers'      }
    {'RestorationTime'}
    {'Cause'          }

View the data type of each variable in the imported data.

metadata.VariableType

ans = 6×1 cell
    {'string'  }
    {'datetime'}
    {'double'  }
    {'double'  }
    {'datetime'}
    {'string'  }

View the missing data value for each variable in the imported data.

metadata.FillValue

ans=6×1 cell array
    {1×1 missing}
    {[NaT      ]}
    {[      NaN]}
    {[      NaN]}
    {[NaT      ]}
    {1×1 missing}

View the indices of the missing data for each variable in the imported data.

metadata.MissingRows

ans=6×1 cell array
    {  0×1 double}
    {  0×1 double}
    {604×1 double}
    {328×1 double}
    { 29×1 double}
    {  0×1 double}

Display the first eight rows of the imported data that contain missing restoration time values. data contains restoration time values in the fifth variable. Use the numeric indices to find the rows with missing data.

index = metadata.MissingRows{5,1};
nullrestoration = data(index,:);
head(nullrestoration)

ans=8×6 table
      Region            OutageTime          Loss     Customers     RestorationTime          Cause       
    ___________    ____________________    ______    __________    _______________    __________________

    "SouthEast"    23-Jan-2003 00:49:00    530.14    2.1204e+05          NaT          "winter storm"    
    "NorthEast"    18-Sep-2004 05:54:00         0             0          NaT          "equipment fault" 
    "MidWest"      20-Apr-2002 16:46:00     23141           NaN          NaT          "unknown"         
    "NorthEast"    16-Sep-2004 19:42:00      4718           NaN          NaT          "unknown"         
    "SouthEast"    14-Sep-2005 15:45:00    1839.2    3.4144e+05          NaT          "severe storm"    
    "SouthEast"    17-Aug-2004 17:34:00     624.1    1.7879e+05          NaT          "severe storm"    
    "SouthEast"    28-Jan-2006 23:13:00    498.78           NaN          NaT          "energy emergency"
    "West"         20-Jun-2003 18:22:00         0             0          NaT          "energy emergency"

Delete the outages database table using the execute function.

sqlstr = "DROP TABLE ";
sqlquery = strcat(sqlstr,tablename);
execute(conn,sqlquery)

Close the database connection.

close(conn)

Input Arguments

collapse all

`conn` — MySQL native interface database connection
`connection` object

MySQL native interface database connection, specified as a connection object. Starting in R2024a, it is recommended that you use setSecret and getSecret to store and retrieve your credentials for databases that require authentication. For more details, refer to this example.

`tablename` — Database table name
string scalar | character vector

Database table name, specified as a string scalar or character vector denoting the name of a table in the database.

Example: "employees"

Data Types: string | char

`opts` — Database import options
`SQLImportOptions` object

Database import options, specified as an SQLImportOptions object.

Name-Value Arguments

collapse all

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: data = sqlread(conn,'inventoryTable','Catalog','toy_store','MaxRows',5) imports five rows of data from the database table inventoryTable stored in the toy_store catalog.

`Catalog` — Database catalog name
string scalar | character vector

Database catalog name, specified as a string scalar or character vector. A catalog serves as the container for the schemas in a database and contains related metadata information. A database can have multiple catalogs.

Example: Catalog = "toy_store"

Data Types: string | char

`MaxRows` — Maximum number of rows to return
positive numeric scalar

Maximum number of rows to return, specified as the comma-separated pair consisting of 'MaxRows' and a positive numeric scalar. By default, the sqlread function returns all rows from the executed SQL query. Use this name-value pair argument to limit the number of rows imported into MATLAB.

Example: 'MaxRows',10

Data Types: double

`VariableNamingRule` — Variable naming rule
`"preserve"` (default) | `"modify"`

Variable naming rule, specified as the comma-separated pair consisting of 'VariableNamingRule' and one of these values:

"preserve" — Preserve most variable names when the sqlread function imports data. For details, see the Limitations section.
"modify" — Remove non-ASCII characters from variable names when the sqlread function imports data.

Example: 'VariableNamingRule',"modify"

Data Types: string

`RowFilter` — Row filter condition
`<unconstrained>` (default) | `matlab.io.RowFilter` object

Row filter condition, specified as a matlab.io.RowFilter object.

Example: rf = rowfilter("productnumber"); rf = rf.productnumber <= 5; sqlread(conn,tablename,"RowFilter",rf)

Output Arguments

collapse all

`data` — Imported data
table

Imported data, returned as a table. The rows of the table correspond to the rows in the database table tablename. The variables in the table correspond to each column in the database table.

If the database table contains no data to import, then data is an empty table.

When you import data, the sqlread function converts the data type of each column from the MySQL database to the MATLAB data type. This table maps the data type of a database column to the converted MATLAB data type.

MySQL Data Type	MATLAB Data Type
`BIT`	`logical`
`TINYINT`	`double`
`SMALLINT`	`double`
`BIGINT`	`double`
`REAL`	`double`
`DOUBLE`	`double`
`DECIMAL`	`double`
`NUMERIC`	`double`
`CHAR`	`string`
`VARCHAR`	`string`
`LONGVARCHAR`	`string`
`TIMESTAMP`	`datetime`
`DATE`	`datetime`
`TIME`	`duration`
`YEAR`	`double`
`ENUM`	`categorical`
`JSON`	`char`

`metadata` — Metadata information
table

Metadata information, returned as a table with these variables.

Variable Name	Variable Description	Variable Data Type
`VariableType`	Data type of each variable in the imported data	Cell array of character vectors
`FillValue`	Value of missing data for each variable in the imported data	Cell array of missing data values
`MissingRows`	Indices for each occurrence of missing data in each variable of the imported data	Cell array of numeric indices

By default, the sqlread function imports text data as a character vector and numeric data as a double. FillValue is an empty character array (for text data) or NaN (for numeric data) by default. To change the missing data value to another value, use the SQLImportOptions object.

The RowNames property of the metadata table contains the names of the variables in the imported data.

Limitations

The sqlread function returns an error when you use the VariableNamingRule name-value argument with the SQLImportOptions object opts.
When the VariableNamingRule name-value pair argument is set to the value "modify":
- The variable names Properties, RowNames, and VariableNames are reserved identifiers for the table data type.
- The length of each variable name must be less than the number returned by namelengthmax.
The sqlread function returns an error if you specify the RowFilter name-value argument with the SQLImportOptions object opts. It is ambiguous which of the RowFilter object to use in this case, especially if the filter conditions are different.

Version History

Introduced in R2020b

expand all

R2023a: Selectively import rows of data based on filter condition

You can use the RowFilter name-value argument to selectively import rows of data from a database table.

sqlread

Syntax

Description

Examples

Import Data from Database Table Using MySQL Native Interface

Import Data from Database Table Using Import Options

Limit Number of Rows in Imported Data

Retrieve Metadata Information About Imported Data

Input Arguments

`conn` — MySQL native interface database connection
`connection` object

`tablename` — Database table name
string scalar | character vector

`opts` — Database import options
`SQLImportOptions` object

Name-Value Arguments

`Catalog` — Database catalog name
string scalar | character vector

`MaxRows` — Maximum number of rows to return
positive numeric scalar

`VariableNamingRule` — Variable naming rule
`"preserve"` (default) | `"modify"`

`RowFilter` — Row filter condition
`<unconstrained>` (default) | `matlab.io.RowFilter` object

Output Arguments

`data` — Imported data
table

`metadata` — Metadata information
table

Limitations

Version History

R2023a: Selectively import rows of data based on filter condition

See Also

Functions

Topics

sqlread

Syntax

Description

Examples

Import Data from Database Table Using MySQL Native Interface

Import Data from Database Table Using Import Options

Limit Number of Rows in Imported Data

Retrieve Metadata Information About Imported Data

Input Arguments

conn — MySQL native interface database connection connection object

tablename — Database table name string scalar | character vector

opts — Database import options SQLImportOptions object

Name-Value Arguments

Catalog — Database catalog name string scalar | character vector

MaxRows — Maximum number of rows to return positive numeric scalar

VariableNamingRule — Variable naming rule "preserve" (default) | "modify"

RowFilter — Row filter condition <unconstrained> (default) | matlab.io.RowFilter object

Output Arguments

data — Imported data table

metadata — Metadata information table

Limitations

Version History

R2023a: Selectively import rows of data based on filter condition

See Also

Functions

Topics

`conn` — MySQL native interface database connection
`connection` object

`tablename` — Database table name
string scalar | character vector

`opts` — Database import options
`SQLImportOptions` object

`Catalog` — Database catalog name
string scalar | character vector

`MaxRows` — Maximum number of rows to return
positive numeric scalar

`VariableNamingRule` — Variable naming rule
`"preserve"` (default) | `"modify"`

`RowFilter` — Row filter condition
`<unconstrained>` (default) | `matlab.io.RowFilter` object

`data` — Imported data
table

`metadata` — Metadata information
table