Table SQL

A script for performing SQL CRUD operations on Matlab tables.
202 téléchargements
Mise à jour 17 juil. 2020

Afficher la licence

# 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.

Citation pour cette source

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

Compatibilité avec les versions de MATLAB
Créé avec R2018a
Compatible avec les versions R2015b à R2018b
Plateformes compatibles
Windows macOS Linux

Community Treasure Hunt

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

Start Hunting!
Version Publié le Notes de version
1.3.0

Minor fixes and improvements.

1.2.9

Improved description.

1.2.8

Minor fixes and improvements.

1.2.7

Minor improvements.

1.2.6

Changed image.

1.2.5

Changed image.

1.2.4

Changed image.

1.2.3

Changed image.

1.2.2

Changed image.

1.2.1

Changed image.

1.2.0

Improved description.

1.1.9

Minor fixes and improvements.

1.1.8

Minor fixes and improvements.

1.1.7

Minor fixes and improvements.

1.1.6

Minor fixes and improvements.

1.1.5

Minor fixes and improvements.

1.1.4

Updated details concerning compatibility & requirements.

1.1.3

Updated details concerning compatibility & requirements.

1.1.2

Minor fixes and improvements.

1.1.1

Project website.

1.1.0

Target release.

1.0.9

Improved tags.

1.0.8

Improved tags.

1.0.7

Improved tags.

1.0.6

Improved description.

1.0.5

Improved description.

1.0.4

Improved description.

1.0.3

Screenshot added.

1.0.2

Minor fixes and improvements.

1.0.1

Added details concerning compatibility & requirements.

1.0.0