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

2017 9 4
1 个回答
更新时间：2021 2 25
6 次查看（30 天）

0 个投票

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?

请先登录，再回答此问题。

回答（1 个）

Kojiro Saito
Kojiro Saito 2017-9-5

0 个投票

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 个评论
显示 2更早的评论 隐藏 2更早的评论

Charles
Charles 2017-9-5
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
Kojiro Saito 2017-9-5
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)
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 2021-2-25
编辑：Christian Hager 2021-2-25
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

请先登录，再进行评论。

类别

在 帮助中心 和 File Exchange 中查找有关 MATLAB Interface to SQLite 的更多信息

产品

标签

Community Treasure Hunt

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

Start Hunting!

Translated by