sqlread
Syntax
Description
customizes options for importing data from a database table using the data
= sqlread(conn
,tablename
,opts
)SQLImportOptions
object.
specifies additional options using one or more name-value pair arguments and any of the
previous input argument combinations. For example, specify data
= sqlread(___,Name,Value
)Catalog =
"cat"
to import data from a database table stored in the
"cat"
catalog.
Examples
Import Data from Database Table Using PostgreSQL Native Interface
Use a PostgreSQL native interface database connection to import product data from a database table into MATLAB® using a PostgreSQL database. Then, perform a simple data analysis.
Create a PostgreSQL native interface database connection to a PostgreSQL database. The database contains the table productTable
.
datasource = "PostgreSQLDataSource"; username = "dbdev"; password = "matlab"; conn = postgresql(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 products.
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
Customize import options when importing data from a database table using the PostgreSQL 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 uses a PostgreSQL database version 9.405 database and the libpq driver version 10.12.
Create a PostgreSQL native interface database connection to a PostgreSQL database.
datasource = "PostgreSQLDataSource"; username = "dbdev"; password = "matlab"; conn = postgresql(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: 100×1 string 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 double Values: Min 0 Median 0 Max 1 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 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: 24×1 string 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 double Values: Min 0 Median 0 Max 1 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 = strcat("DROP TABLE ",tablename);
execute(conn,sqlquery)
Close the database connection.
close(conn)
Limit Number of Rows in Imported Data
Use a PostgreSQL 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 PostgreSQL native interface database connection to a PostgreSQL database using the data source name, user name, and password. The database contains the table productTable
.
datasource = "PostgreSQLDataSource"; username = "dbdev"; password = "matlab"; conn = postgresql(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.
head(data,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
Retrieve metadata information when importing data from a database table using the PostgreSQL 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 uses a PostgreSQL database version 9.405 database and the libpq driver version 10.12.
Create a PostgreSQL native interface database connection to a PostgreSQL database using the data source name, user name, and password.
datasource = "PostgreSQLDataSource"; username = "dbdev"; password = "matlab"; conn = postgresql(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)","timestamp","numeric(38,16)", ... "numeric(38,16)","timestamp","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
conn
— PostgreSQL native interface database connection
connection
object
PostgreSQL native interface database connection, specified as a connection
object.
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
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',"toystore_doc",'Schema',"dbo",'MaxRows',5)
imports five rows of data from the database table inventoryTable
stored
in the toystore_doc
catalog and the dbo
schema.
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
Schema
— Database schema name
string scalar | character vector
Database schema name, specified as a string scalar or character vector. A schema defines the database tables, views, relationships among tables, and other elements. A database catalog can have numerous schemas.
Example: Schema = "dbo"
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 thesqlread
function imports data. For details, see the Limitations section."modify"
— Remove non-ASCII characters from variable names when thesqlread
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
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 PostgreSQL database to the MATLAB data type. This table maps the data type of a database column to the
converted MATLAB data type. The first and second columns contain the scalar data type
mappings, whereas the third and fourth columns contain the array data type
mappings.
Details for how MATLAB reads PostgreSQL array types consist of the following:
MATLAB reads columns of PostgreSQL array types into cell arrays. The dimensions of the array in each cell match the dimension of the array stored in the PostgreSQL database table row.
The data of the arrays in the MATLAB cell arrays depends on the underlying data in the PostgreSQL arrays.
The default data type of each PostgreSQL array type matches its scalar equivalent.
Scalar Data Type Mappings | Array Data Type Mappings (since R2024b) | ||
---|---|---|---|
PostgreSQL | MATLAB | PostgreSQL | MATLAB |
|
|
| Cell array of logical values |
|
|
| Cell array of double values |
|
|
| Cell array of double values |
|
|
| Cell array of double values |
|
|
| Cell array of double values |
|
|
| Cell array of double values |
|
|
| Cell array of double values |
|
|
| Cell array of double values |
|
| N/A | N/A |
|
| N/A | N/A |
|
| N/A | N/A |
|
|
| Cell array of double values |
|
|
| Cell array of strings |
|
|
| Cell array of strings |
|
|
| Cell array of strings |
|
|
| Cell array of strings |
|
|
| Cell array of datetimes |
|
|
| Cell array of datetimes |
|
|
| Cell array of datetimes |
|
|
| Cell array of datetimes |
|
|
| Cell array of durations |
|
|
| Cell array of durations |
|
|
| Cell array of calendarDurations |
|
|
| Cell array of calendarDurations |
|
|
| Cell array of categoricals |
|
|
| Cell array of strings |
|
|
| Cell array of strings |
|
|
| Cell array of strings |
|
|
| Cell array of strings |
|
|
| Cell array of strings |
metadata
— Metadata information
table
Metadata information, returned as a table with these variables.
Variable Name | Variable Description | Variable Data Type |
---|---|---|
| Data type of each variable in the imported data | Cell array of character vectors |
| Value of missing data for each variable in the imported data | Cell array of missing data values |
| 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 theVariableNamingRule
name-value argument with theSQLImportOptions
objectopts
.When the
VariableNamingRule
name-value pair argument is set to the value"modify"
:The variable names
Properties
,RowNames
, andVariableNames
are reserved identifiers for thetable
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 theRowFilter
name-value argument with theSQLImportOptions
objectopts
. It is ambiguous which of theRowFilter
object to use in this case, especially if the filter conditions are different.
Version History
Introduced in R2020bR2024b: Support for reading array types
You can now read array types without any extra data conversions.
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.
MATLAB Command
You clicked a link that corresponds to this MATLAB command:
Run the command by entering it in the MATLAB Command Window. Web browsers do not support MATLAB commands.
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)