File Exchange

image thumbnail

Table SQL

version 1.3.0 (3.74 KB) by Tommaso Belluzzo
A script for performing SQL CRUD operations on Matlab tables.

2 Downloads

Updated 17 Jul 2020

View Version History

View License

# INTRODUCTION #

Basically, this script is a SQL wrapper around Matlab tables. It is especially useful for those who are new to Matlab and have the habit of "thinking in SQL", or for those who need to accomplish complex data manipulation tasks and struggle to achieve them with the built-in table manipulation functions.

# METHODOLOGY & USAGE #

The "table_sql" function accepts a single input argument, which must be a valid SQL CRUD statement (DELETE, INSERT, SELECT or UPDATE) where the table names refer to ordinary Matlab tables defined within the main workspace.

Once the query is parsed, the script sets up an in-memory instance of the SQLite database, imports the tables, performs the specified SQL statement, synchronizes the workspace tables with the database and returns the result. The content of the latter depends upon the performed operation:
> for SELECT statements, an m-by-n table representing the result set returned by the database;
> for DELETE, INSERT and UPDATE statements, an integer representing the number of affected rows.

# EXAMPLES #

1) Setup:
load('patients');
ids = (1:numel(LastName)).';
t1 = table(ids,LastName,Gender,'VariableNames',{'ID' 'LastName' 'Gender'});
t2 = table(ids,Age,Height,Weight,'VariableNames',{'ID' 'Age' 'Height' 'Weight'});

2) Selections (with and without assignment):
t3 = table_sql('SELECT A.ID, A.LastName, B.Age FROM t1 A INNER JOIN t2 B ON B.ID = A.ID');
table_sql('SELECT Age, AVG(Height) FROM t1 A INNER JOIN t2 B ON B.ID = A.ID GROUP BY Age ORDER BY Age');

3) Modifications:
ar = table_sql('DELETE FROM t1 WHERE Gender = "Male"'); t1
ar = table_sql('UPDATE t2 SET Age = Age + 1 WHERE Age >= 40'); t2

# NOTES #

> The script requires a SQLite JDBC driver, which can be downloaded from the following link: https://bitbucket.org/xerial/sqlite-jdbc/downloads/. The ".jar" file must be placed in the script folder.
> The column types of the tables being queried are normalized as follows: categoricals and strings become character arrays, logicals become 8-bit integers (bytes), integers become doubles.

Cite As

Tommaso Belluzzo (2020). Table SQL (https://www.mathworks.com/matlabcentral/fileexchange/68771-table-sql), MATLAB Central File Exchange. Retrieved .

Comments and Ratings (5)

Tommaso Belluzzo

Hi ZhuoHe, sorry for the issue.
The script has been fixed!

Zhuohe Liu

Run time error when table has 1 column:
t = table(1, 'VariableNames', "a");
table_sql('SELECT a FROM t')

Error using table_sql>table_sql_internal (line 82)
The script could not create the table "t".
[SQLITE_ERROR] SQL error or missing database (near ")": syntax error)

Error in table_sql (line 29)
out = table_sql_internal(query,query_chunks);

Arpan Badeka

Tommaso Belluzzo

Hi XuDong, honestly I really cannot answer your first question, and for what concerns the second, well I know nothing about Handoop.

xudong liu

Dear Tommaso Belluzzo
Can matlad use for bigdata?

would you write hadoop program?

yours daniel tulips liu

MATLAB Release Compatibility
Created with R2018a
Compatible with R2015b to R2018b
Platform Compatibility
Windows macOS Linux

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!