fetch
Import data into MATLAB workspace using SQLite connection
Description
specifies additional options using one or more name-value arguments. For example,
results
= fetch(conn
,sqlquery
,Name=Value
)MaxRows=5
imports five rows of data.
Examples
Import Data from Database Table in SQLite Database File
Import airline data from a table in an SQLite database file into MATLAB®.
First, create an SQLite connection to the file sample_dataset.db
that contains the table airlinesmall
. The SQLite connection conn
is an sqlite
object.
dbfile = fullfile(matlabroot,"toolbox","database","database","sample_dataset.db"); conn = sqlite(dbfile);
Import the first ten rows of data from airlinesmall
. The output contains the imported data as a table.
sqlquery = 'SELECT * FROM airlinesmall LIMIT 10';
results = fetch(conn,sqlquery)
results=10×29 table
YEAR MONTH DayofMonth DayOfWeek DepTime CRSDepTime ArrTime CRSArrTime UniqueCarrier FlightNum TailNum ActualElapsedTime CRSElapsedTime AirTime ArrDelay DepDelay Origin Dest Distance TaxiIn TaxiOut Cancelled CancellationCode Diverted CarrierDelay WeatherDelay NASDelay SecurityDelay LateAircraftDelay
____ _____ __________ _________ _______ __________ _______ __________ _____________ _________ ________ _________________ ______________ _______ ________ ________ ______ _____ ________ ______ _______ _________ ________________ ________ ____________ ____________ ________ _____________ _________________
1996 1 18 4 2117 2120 2305 2259 "HP" 415 "N637AW" 108 99 "85" 6 -3 "COS" "PHX" 551 "5" "18" 0 "NA" 0 "NA" "NA" "NA" "NA" "NA"
1996 1 25 4 1712 0 1908 0 "AA" 1733 "N283AA" 176 151 "138" 29 4 "DFW" "PHX" 868 "6" "32" 0 "NA" 0 "NA" "NA" "NA" "NA" "NA"
1996 1 26 5 755 755 938 940 "WN" 708 "N675" 163 165 "151" -2 0 "HOU" "PHX" 1020 "3" "9" 0 "NA" 0 "NA" "NA" "NA" "NA" "NA"
1996 1 17 3 732 731 934 930 "HP" 546 "N622AW" 62 59 "39" 4 1 "LAS" "PHX" 256 "5" "18" 0 "NA" 0 "NA" "NA" "NA" "NA" "NA"
1996 1 12 5 1252 1245 1511 1500 "HP" 610 "N905AW" 79 75 "58" 11 7 "LAX" "PHX" 370 "3" "18" 0 "NA" 0 "NA" "NA" "NA" "NA" "NA"
1996 1 23 2 2040 2000 2245 2210 "WN" 1306 "N334" 65 70 "51" 35 40 "LAX" "PHX" 370 "4" "10" 0 "NA" 0 "NA" "NA" "NA" "NA" "NA"
1996 1 24 3 913 915 NaN 1135 "NW" 1815 "N308US" NaN 200 "NA" NaN -2 "MSP" "PHX" 1276 "0" "15" 0 "NA" 1 "NA" "NA" "NA" "NA" "NA"
1996 1 27 6 855 855 1056 1100 "WN" 822 "N612" 61 65 "47" -4 0 "ONT" "PHX" 325 "2" "12" 0 "NA" 0 "NA" "NA" "NA" "NA" "NA"
1996 1 16 2 1441 1445 1708 1721 "HP" 211 "N165AW" 87 96 "74" -13 -4 "RNO" "PHX" 601 "4" "9" 0 "NA" 0 "NA" "NA" "NA" "NA" "NA"
1996 1 30 2 1344 1344 1730 1724 "AS" 98 "N778AS" 166 160 "146" 6 0 "SEA" "PHX" 1107 "2" "18" 0 "NA" 0 "NA" "NA" "NA" "NA" "NA"
Display the names of the unique airline carriers.
unique(results.UniqueCarrier)
ans = 5×1 string
"AA"
"AS"
"HP"
"NW"
"WN"
Use a row filter to display the entries where DayOfWeek
is 3.
rf = rowfilter("DayOfWeek"); rf = rf.DayOfWeek == 3; results = fetch(conn,sqlquery,"RowFilter",rf)
results=2×29 table
YEAR MONTH DayofMonth DayOfWeek DepTime CRSDepTime ArrTime CRSArrTime UniqueCarrier FlightNum TailNum ActualElapsedTime CRSElapsedTime AirTime ArrDelay DepDelay Origin Dest Distance TaxiIn TaxiOut Cancelled CancellationCode Diverted CarrierDelay WeatherDelay NASDelay SecurityDelay LateAircraftDelay
____ _____ __________ _________ _______ __________ _______ __________ _____________ _________ ________ _________________ ______________ _______ ________ ________ ______ _____ ________ ______ _______ _________ ________________ ________ ____________ ____________ ________ _____________ _________________
1996 1 17 3 732 731 934 930 "HP" 546 "N622AW" 62 59 "39" 4 1 "LAS" "PHX" 256 "5" "18" 0 "NA" 0 "NA" "NA" "NA" "NA" "NA"
1996 1 24 3 913 915 NaN 1135 "NW" 1815 "N308US" NaN 200 "NA" NaN -2 "MSP" "PHX" 1276 "0" "15" 0 "NA" 1 "NA" "NA" "NA" "NA" "NA"
Close the SQLite connection.
close(conn)
Limit Number of Rows in Imported Data
Use the MATLAB® interface to SQLite to import a limited number of rows of airline data into MATLAB from a table in an SQLite database file.
First, create an SQLite connection to the file sample_dataset.db
that contains the table airlinesmall
. The SQLite connection conn
is an sqlite
object.
dbfile = fullfile(matlabroot,"toolbox","database","database","sample_dataset.db"); conn = sqlite(dbfile);
Import five rows of data from airlinesmall
by using the MaxRows
name-value argument. The output contains five rows of imported data as a table.
sqlquery = "SELECT * FROM airlinesmall";
results = fetch(conn,sqlquery,MaxRows=5)
results=5×29 table
YEAR MONTH DayofMonth DayOfWeek DepTime CRSDepTime ArrTime CRSArrTime UniqueCarrier FlightNum TailNum ActualElapsedTime CRSElapsedTime AirTime ArrDelay DepDelay Origin Dest Distance TaxiIn TaxiOut Cancelled CancellationCode Diverted CarrierDelay WeatherDelay NASDelay SecurityDelay LateAircraftDelay
____ _____ __________ _________ _______ __________ _______ __________ _____________ _________ ________ _________________ ______________ _______ ________ ________ ______ _____ ________ ______ _______ _________ ________________ ________ ____________ ____________ ________ _____________ _________________
1996 1 18 4 2117 2120 2305 2259 "HP" 415 "N637AW" 108 99 "85" 6 -3 "COS" "PHX" 551 "5" "18" 0 "NA" 0 "NA" "NA" "NA" "NA" "NA"
1996 1 25 4 1712 0 1908 0 "AA" 1733 "N283AA" 176 151 "138" 29 4 "DFW" "PHX" 868 "6" "32" 0 "NA" 0 "NA" "NA" "NA" "NA" "NA"
1996 1 26 5 755 755 938 940 "WN" 708 "N675" 163 165 "151" -2 0 "HOU" "PHX" 1020 "3" "9" 0 "NA" 0 "NA" "NA" "NA" "NA" "NA"
1996 1 17 3 732 731 934 930 "HP" 546 "N622AW" 62 59 "39" 4 1 "LAS" "PHX" 256 "5" "18" 0 "NA" 0 "NA" "NA" "NA" "NA" "NA"
1996 1 12 5 1252 1245 1511 1500 "HP" 610 "N905AW" 79 75 "58" 11 7 "LAX" "PHX" 370 "3" "18" 0 "NA" 0 "NA" "NA" "NA" "NA" "NA"
Determine the largest flight number.
data = results.FlightNum; max(data)
ans = int64
1733
Close the SQLite connection.
close(conn)
Copyright 2021 The MathWorks, Inc.
Input Arguments
conn
— SQLite database connection
sqlite
object
SQLite database connection, specified as an sqlite
object created using the sqlite
function.
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. For information about the SQL query language, see the SQL Tutorial.
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.
Example: fetch(conn,sqlquery,MaxRows=5)
imports five rows of
data.
MaxRows
— Maximum number of rows to return
positive numeric scalar
Maximum number of rows to return, specified as a positive numeric scalar. By
default, the fetch
function returns all rows from the
executed SQL query. Use this name-value 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 one of these values:
"preserve"
— Preserve most variable names when thefetch
function imports data."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
Result data, returned as a table. The result data contains all rows of data from the executed SQL statement.
The fetch
function converts SQLite data types to MATLAB data types and represents NULL values accordingly.
SQLite Data Type | MATLAB Data Type | MATLAB Null Value Representation |
---|---|---|
| double | double(NaN) |
| string | <missing> |
| string | <missing> |
|
| 0 x 1 |
| int64 | Not available |
Version History
Introduced in R2016aR2023a: 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.
R2022a: fetch
function returns table
In prior releases, the fetch
function returned the
results
output argument as a cell array. In R2022a, the
fetch
function returns the results
output argument
as a table. Use the table2cell
function to convert the data type
back to a cell array, or adjust your code to accept the new data type.
See Also
Objects
Functions
Topics
External Websites
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)