Reading a MySQL table into R
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