fetch - Import data into MATLAB workspace using SQLite connection - MATLAB (original) (raw)
Import data into MATLAB workspace using SQLite connection
Syntax
Description
[results](#mw%5F4f0c7264-694e-4a4c-8fda-e3eba1ae1c7e) = fetch([conn](#mw%5Fa0c2c879-838b-40f9-9b9e-0e53733860f6%5Fsep%5Fshared-conn),[sqlquery](#mw%5F607bfdb4-5f26-401f-aa97-2d7d90a87da3)) returns all rows of data from an SQLite database file immediately after executing the SQL statement sqlquery by using the SQLite connection conn of the MATLAB® interface to SQLite.
[results](#mw%5F4f0c7264-694e-4a4c-8fda-e3eba1ae1c7e) = fetch([conn](#mw%5Fa0c2c879-838b-40f9-9b9e-0e53733860f6%5Fsep%5Fshared-conn),[sqlquery](#mw%5F607bfdb4-5f26-401f-aa97-2d7d90a87da3),[Name=Value](#namevaluepairarguments)) specifies additional options using one or more name-value arguments. For example,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.
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)
Close the SQLite connection.
Copyright 2021 The MathWorks, Inc.
Input Arguments
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 asName1=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 thefetchfunction imports data."modify"— Remove non-ASCII characters from variable names when thefetchfunction 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 |
|---|---|---|
| REALDOUBLEFLOATNUMERICINTTINYINTSMALLINTMEDIUMINTBIGINT | double | double(NaN) |
| CHARVARCHAR | string | |
| DATEDATETIME | string | |
| BLOB | N x 1 uint8 vector | 0 x 1 uint8 vector |
| BOOLEAN | int64 | Not available |
Version History
Introduced in R2016a
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.
R2022a: fetch function returns table
In prior releases, the fetch function returned theresults output argument as a cell array. In R2022a, thefetch 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.