[R] How to get a table of MySQL database as a matrix variable in R

[Sean Davis]

On Feb 1, 2005, at 5:57 AM, Willie Y. CHEN wrote:

> It seems that the dbReadTable() method provided by RMySQL could not
> get rid of the headers, neither the index column... So
>

You can use dbSendQuery and fetch to get your results without the index 
column.  If you have a table with columns called col1, col2, col3, and 
indexcol, you could do something like:

drv <- dbDriver("MySQL")
con <- dbConnect(drv, "usr", "password", "dbname")
res <- dbSendQuery(con, "SELECT col1,col2,col3 from mytable")
data <- fetch(res, n = -1)

data will now be a dataframe with columns named col1,col2,and col3.  If 
you want it as a matrix, you can simply do:

my.matrix <- as.matrix(data)

Alternatively, if you use dbReadTable, your result will always be a 
dataframe.  If you want a matrix and your index column is in the first 
column of the dataframe, you can do:

my.matrix <- as.matrix(data[,-1])


> 1. Can I use read.table() method to get a table of MySQL database?
>> From the help document the argument of 'file' of read.table() method
> could be a connection...

No, you cannot use read.table to read from a MySQL database.  You could 
dump the table to disk as a tab-delimited text file and then read it, 
but that defeats the purpose of using RDBMS for data storage and 
retrieval

>
> 2. How can I obtain a matrix from the database table contains all
> elements except the headers and the index column?
>

See above for coercing a dataframe to a matrix.

> Thanks a lot.
>
> Regards,
>
> Willie
>
> ______________________________________________
> R-help at stat.math.ethz.ch mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide! 
> http://www.R-project.org/posting-guide.html