fetch

Import data into MATLAB workspace using SQLite connection

Syntax

results = fetch(conn,sqlquery)

results = fetch(conn,sqlquery,Name=Value)

Description

results = fetch(conn,sqlquery) 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 = fetch(conn,sqlquery,Name=Value) 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
    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 array
    "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
    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
    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 the fetch function imports data.

• "modify" — Remove non-ASCII characters from variable names when the fetch 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
REAL DOUBLE FLOAT NUMERIC INT TINYINT SMALLINT MEDIUMINT BIGINT	double	double(NaN)
CHAR VARCHAR	string	<missing>
DATE DATETIME	string	<missing>
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 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.