[R-SIG-Mac] Reading a MySQL table into R
marc_schwartz at me.com
Mon Jan 31 21:25:14 CET 2011
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:
>>> 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 - 184.108.40.206. 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?
>> 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:
>> 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.
I did not have the free option with Oracle unfortunately...
>> If you elect to use RODBC, Prof. Ripley has created a very helpful vignette, which can be read after installation by using:
> Or online as
>> Lastly, for detailed assistance on R and databases, there is the r-sig-db list. More info here:
>> 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
More information about the R-SIG-Mac