DATA 607 HW 2

Sarah Wigodsky

September 8, 2017

Reading a Database of Movie Reviews in from MySQL

Reading in MySQL password from a text file stored on the computer

library(readr)
sqlpass <- read_file("C:/Users/Swigo/Desktop/Sarah/sqlpassword.txt")

Connecting to MySQL

library(RMySQL)

## Loading required package: DBI

movie_db <- dbConnect(RMySQL::MySQL(), user = 'root', password = sqlpass, dbname = 'movie_rating', host = 'localhost', port = 3306)

List Tables in Movie Database

dbListTables(movie_db)

## [1] "movie"         "moviereviewer" "reviewer"

Run Querry to print movie name, reviewer name, and reviews

dbGetQuery(conn = movie_db, statement = "SELECT m.movie_name AS 'Movie Name', r.reviewer_name AS 'Reviewer', mr.rating AS 'Rating' FROM movie m
LEFT JOIN moviereviewer mr
ON m.movie_id = mr.movie_id 
LEFT JOIN reviewer r
ON mr.reviewer_id = r.reviewer_id
ORDER BY m.movie_name ASC;")

##          Movie Name Reviewer Rating
## 1  Fantastic Beasts    Sarah      3
## 2  Fantastic Beasts      Dan      3
## 3  Fantastic Beasts    Nadav      3
## 4  Fantastic Beasts     Alon      3
## 5  Fantastic Beasts    Deena      4
## 6            Fences    Sarah      5
## 7            Fences      Dan      5
## 8         Spiderman    Robin      2
## 9         Spiderman     Alon      3
## 10        Spiderman    Deena      4
## 11  The Jungle Book    David      4
## 12  The Jungle Book    Sarah      5
## 13  The Jungle Book    Deena      5
## 14      Wonderwoman    Sarah      4
## 15      Wonderwoman    Nadav      3
## 16      Wonderwoman     Alon      4
## 17      Wonderwoman    Deena      5
## 18      Wonderwoman    Robin      4
## 19         Zootopia    Sarah      5
## 20         Zootopia    Robin      5
## 21         Zootopia     Alon      4