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
    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 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.