SQLite Database: A Convenient Alternative to MySQL in MATLAB
In our previous discussion, we explored the use of MySQL databases in MATLAB. However, setting up and configuring a MySQL database can be a cumbersome process, and loading the corresponding Java documentation in MATLAB can be challenging. In this article, we will introduce SQLite, a lightweight and easy-to-use database that can be used as an alternative to MySQL in MATLAB.
Avoiding Old Data
To avoid loading old data, we can clear the database by deleting the existing file. The following code snippet demonstrates how to delete the database file ‘demo.db’ if it exists:
if exist('demo.db', 'file')
delete('demo.db');
end
catch
error('Unable to delete database');
end
Creating a Database
To create a new SQLite database, we can use the sqlite3 function in MATLAB. The following code snippet creates a new database file ‘demo.db’ and creates a table ‘test’ with three columns: ‘some_text’, ‘some_int’, and ‘some_real’:
sqlite3('demo.db', ...
['CREATE TABLE test (' ...
'some_text TEXT, some_int INT, some_real REAL);']);
Inserting Data
To insert data into the ‘test’ table, we can use the sqlite3 function with the INSERT INTO statement. The following code snippet inserts three sets of data into the ‘test’ table:
data = struct;
data(1).text = 'world';
data(1).int = 1337;
data(1).dbl = 2.71828;
data(2).text = 'demobar';
data(2).int = -4131;
data(2).dbl = 9.0;
data(3).text = 'demo-bar';
data(3).int = 404;
data(3).dbl = 2 * pi;
sqlite3('demo.db', ...
['INSERT INTO test (some_text, some_int, some_real) VALUES (' ...
'?, ?, ?);'], data);
Querying Data
To query the data in the ‘test’ table, we can use the sqlite3 function with the SELECT statement. The following code snippet selects all the data from the ‘test’ table:
x = sqlite3('demo.db', ...
'SELECT some_text, some_int, some_real AS real FROM test;');
y = sqlite3('demo.db', 'SELECT * FROM test;');
Modifying Data
To modify the data in the ‘test’ table, we can use the UPDATE statement. The following code snippet updates the value of ‘some_int’ to 2333 for the row where ‘some_text’ is ‘world’:
sqlite3('demo.db', ...
'UPDATE test SET some_int = "2333" WHERE some_text = "world";');
Deleting Data
To delete data from the ‘test’ table, we can use the DELETE statement. The following code snippet deletes the row where ‘some_text’ is ‘hello’:
sqlite3('demo.db', ...
'DELETE FROM test WHERE some_text = "hello";');
y = sqlite3('demo.db', 'SELECT * FROM test;');
Fuzzy Queries
To perform fuzzy queries, we can use the LIKE operator. The following code snippet selects all the data from the ‘test’ table where ‘some_text’ starts with ‘demo’:
y = sqlite3('demo.db', 'SELECT * FROM test WHERE some_text LIKE "demo%";');
This is a powerful feature of SQLite that allows us to search for data based on a pattern. The % wildcard can be used to match any characters, making it a versatile tool for data querying.
Conclusion
In this article, we have demonstrated how to use SQLite as an alternative to MySQL in MATLAB. We have shown how to create a database, insert data, query data, modify data, delete data, and perform fuzzy queries using SQLite. With its ease of use and powerful features, SQLite is a great choice for database operations in MATLAB.