An embedded and charset-unspecified text was scrubbed... Name: not available URL: <https://stat.ethz.ch/pipermail/r-sig-mac/attachments/20110131/dd44ab8c/attachment.pl>
Reading a MySQL table into R
5 messages · Luca Meyer, Marc Schwartz, Brian Ripley +1 more
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
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
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. 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")
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
On Jan 31, 2011, at 2:14 PM, 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 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/
3. RJDBC (also on CRAN) Since OS X already has the matching Java all you need is the MySQL JDBC driver Connector/J - see ?JDBC for an example. Nonetheless I was going to interject that all the methods above assume that the database is accessible form the outside, which is usually not the case. So you should check first that the MySQL is actually running on a externally accessible port so you can connect to it remotely. If it doesn't, you can use ssh + port forwarding if you have ssh access to the machine, otherwise you're in trouble. Cheers, Simon
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 _______________________________________________ R-SIG-Mac mailing list R-SIG-Mac at r-project.org https://stat.ethz.ch/mailman/listinfo/r-sig-mac