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

[Luca Meyer]

Thank You Marc, Brian and Simon,

I am indeed first going to understand if the MySQL server can be accessed from a web connection using my machine and then I will take a look at the solutions you suggested.

Cheers,
Luca

Il giorno 31/gen/2011, alle ore 23.23, Marc Schwartz ha scritto:

> 
> On Jan 31, 2011, at 4:08 PM, Prof Brian Ripley wrote:
> 
>> On Mon, 31 Jan 2011, Marc Schwartz wrote:
>> 
>>> 
>>> On Jan 31, 2011, at 1:56 PM, Prof Brian Ripley wrote:
>>> 
>>>> 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.
>>> 
>>> 
>>> Good points, which I was unaware of, as I don't use R with MySQL, although I do have it installed locally on my MacBook Pro for use with WordPress via phpMyAdmin.
>>> 
>>> 
>>>>> 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.)
>>> 
>>> 
>>> Right. I was going to point out the AT driver (http://www.actualtech.com/product_opensourcedatabases.php), but unless there are advantages to the AT driver @ $34.95 U.S., the MySQL driver is free.
>> 
>> It has a user-friendly configuration screen.  Otherwise AFAIR you need to do some command-line file manipulation.
> 
> 
> OK.  The other thought that came to mind, is that the recent versions of the AT drivers include both 32 and 64 bit with a single installation. If going back and forth between 32 and 64 bit R versions might be required, the AT driver might make for easier installation and maintenance. 
> 
> A value judgement of sorts...
> 
> Regards,
> 
> Marc
> 
> 
>> 
>>> 
>>> I did not have the free option with Oracle unfortunately...
>>> 
>>> Regards,
>>> 
>>> Marc
>>> 
>>> 
>>>>> 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
>>> 
>> 
>> -- 
>> 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
>