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

53 views (last 30 days)
Charles
Charles on 4 Sep 2017
Edited: Christian Hager on 25 Feb 2021
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?

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
Christian Hager
Christian Hager on 25 Feb 2021
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!