sqlread specific columns/variables from a MySQL table

2025 1 18
2 个回答
更新时间：2025 1 19
7 次查看（30 天）

0 个投票

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更早的评论 隐藏 3更早的评论

Jeff Miller
Jeff Miller 2025-1-18
What was the error message? Note there is a typo in your post--"SelectedVaraibleNames" is mis-spelled. Is it mis-spelled in your code too?
Simon
Simon 2025-1-19
The spelling in my real codes is correct. I misspelled it in this post. The error message I had was
Error using database.options.SQLImportOptions/setoptions
Expected wanted_column options to match one of these values:
'MissingRule', 'Name', 'Type', 'FillValue', 'WhitespaceRule', 'TextCaseRule'
The input, 'SelectedVariableNames', did not match any of the valid values.
Simon
Simon 2025-1-19
@Walter Roberson, @Jeff Miller Thanks for pointing out the misspelling in the codes. After I corrected that, I tried
opts = databaseImportOptions(conn, "myTable")
opts.SelectedVariableNames = {'wanted_cloumn'}
And I got the error message:
------------------------- Error Message --------------------
Error using database.relational.connection/fetch
Unable to use database import options with
'SELECT * from myTable'.
Error in database.mysql.connection/sqlreadHook (line 4)
[T,metadata] = fetch(connect,sqlquery,optsObject,'MaxRows',maxRows);
Error in database.relational.connection/sqlread (line 689)
data = sqlreadHook(connect,query,optsObject,maxRows,preservenames,isvarnamerulespecified);
----------------------------
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

1 个投票

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

0 个投票

opts = setoptions(opts, 'SelectedVariableNames', {'wanted_column'});
You had 'SelectedVaraibleNames' instead of 'SelectedVariableNames'

类别

在 帮助中心 和 File Exchange 中查找有关 Database Toolbox 的更多信息

产品

版本

R2023a

标签

提问:

Simon
Simon
2025-1-18

回答：

Simon
Simon
2025-1-19

Community Treasure Hunt

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

Start Hunting!

Translated by