sqlread specific columns/variables from a MySQL table

6 次查看（过去 30 天）
Simon
Simon 2025-1-18
回答： Simon 2025-1-19
I want to import to Matlab one or a couple specific columns from a table that is stored in MySQL database. sqlread(conn, tablename) would import the whole table. I tried playing around with opts. it didn't work. Is there any way to make this work? Or should I try using sql query script? Thanks!
opts = databaseImportOptions(conn, tablename);
opts = setoptions(opts, 'SelectedVaraibleNames', {'wanted_column'}); % error message
  5 个评论
显示 3更早的评论
Simon
Simon 2025-1-19
The correct SQL query should be
SELECT wanted_column FROM DATABASE.myTable;
Matlab had not translated opts.SelectedVariableNames = wanted_column and mysqlread(....., opts) into that sql query. Instead, it translated to Select * from myTable. I guess I didn't understand how the sql input options work at all.
Simon
Simon 2025-1-19
So I ran debugging. In connection.m, I found
%Construct the query
querybuilder = database.internal.utilities.SQLQueryBuilder;
querybuilder = querybuilder.select("*").from(tablename);
%.....
querybuilder = dispatcher.dispatch(rowFilter,querybuilder,connect.DatabaseProductName);
query = strtrim(querybuilder.SQLQuery);
Obviously, the querybuilder is not affected by opts.SelectedVariableNames at all. And it seems that, according to the way query is made, it's impossible to select specific columns for sql import.

请先登录，再进行评论。

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

回答（2 个）

Simon
Simon 2025-1-19
The answer to my own questiion is fetch. sqlread always reads in the whole table.
sqlquery = 'select wanted_column from myTable';
wanted = fetch(conn, sqlquery)
Walter Roberson
Walter Roberson 2025-1-18
opts = setoptions(opts, 'SelectedVariableNames', {'wanted_column'});
You had 'SelectedVaraibleNames' instead of 'SelectedVariableNames'

类别

在 Help Center 和 File Exchange 中查找有关 Database Toolbox 的更多信息

标签

产品


版本

R2023a

Community Treasure Hunt

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

Start Hunting!

Translated by