Things I tend to forget

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)