Slides: “Accessing Databases from R” #rstats
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.
For the past few meetings of the Greater Boston useR Group, we have been opened with an introductory “useR Vignette” talk on a topic which may be helpful for new R users. This week, I presented an overview of accessing databases from R. Several people have tweeted and blogged nice things about my talk
and have asked for the slides, so here they are, via Slideshare:
The final slide includes the code which I used to create and populate the ‘testdb’ database I used for my examples. I have duplicated it here as it’s a nice, quick example of using DBI to store an R data.frame
in a database:
First, create new database & user in MySQL:
mysql> create database testdb; mysql> grant all privileges on testdb.* to 'testuser'@'localhost' identified by 'testpass'; mysql> flush privileges;
In R, load the “mtcars” data.frame
, clean it up, and write it to a new “motortrend” table:
library(stringr) library(RMySQL) data(mtcars) # car name is data.frame's rownames. Let's split into manufacturer and model columns: mtcars$mfg = str_split_fixed(rownames(mtcars), ' ', 2)[,1] mtcars$mfg[mtcars$mfg=='Merc'] = 'Mercedes' mtcars$model = str_split_fixed(rownames(mtcars), ' ', 2)[,2] # connect to local MySQL database (host='localhost' by default) con = dbConnect("MySQL", "testdb", username="testuser", password="testpass") dbWriteTable(con, 'motortrend', mtcars) dbDisconnect(con)
![](https://i0.wp.com/stats.wordpress.com/b.gif?resize=1%2C1)
R-bloggers.com offers daily e-mail updates about R news and tutorials about learning R and many other topics. Click here if you're looking to post or find an R/data-science job.
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.