R and SQL: together again
The people of Frasier are at it again. They’ve watched all kinds of remakes and sequels in 2017. Now they’re here to report their ratings (Sorry, no long review. It’s only numerical.)
The package RMySQL appears to be deprecated and was replaced by RMariaDB
========================================================================
library("RMariaDB")
library(readr)
#library(RMySQL)
passw<-read_file("SQLpassword.txt")
MyConnection <- dbConnect(RMariaDB::MariaDB(),user='dan2', password=passw, dbname='movies', host='localhost')
We open with a query of all of the movies watched in 2017.
options(width = 190)
res <- dbSendQuery(MyConnection, "select *from movies;")
dbFetch(res)
## ID title short_title
## 1 1 Jurassic World: Fallen Kingdom Dinos again
## 2 2 Mary Poppins Returns Mary Poppins
## 3 3 Jumanji: Welcome to the Jungle Jumanji again
## 4 4 Kong: Skull Island King Kong again
## 5 5 Pirates of the Caribbean: Dead Men Tell No Tales Depp again
## 6 6 Smurfs: The Lost Village Blue again
## 7 7 Tyler Perrys Boo 2! A Madea Halloween Madea again
Now, we would like to know what each person thought of the movies.
res2 <- dbSendQuery(MyConnection, "select
rev.Name,
rev.review,
mov.short_title,
mov.title
from Reviewers as rev
left join Movies as mov
on rev.ID = mov.ID;")
ourdataFrame<-dbFetch(res2)
ourdataFrame
## Name review short_title title
## 1 Daphne Moon 4 Dinos again Jurassic World: Fallen Kingdom
## 2 Daphne Moon 5 Mary Poppins Mary Poppins Returns
## 3 Daphne Moon 3 Jumanji again Jumanji: Welcome to the Jungle
## 4 Daphne Moon 2 King Kong again Kong: Skull Island
## 5 Daphne Moon 1 Depp again Pirates of the Caribbean: Dead Men Tell No Tales
## 6 Daphne Moon 3 Blue again Smurfs: The Lost Village
## 7 Daphne Moon 5 Madea again Tyler Perrys Boo 2! A Madea Halloween
## 8 Martin Crane 4 Dinos again Jurassic World: Fallen Kingdom
## 9 Martin Crane 1 Mary Poppins Mary Poppins Returns
## 10 Martin Crane 3 Jumanji again Jumanji: Welcome to the Jungle
## 11 Martin Crane 5 King Kong again Kong: Skull Island
## 12 Martin Crane 5 Depp again Pirates of the Caribbean: Dead Men Tell No Tales
## 13 Martin Crane 1 Blue again Smurfs: The Lost Village
## 14 Martin Crane 2 Madea again Tyler Perrys Boo 2! A Madea Halloween
## 15 Gil Chesterton 1 Dinos again Jurassic World: Fallen Kingdom
## 16 Gil Chesterton 2 Mary Poppins Mary Poppins Returns
## 17 Gil Chesterton 1 Jumanji again Jumanji: Welcome to the Jungle
## 18 Gil Chesterton 1 King Kong again Kong: Skull Island
## 19 Gil Chesterton 1 Depp again Pirates of the Caribbean: Dead Men Tell No Tales
## 20 Gil Chesterton 1 Blue again Smurfs: The Lost Village
## 21 Gil Chesterton 1 Madea again Tyler Perrys Boo 2! A Madea Halloween
## 22 Chopper Dave 5 Dinos again Jurassic World: Fallen Kingdom
## 23 Chopper Dave 3 Mary Poppins Mary Poppins Returns
## 24 Chopper Dave 5 Jumanji again Jumanji: Welcome to the Jungle
## 25 Chopper Dave 5 King Kong again Kong: Skull Island
## 26 Chopper Dave 5 Depp again Pirates of the Caribbean: Dead Men Tell No Tales
## 27 Chopper Dave 2 Blue again Smurfs: The Lost Village
## 28 Chopper Dave 4 Madea again Tyler Perrys Boo 2! A Madea Halloween
## 29 Bulldog Briscoe 5 Dinos again Jurassic World: Fallen Kingdom
## 30 Bulldog Briscoe 1 Mary Poppins Mary Poppins Returns
## 31 Bulldog Briscoe 2 Jumanji again Jumanji: Welcome to the Jungle
## 32 Bulldog Briscoe 5 King Kong again Kong: Skull Island
## 33 Bulldog Briscoe 5 Depp again Pirates of the Caribbean: Dead Men Tell No Tales
## 34 Bulldog Briscoe 2 Blue again Smurfs: The Lost Village
## 35 Bulldog Briscoe 4 Madea again Tyler Perrys Boo 2! A Madea Halloween
What were the most liked movies?
res3 <- dbSendQuery(MyConnection, "select
rev.Name,
rev.review,
mov.short_title,
mov.title
from Reviewers as rev
left join Movies as mov
on rev.ID = mov.ID
where rev.review = 5;")
dbFetch(res3)
## Name review short_title title
## 1 Daphne Moon 5 Mary Poppins Mary Poppins Returns
## 2 Daphne Moon 5 Madea again Tyler Perrys Boo 2! A Madea Halloween
## 3 Martin Crane 5 King Kong again Kong: Skull Island
## 4 Martin Crane 5 Depp again Pirates of the Caribbean: Dead Men Tell No Tales
## 5 Chopper Dave 5 Dinos again Jurassic World: Fallen Kingdom
## 6 Chopper Dave 5 Jumanji again Jumanji: Welcome to the Jungle
## 7 Chopper Dave 5 King Kong again Kong: Skull Island
## 8 Chopper Dave 5 Depp again Pirates of the Caribbean: Dead Men Tell No Tales
## 9 Bulldog Briscoe 5 Dinos again Jurassic World: Fallen Kingdom
## 10 Bulldog Briscoe 5 King Kong again Kong: Skull Island
## 11 Bulldog Briscoe 5 Depp again Pirates of the Caribbean: Dead Men Tell No Tales
dbDisconnect(MyConnection)