Pulling in data from MySQL database. Password is CUNYDATA in database VIDEOSQL
library(RMySQL)## Loading required package: DBIcon <- dbConnect(RMySQL::MySQL(),
dbname = "VIDEOSQL",
host = "localhost",
port = 3306,
user = "root",
password = "CUNYDATA")
table_names <- dbListTables(con)
reviews <- dbReadTable(con, "Reviews")
videos <- dbReadTable(con, "Videos")
tables <- sapply(table_names, DBI::dbReadTable, conn=con)
query1 <- dbGetQuery(con, "select * from Videos a
left join Reviews b
on a.VideoID = b.VideoID")
print(reviews)## ReviewerID ReviewerName rating
## 1 1 Cesar 5
## 2 2 George 4
## 3 3 Alex 4
## 4 4 Luciano 5
## 5 5 Luciano 1
## review
## 1 I laughed and I cried. The triump of the human spirit is clear in this film.
## 2 A bit long for the storyline, great cast
## 3 Creepy but good, that plot twist at the end!
## 4 This movie should win an oscar!
## 5 I do not get this movie and I was born in LA!
## VideoID
## 1 1
## 2 2
## 3 3
## 4 4
## 5 5print(videos)## VideoID title runningtime
## 1 1 Hidden Figures 127
## 2 2 Passengers 116
## 3 3 Split (2017) 117
## 4 4 Moonlight 110
## 5 5 La La Land 128print(query1)## VideoID title runningtime ReviewerID ReviewerName rating
## 1 1 Hidden Figures 127 1 Cesar 5
## 2 2 Passengers 116 2 George 4
## 3 3 Split (2017) 117 3 Alex 4
## 4 4 Moonlight 110 4 Luciano 5
## 5 5 La La Land 128 5 Luciano 1
## review
## 1 I laughed and I cried. The triump of the human spirit is clear in this film.
## 2 A bit long for the storyline, great cast
## 3 Creepy but good, that plot twist at the end!
## 4 This movie should win an oscar!
## 5 I do not get this movie and I was born in LA!
## VideoID
## 1 1
## 2 2
## 3 3
## 4 4
## 5 5View(query1)dbDisconnect(con)## [1] TRUE