Loading Data from MySQL into R

The purpose of this assignment is to load information, originally stored in MySQL as a database, into R as a data frame. The script used to create the table in MySQL Workbench is seen below.

CREATE TABLE movierating ( MovieID varchar(45) NOT NULL, Steve decimal(2,0) DEFAULT NULL, Kelley decimal(2,0) DEFAULT NULL, SteveJr decimal(2,0) DEFAULT NULL, Luke decimal(2,0) DEFAULT NULL, Will decimal(2,0) DEFAULT NULL, PRIMARY KEY (MovieID) )

The database was connected to R using the RMySQL package. This can be recourseful in scenarios where a database is already in MySQL and the user would like to use the R interface. The dbconnect function makes it very easy to do this, however it is necesary to change the authentication to legacy.

require(RMySQL)

## Loading required package: RMySQL

## Loading required package: DBI

library(RMySQL)


mydb = dbConnect(MySQL(), 
                 user='root', 
                 password='mojo9184',
                 host='localhost',
                 dbname='homework2')

df <- dbGetQuery(mydb, "select * from movierating;")

## Warning in .local(conn, statement, ...): Decimal MySQL column 1 imported as
## numeric

## Warning in .local(conn, statement, ...): Decimal MySQL column 2 imported as
## numeric

## Warning in .local(conn, statement, ...): Decimal MySQL column 3 imported as
## numeric

## Warning in .local(conn, statement, ...): Decimal MySQL column 4 imported as
## numeric

## Warning in .local(conn, statement, ...): Decimal MySQL column 5 imported as
## numeric

on.exit(dbDisconnect(mydb))