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 all rows of data from a database table in an SQLite database file into MATLAB®. 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 the SQLite connection conn to the existing SQLite database file tutorial.db. The database file contains the table productTable. The SQLite connection is an sqlite object.

dbfile = 'tutorial.db';
conn = sqlite(dbfile);

Import all the data from productTable. The results output argument contains the imported data as a table.

sqlquery = 'SELECT * FROM productTable';
results = fetch(conn,sqlquery)

results=15×5 table
    productNumber    stockNumber    supplierNumber    unitCost    productDescription
    _____________    ___________    ______________    ________    __________________

          9            125970            1003            13       "Victorian Doll"  
          8            212569            1001             5       "Train Set"       
          7            389123            1007            16       "Engine Kit"      
          2            400314            1002             9       "Painting Set"    
          4            400339            1008            21       "Space Cruiser"   
          1            400345            1001            14       "Building Blocks" 
          5            400455            1005             3       "Tin Soldier"     
          6            400876            1004             8       "Sail Boat"       
          3            400999            1009            17       "Slinky"          
         10            888652            1006            24       "Teddy Bear"      
         11            408143            1004            11       "Convertible"     
         12            210456            1010            22       "Hugsy"           
         13            470816            1012            16       "Pancakes"        
         14            510099            1011            19       "Shawl"           
         15            899752            1011            20       "Snacks"          

Determine the highest unit cost of the products. Access the variable in the table for the unit cost data, and then find the maximum cost.

max(results.unitCost)

ans = int64
    24

Now, import the data using a row filter. The filter condition is that unitCost must be less than 15.

rf = rowfilter("unitCost");
rf = rf.unitCost < 15;
results = fetch(conn,sqlquery,"RowFilter",rf)

results=7×5 table
    productNumber    stockNumber    supplierNumber    unitCost    productDescription
    _____________    ___________    ______________    ________    __________________

          9            125970            1003            13       "Victorian Doll"  
          8            212569            1001             5       "Train Set"       
          2            400314            1002             9       "Painting Set"    
          1            400345            1001            14       "Building Blocks" 
          5            400455            1005             3       "Tin Soldier"     
          6            400876            1004             8       "Sail Boat"       
         11            408143            1004            11       "Convertible"     

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 into MATLAB from a database table in an SQLite database file. Then, determine the highest unit cost among products in the table.

Create the SQLite connection conn to the existing SQLite database file tutorial.db. The database file contains the table productTable. The SQLite connection is an sqlite object.

dbfile = "tutorial.db";
conn = sqlite(dbfile);

Import five rows of data from productTable by using the MaxRows name-value argument. results contains five rows of imported data as a table.

sqlquery = "SELECT * FROM productTable";
results = fetch(conn,sqlquery,MaxRows=5)

results=5×5 table
    productNumber    stockNumber    supplierNumber    unitCost    productDescription
    _____________    ___________    ______________    ________    __________________

          9            125970            1003            13        "Victorian Doll" 
          8            212569            1001             5        "Train Set"      
          7            389123            1007            16        "Engine Kit"     
          2            400314            1002             9        "Painting Set"   
          4            400339            1008            21        "Space Cruiser"  

Determine the highest unit cost for the limited number of products. Access the variable in the table for the unit cost data, and then find the maximum cost.

data = results.unitCost;
max(data)

ans = int64
    21

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.