arikuncoro
October 3, 2016
When you use R for data analysis, sometimes you have to import data from a Database (e.g. SQL), instead of just import data by reading the csv, excel, or mat file. To do this, we have to do several steps. Good thing Datacamp course provide us step-by-step guidance.
There are 5 steps you need to do if you did not install RMySQL package yet. If you have installed the package, you could skip step 1. In step 2, you need to establish connection with database with dbconnect() function. Then, in step 3 you can list the database tables using dbListTables(), in which three tables are available: users, tweats, and comments. In Step 4, you can import the table and assign into a data frame variable. Last but not least, if you have finish importing data, the polite way must be performed is disconnecting the database using dbDisconnect() function.
You can find the R-script described as follow:
#Step 1, install the RMySQL package (only if you did not install the package)
install.packages("RMySQL")
#Step 2, Establish a connection
library(DBI)
con <- dbConnect(RMySQL::MySQL(),
dbname = "tweater",
host = "courses.csrrinzqubik.us-east-1.rds.amazonaws.com",
port = 3306,
user = "student",
password = "datacamp")
#Step 3, List the database tables
tables <- dbListTables(con)
str(tables) #display structure of tables
#Step 4, Import data from a table
users <- dbReadTable(con,"users")
users #print users
tweats <- dbReadTable(con,"tweats")
tweats #print tweats
comments <- dbReadTable(con,"comments")
comments #print comments
#Step5, disconnect database
dbDisconnect(con)
Hope it helps! ;D
