sqlread

Import data into MATLAB from SQLite database table

Since R2022a

Syntax

data = sqlread(conn,tablename)

data = sqlread(conn,tablename,Name=Value)

Description

data = sqlread(conn,tablename) returns a table by importing data into MATLAB^® from a database table with the MATLAB interface to SQLite. Executing this function is the equivalent of writing a SELECT * FROM tablename SQL statement in ANSI SQL.

data = sqlread(conn,tablename,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 the sqlread function with a 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.

tablename = "productTable";
results = sqlread(conn,tablename)

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 unit cost data using the variable of the results table. data is a vector that contains numeric unit costs. Find the maximum unit cost.

data = results.unitCost;
max(data)

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 = sqlread(conn,tablename,"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)

Import Specific Number of Rows from Database Table

Use the sqlread function of the MATLAB® interface to SQLite to import a limited number of rows of data into MATLAB from a database table in an SQLite database file.

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 data from the table productTable. Import only three rows of data from the database table. The data table contains the product data.

tablename = "productTable";
data = sqlread(conn,tablename,MaxRows=3)

data=3×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"     

Close the SQLite connection.

close(conn)

Remove Non-ASCII Characters in Variable Names When Importing Data

Import product data from an SQLite database table into MATLAB® by using the MATLAB interface to SQLite. The table contains a variable name with a non-ASCII character. When importing data, remove non-ASCII characters from the names of all the variables.

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);

Rename the unitCost column in the database table productTable to tamaño. The column name contains a non-ASCII character.

sqlquery = "ALTER TABLE productTable RENAME COLUMN unitCost TO tamaño"; 
execute(conn,sqlquery)

Import data from the database table productTable. The sqlread function returns a MATLAB table that contains the product data. Display the first three rows of the data in the table.

tablename = "productTable";
data = sqlread(conn,tablename);
head(data,3)

    productNumber    stockNumber    supplierNumber    tamaño    productDescription
    _____________    ___________    ______________    _______________________    __________________

          9            125970            1003                   13                "Victorian Doll" 
          8            212569            1001                    5                "Train Set"      
          7            389123            1007                   16                "Engine Kit"     

The sqlread function preserves non-ASCII characters in the name of the variable by default.

Remove the non-ASCII character in the name of the variable by specifying the VariableNamingRule name-value argument. Import the data again.

data = sqlread(conn,tablename, ...
    VariableNamingRule="modify");
head(data,3)

    productNumber    stockNumber    supplierNumber    tama_o    productDescription
    _____________    ___________    ______________    ______    __________________

          9            125970            1003           13       "Victorian Doll" 
          8            212569            1001            5       "Train Set"      
          7            389123            1007           16       "Engine Kit"     

The sqlread function removes the non-ASCII character in the variable name.

Rename the tamaño column in the database table productTable back to unitCost.

sqlquery = "ALTER TABLE productTable RENAME COLUMN tamaño TO unitCost"; 
execute(conn,sqlquery)

Close the SQLite connection.

close(conn)

Input Arguments

conn — SQLite database connection

sqlite object

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

tablename — Database table name

string scalar | character vector

Database table name, specified as a string scalar or character vector denoting the name of a table in the database.

Example: "employees"

Data Types: string | char

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: data = sqlread(conn,"inventoryTable",MaxRows=5) imports five rows of data from the database table inventoryTable.

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 sqlread 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 sqlread function imports data.

• "modify" — Remove non-ASCII characters from variable names when the sqlread 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; sqlread(conn,tablename,"RowFilter",rf)

Output Arguments

data — Result data

table

Result data, returned as a table. The result data contains all rows of data from the executed SQL statement.

The sqlread 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 R2022a

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.