R help - Feb 2005 - How to get a table of MySQL database as a matrix variable in R

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

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...

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

Thanks a lot.

Regards,

Willie

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

On Tue, 1 Feb 2005, 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
>
> 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...
?connection will tell you what that is: it is nothing to do with RMySQL.
> 2. How can I obtain a matrix from the database table contains all
> elements except the headers and the index column?
We don't know what you have tried, but note that dbReadTable (and 
read.table) return data frames, not matrices, and data frames must have 
row names and (column) names.  You probably need to research the 
difference between a matrix and a data frame.

-- 
Brian D. Ripley,                  ripley at stats.ox.ac.uk
Professor of Applied Statistics,  http://www.stats.ox.ac.uk/~ripley/
University of Oxford,             Tel:  +44 1865 272861 (self)
1 South Parks Road,                     +44 1865 272866 (PA)
Oxford OX1 3TG, UK                Fax:  +44 1865 272595