fetch
Syntax
Description
returns all rows of data after executing the SQL statement results
= fetch(conn
,sqlquery
)sqlquery
for
the connection
object. fetch
imports data in batches.
customizes options for importing data from an executed SQL query by using the results
= fetch(conn
,sqlquery
,opts
)SQLImportOptions
object.
specifies additional options using one or more name-value arguments with any of the previous
input argument combinations. For example, specify results
= fetch(___,Name,Value
)MaxRows = 5
to import
five rows of data.
Examples
Import All Data Using PostgreSQL Native Interface
Import all product data from a PostgreSQL database table into MATLAB® using the PostgreSQL native interface and the fetch
function. Determine the highest unit cost among products in the table. Then, use a row filter to import only the data for products with a unit cost less than 15.
Create a PostgreSQL native interface database connection to a PostgreSQL database using a data source, username, and password. The database contains the table productTable
.
datasource = "PostgreSQLDataSource"; username = "dbdev"; password = "matlab"; conn = postgresql(datasource,username,password);
Import all the data from productTable
by using the connection object and SQL query. Then, display the first five rows of the imported data.
sqlquery = "SELECT * FROM productTable";
data = fetch(conn,sqlquery);
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"
Determine the highest unit cost for all products in the table.
max(data.unitcost)
ans = 24
Now, import the data using a row filter. The filter condition is that unitсost
must be less than 15.
rf = rowfilter("unitcost"); rf = rf.unitcost < 15; data = fetch(conn,sqlquery,"RowFilter",rf);
Again, display the first five rows of the imported 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 SQL Query Using Import Options
Customize import options when importing data from the results of an SQL query on a PostgreSQL database using the PostgreSQL native interface. 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 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 with a data source name, username, and password.
datasource = "PostgreSQLDataSource"; username = "dbdev"; password = "matlab"; conn = postgresql(datasource,username,password);
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: 'preserve' VariableNames: {'employee_id', 'first_name', 'last_name' ... and 13 more} VariableTypes: {'double', 'string', 'string' ... and 13 more} SelectedVariableNames: {'employee_id', 'first_name', 'last_name' ... and 13 more} FillValues: { NaN, <missing>, <missing> ... 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_1' | 'department_name' | 'manager_id_1' | 'location_id' Type: 'double' | 'string' | 'string' | 'string' | 'string' | 'datetime' | 'string' | 'double' | 'double' | 'double' | 'double' | 'logical' | 'double' | 'string' | 'double' | 'double' MissingRule: 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' FillValue: NaN | <missing> | <missing> | <missing> | <missing> | NaT | <missing> | NaN | NaN | NaN | NaN | 0 | NaN | <missing> | 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. For efficiency, change the data type of the hire_date
variable to string
. Because department_name
designates a finite set of repeating values, change the data type of this variable to categorical
. Because first_name
stores text data, change the data type of this variable to char
.
opts = setoptions(opts,"hire_date","Type","string"); opts = setoptions(opts,"department_name","Type","categorical"); opts = setoptions(opts,"first_name","Type","char"); 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_1' | 'department_name' | 'manager_id_1' | 'location_id' Type: 'double' | 'char' | 'string' | 'string' | 'string' | 'string' | 'string' | 'double' | 'double' | 'double' | 'double' | 'logical' | 'double' | 'categorical' | 'double' | 'double' MissingRule: 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' FillValue: NaN | '' | <missing> | <missing> | <missing> | <missing> | <missing> | NaN | NaN | NaN | NaN | 0 | 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: 'preserve' VariableNames: {'employee_id', 'first_name', 'last_name' ... and 13 more} VariableTypes: {'double', 'char', 'string' ... 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' } "2005-06-25 00:00:00" IT
{'Alberto'} "2005-03-10 00:00:00" Sales
{'Karen' } "2005-01-05 00:00:00" Sales
{'John' } "2004-10-01 00:00:00" 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 SQL Query as Structure
Specify the data return format and the number of imported rows for the results of an SQL query. Import data using the SQL query and the fetch
function.
This 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 with a data source name, user name, and password.
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)
Select all data from the patients
database table and import five rows from the table as a structure. Use the 'DataReturnFormat'
name-value pair argument to specify returning the data as a structure. Also, use the 'MaxRows'
name-value pair argument to specify five rows. Display the imported data.
sqlquery = strcat("SELECT * FROM ",tablename); results = fetch(conn,sqlquery,'DataReturnFormat',"structure", ... 'MaxRows',5)
results=5×1 struct array with fields:
lastname
gender
age
location
height
weight
smoker
systolic
diastolic
selfassessedhealthstatus
Delete the patients
database table using the execute
function.
sqlquery = strcat("DROP TABLE ",tablename);
execute(conn,sqlquery)
Close the database connection.
close(conn)
Retrieve Metadata Information About Imported Data
Retrieve metadata information when importing data from an SQL query. Import data using the fetch
function and explore the metadata information by using dot notation.
This example uses the outages.csv
file, which contains outage data. Also, 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 with a 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 customize the data types of the variables in the outages
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.
sqlquery = "SELECT * FROM outages";
[results,metadata] = fetch(conn,sqlquery);
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 = results(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.
sqlquery
— SQL statement
character vector | string scalar
SQL statement, specified as a character vector or string scalar. The SQL statement can be any
valid SQL statement, including nested queries. The SQL statement can be a stored
procedure, such as {call sp_name (parm1,parm2,...)}
. For stored
procedures that return one or more result sets, use the fetch
function.
Data Types: char
| string
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: results =
fetch(conn,sqlquery,'MaxRows',50,'DataReturnFormat','structure')
imports 50 rows
of data as a structure.
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
fetch
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
DataReturnFormat
— Data return format
'table'
(default) | 'cellarray'
| 'numeric'
| 'structure'
Data return format, specified as the comma-separated pair consisting of
'DataReturnFormat'
and one of these values:
'table'
'cellarray'
'numeric'
'structure'
Use the 'DataReturnFormat'
name-value pair argument to
specify the data type of the results
data. To specify integer
classes for numeric data, use the opts
input argument.
You can specify the value using a character vector or string scalar.
Example: 'DataReturnFormat','cellarray'
imports data as a cell array.
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 thefetch
function imports data. For details, see the Limitations section."modify"
— Remove non-ASCII characters from variable names when thefetch
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;
fetch(conn,sqlquery,"RowFilter",rf)
Output Arguments
results
— Result data
table (default) | cell array | structure | numeric matrix
Result data, returned as a table, cell array, structure, or numeric matrix. The result data contains all rows of data from the executed SQL statement by default.
Use the 'MaxRows'
name-value pair argument to specify the
number of rows of data to import. Use the 'DataReturnFormat'
name-value pair argument to specify the data type of the result data.
When the executed SQL statement does not return any rows, the result data is an empty table.
When you import data, the fetch
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 fetch
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 name-value argument VariableNamingRule
has these
limitations:
The
fetch
function returns an error if you specify theVariableNamingRule
name-value argument and set theDataReturnFormat
name-value argument to"cellarray"
,"structure"
, or"numeric"
.The
fetch
function returns a warning if you set theVariableNamingRule
property of theSQLImportOptions
object to"preserve"
and set theDataReturnFormat
name-value argument to"structure"
.The
fetch
function returns an error if you use theVariableNamingRule
name-value argument with theSQLImportOptions
objectopts
.If you set the
VariableNamingRule
name-value argument to the value"modify"
:These variable names are reserved identifiers for the
table
data type:Properties
,RowNames
, andVariableNames
.The length of each variable name must be less than the number returned by
namelengthmax
.
The name-value argument RowFilter
has this limitation:
The
fetch
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.
Alternative Functionality
App
The fetch
function imports data using the command line. To import
data interactively, use the Database Explorer app.
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.
See Also
Functions
postgresql
|close
|databaseImportOptions
|getoptions
|reset
|setoptions
|execute
Topics
External Websites
MATLAB-Befehl
Sie haben auf einen Link geklickt, der diesem MATLAB-Befehl entspricht:
Führen Sie den Befehl durch Eingabe in das MATLAB-Befehlsfenster aus. Webbrowser unterstützen keine MATLAB-Befehle.
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)