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

[Prof Brian Ripley]

On Mon, 31 Jan 2011, Marc Schwartz wrote:

> 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

And, I should add, you need a matching 32- or 64-bit libmysqlclient
library for your version of R, which means you will need to install 
MySQL locally even to use a remote server.

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

(same as RMySQL ....  There is also an Actual Technologies driver.)

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

Or online as
http://cran.r-project.org/web/packages/RODBC/vignettes/RODBC.pdf

> 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

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