Main Content

fetch

Import data into MATLAB workspace using SQLite connection

Description

example

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.

example

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

collapse all

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)

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

collapse all

SQLite database connection, specified as an sqlite object created using the sqlite function.

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.

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

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

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

collapse all

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 TypeMATLAB Data TypeMATLAB Null Value Representation
  • REAL

  • DOUBLE

  • FLOAT

  • NUMERIC

  • INT

  • TINYINT

  • SMALLINT

  • MEDIUMINT

  • BIGINT

doubledouble(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

expand all