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

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?

Answer 1

[Kojiro Saito]

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.

[Charles]

Ok. Thank you I will try this. If I also want to see the fields i.e table structure in my db, how do I do this?

[Kojiro Saito]

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]

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]

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