sqlread
Description
specifies additional options using one or more name-value arguments. For example,
data
= sqlread(conn
,tablename
,Name=Value
)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 thesqlread
function imports data."modify"
— Remove non-ASCII characters from variable names when thesqlread
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 |
---|---|---|
| double | double(NaN) |
| string | <missing> |
| string | <missing> |
|
| 0 x 1 |
| int64 | Not available |
Version History
Introduced in R2022aR2023a: 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.
See Also
Objects
Functions
MATLAB Command
You clicked a link that corresponds to this MATLAB command:
Run the command by entering it in the MATLAB Command Window. Web browsers do not support MATLAB commands.
Select a Web Site
Choose a web site to get translated content where available and see local events and offers. Based on your location, we recommend that you select: .
You can also select a web site from the following list:
How to Get Best Site Performance
Select the China site (in Chinese or English) for best site performance. Other MathWorks country sites are not optimized for visits from your location.
Americas
- América Latina (Español)
- Canada (English)
- United States (English)
Europe
- Belgium (English)
- Denmark (English)
- Deutschland (Deutsch)
- España (Español)
- Finland (English)
- France (Français)
- Ireland (English)
- Italia (Italiano)
- Luxembourg (English)
- Netherlands (English)
- Norway (English)
- Österreich (Deutsch)
- Portugal (English)
- Sweden (English)
- Switzerland
- United Kingdom (English)