MATLAB Answers

How do I pull data from an SQLite daatbase into Matlab workspace

74 views (last 30 days)
Charles
Charles on 4 Sep 2017
Edited: Christian Hager on 25 Feb 2021 at 20:58
Hi I have somehow managed to put together some python code to retrieve data in an SQL lite database, I know which to pull that same data from the SQLite data base into the Matlab workspace. Do I merely connect to the database using 'conn' and then use 'fetch'? Is there another means by which I can pull the data into a cell array structure?

  0 Comments

Sign in to comment.

Answers (1)

Kojiro Saito
Kojiro Saito on 5 Sep 2017
The simple way is using sqlite and fetch.
conn = sqlite('sqlite_db.db');
sqlquery = 'SELECT * FROM someTable';
extracted_data = fetch(conn, sqlquery);
close(conn);
For detail, please see this link and this link.

  4 Comments

Show 1 older comment
Kojiro Saito
Kojiro Saito on 5 Sep 2017
To see the fields of the table, you need to change the query. For example, if you want to see the fields of table "inventoryTable",
conn = sqlite('sqlite_db.db');
sqlquery = 'SELECT sql FROM sqlite_master WHERE tbl_name = "inventoryTable" AND type = "table"';
extracted_data = fetch(conn, sqlquery)
These commands will show the fields.
Or alternatively, the following will list the all tables of sqlite_db.db file.
conn = sqlite('sqlite_db.db');
sqlquery = 'SELECT * FROM sqlite_master WHERE type="table"';
extracted_data = fetch(conn, sqlquery)
Maurilio Matracia
Maurilio Matracia on 7 Jan 2021
That is an interesting issue, can you please help me to fix this? I've never used sqlite files so I guess I am missing something basic.
Thank you in advance
Christian Hager
Christian Hager on 25 Feb 2021 at 20:57
It seems your database is in a different location than your Matlab active directory. Below how I use it to get all table names and column names for each table.
%%%%% CONNECT %%%%%%%%%
dbfile = fullfile(path,'fj.sqlite');
conn = sqlite(dbfile);
%%%%% TABLES & COLUMNS %%%%%
query = 'SELECT * FROM sqlite_master WHERE type = "table"';
TableSet = fetch(conn,query);
DBA_Tables = TableSet(:,2:3); % col 2 and 3 both have table names
for i = 1:size(DBA_Tables,1)
query = ['SELECT sql FROM sqlite_master WHERE tbl_name = ','''',DBA_Tables{i,1},'''',' AND type = "table"'];
cols = fetch(conn,Query);
cols = regexp(cols,'(?<=")\w+(?=")','match');
DBA_Tables{i,2} = unique(cols{1})'; % replace table name cell with column names for each table
end

Sign in to comment.

Community Treasure Hunt

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

Start Hunting!