[R-SIG-Mac] Reading a MySQL table into R

[Marc Schwartz]

On Jan 31, 2011, at 12:56 PM, Luca Meyer wrote:

> Hello,
> 
> I should first mention that I am not familiar with database management tools, nor with database solutions for Mac OS X.
> 
> I currently have some data sitting on a MySQL Server version 5.0.51a-24+lenny4 I access through a web site using phpMyAdmin - 2.11.5.1. That is, the database is sitting remotely and I have to access the table containing the data several times during each day.
> 
> How can I read this data into a R data.frame without having to use phpMyAdmin to download a csv file to my machine and then import it from there? Is there some SQL query I can run directly from R into MySQL?
> 
> I have been looking at RMySQL, am I on the right track? Or should I be looking at something else?
> 
> Thanks,
> Luca


Luca,

There are essentially two methods:

1. RMySQL

2. RODBC (also on CRAN), which would allow you to connect to MySQL via an ODBC driver, which is available from:

  http://www.mysql.com/downloads/connector/odbc/

Be aware that for the second option, you will need to obtain the correct 32 or 64 bit version of the ODBC driver, depending upon which version of R you are running on your Mac when you want to connect to the server.

If you elect to use RODBC, Prof. Ripley has created a very helpful vignette, which can be read after installation by using:

  vignette("RODBC")

Lastly, for detailed assistance on R and databases, there is the r-sig-db list. More info here:

  https://stat.ethz.ch/mailman/listinfo/r-sig-db

HTH,

Marc Schwartz