#install.packages("RMySQL")
library(RMySQL)## Warning: package 'RMySQL' was built under R version 3.2.5## Loading required package: DBI## Warning: package 'DBI' was built under R version 3.2.5#install.packages("sqldf")
library(sqldf)## Warning: package 'sqldf' was built under R version 3.2.5## Loading required package: gsubfn## Warning: package 'gsubfn' was built under R version 3.2.5## Loading required package: proto## Warning: package 'proto' was built under R version 3.2.5## Loading required package: RSQLite## Warning: package 'RSQLite' was built under R version 3.2.5##
## Attaching package: 'RSQLite'## The following object is masked from 'package:RMySQL':
##
## isIdCurrent## sqldf will default to using MySQL#install.packages("DT")
library("DT")## Warning: package 'DT' was built under R version 3.2.5#install.packages("knitr")
library("knitr")## Warning: package 'knitr' was built under R version 3.2.5#install.packages("reshape2")
library("reshape2")## Warning: package 'reshape2' was built under R version 3.2.5#install.packages("tidyr")
library("tidyr")## Warning: package 'tidyr' was built under R version 3.2.5##
## Attaching package: 'tidyr'## The following object is masked from 'package:reshape2':
##
## smithslibrary(RMySQL)
mydb1 = dbConnect(MySQL(), user='root', password='root', dbname='movies', host='localhost')–Seting up the Connection to MySql Database……
# creating a database using RMySQL in R
# dbSendQuery(mydb, "CREATE DATABASE movies_new;")
# dbSendQuery(mydb, "USE movies_new")
# reconnecting to database we just created
mydb = dbConnect(MySQL(), user='root', password='root', host='localhost', dbname="movies_new")#Drop Table if exists
dbSendQuery(mydb, "drop table if exists movies_master")## <MySQLResult:188521288,1,0># Create new table
dbSendQuery(mydb, "CREATE TABLE `movies_master` (
`MovieID` int(11) NOT NULL,
`Name` varchar(100) DEFAULT NULL,
`Description` varchar(2000) DEFAULT NULL,
`Year` int(11) DEFAULT NULL,
`Genere` varchar(450) DEFAULT NULL,
`Directors` varchar(1000) DEFAULT NULL,
`Producer` varchar(1000) DEFAULT NULL,
PRIMARY KEY (`MovieID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;")## <MySQLResult:119556744,1,1>movies_master_df <- read.csv2("E:/0_MSC/Modules/607- Data Acquisition and Management/Asignments/W2/movies_master.csv", header=FALSE)
movies_master_df## V1
## 1 MovieID,Name,Description,Year,Genere,Directors,Producer
## 2 1,Arrival,When twelve mysterious spacecraft appear around the world,2016,Drama, Mystery & Suspense, Science Fiction & Fantasy,Denis Villeneuve,Shawn Levy
## 3 2,The Jungle Book,linguistics professor Louise Banks is tasked with interpreting the language of the apparent alien visitors.,2016,Drama, Mystery & Suspense, Science Fiction & Fantasy, Adventure,NULL
## 4 3,Eye in the Sky,A fantasy adventure musical film,2016,Drama, Mystery & Suspense,Gavin Hood,Guy Hibbert
## 5 4,Inside Out,Col. Katherine Powell, a military officer in command of an operation to capture terrorists in Kenya, sees her mission escalate when a girl enters the kill zone triggering an international dispute over the implications of modern warfare,2015, Animation, Adventure, Comedy,Kids,,Pete Docter,Ronnie Del Carmen
## 6 5,test,test,2017,test,test,test#movies_master_df <- read.csv2("https://github.com/mathsanu/CUNY_MSDA/blob/master/DATA607/W2/movies_mas#ter.csv", header=FALSE)
#movies_master_df#Inserting Data into table [movies_master]
dbWriteTable(mydb, "movies_masterf",movies_master_df,append=TRUE ) ## [1] TRUEmovies_master_rs = dbSendQuery(mydb1, "SELECT * FROM movies_master")
movies_master_df = fetch(movies_master_rs, n=-1)
print(movies_master_df)## MovieID Name
## 1 1 Arrival
## 2 2 The Jungle Book
## 3 3 Eye in the Sky
## 4 4 Inside Out
## 5 5 test
## Description
## 1 When twelve mysterious spacecraft appear around the world
## 2 linguistics professor Louise Banks is tasked with interpreting the language of the apparent alien visitors.
## 3 A fantasy adventure musical film
## 4 Col. Katherine Powell, a military officer in command of an operation to capture terrorists in Kenya, sees her mission escalate when a girl enters the kill zone triggering an international dispute over the implications of modern warfare
## 5 test
## Year Genere
## 1 2016 Drama, Mystery & Suspense, Science Fiction & Fantasy
## 2 2016 Drama, Mystery & Suspense, Science Fiction & Fantasy
## 3 2016 Drama, Mystery & Suspense
## 4 2015 Animation, Adventure, Comedy,Kids,
## 5 2017 test
## Directors Producer
## 1 Denis Villeneuve Shawn Levy
## 2 Adventure <NA>
## 3 Gavin Hood Guy Hibbert
## 4 Pete Docter Ronnie Del Carmen
## 5 test testdbSendQuery(mydb, "drop table if exists rating_master")## <MySQLResult:263175040,1,4># Create new table
dbSendQuery(mydb, "CREATE TABLE `rating_master` (
`Rating_ID` int(11) NOT NULL,
`Rating_Name` varchar(45) DEFAULT NULL,
PRIMARY KEY (`Rating_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;")## <MySQLResult:-1,1,5>Inserting Data into table [rating_master]
rating_master_df <- read.csv2("E:/0_MSC/Modules/607- Data Acquisition and Management/Asignments/W2/rating_master.csv", header=FALSE)
rating_master_df## V1
## 1 Rating_ID,Rating_Name
## 2 1,Awful
## 3 2,Bad
## 4 3,Average
## 5 4,Good
## 6 5,Amazing#rating_master_df <- read.csv2("https://github.com/mathsanu/CUNY_MSDA/blob/master/DATA607/W2/rating_master.csv", header=FALSE)
#rating_master_df#Inserting Data rating_master
dbWriteTable(mydb, "raating_master",rating_master_df,append=TRUE ) ## [1] TRUErating_master_rs = dbSendQuery(mydb1, "SELECT * FROM rating_master")
rating_master_df = fetch(rating_master_rs, n=-1)
print(rating_master_df)## Rating_ID Rating_Name
## 1 1 Awful
## 2 2 Bad
## 3 3 Average
## 4 4 Good
## 5 5 Amazing#Drop Table if exists
dbSendQuery(mydb, "drop table if exists cineast_master")## <MySQLResult:1,1,8># Create new table
dbSendQuery(mydb, "CREATE TABLE `cineast_master` (
`Ceneast_ID` int(11) NOT NULL,
`Name` varchar(45) DEFAULT NULL,
`Year_of_Birth` int(11) DEFAULT NULL,
PRIMARY KEY (`Ceneast_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;")## <MySQLResult:1181576,1,9>Reading from .csv
cineast_master_df <- read.csv2("E:/0_MSC/Modules/607- Data Acquisition and Management/Asignments/W2/cineast_master.csv", header=FALSE)
cineast_master_df## V1
## 1 Ceneast_ID,Name,Year_of_Birth
## 2 1,Inoka Liyanage,1973
## 3 2,Lochitha Thambeliyagoda,2007
## 4 3,Geoff Tang,1980
## 5 4,Maria Bloome,1978
## 6 5,Lili Change,1985#cineast_master_df <- read.csv2("https://github.com/mathsanu/CUNY_MSDA/blob/master/DATA607/W2/cineast_master.csv", header=FALSE)
#cineast_master_df#Inserting Data to cineast_master
dbWriteTable(mydb, "cineast_mastar",cineast_master_df,append=TRUE ) ## [1] TRUErating_master_rs = dbSendQuery(mydb1, "SELECT * FROM cineast_master")
rating_master_df = fetch(rating_master_rs, n=-1)
print(rating_master_df)## Ceneast_ID Name Year_of_Birth
## 1 1 Inoka Liyanage 1973
## 2 2 Lochitha Thambeliyagoda 2007
## 3 3 Geoff Tang 1980
## 4 4 Maria Bloome 1978
## 5 5 Lili Change 1985dbSendQuery(mydb, "drop table if exists movie_ratings")## <MySQLResult:3795920,1,12># Create new table
dbSendQuery(mydb, "CREATE TABLE `movie_ratings` (
`RecID` int(11) NOT NULL,
`Movie_ID` int(11) DEFAULT NULL,
`Rating_ID` int(11) DEFAULT NULL,
`Cineast_ID` int(11) DEFAULT NULL,
`Date_Added` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`RecID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;")## <MySQLResult:-1,1,13>movie_ratings_df <- read.csv2("E:/0_MSC/Modules/607- Data Acquisition and Management/Asignments/W2/movie_ratings.csv", header=FALSE)
movie_ratings_df## V1
## 1 RecID,Movie_ID,Rating_ID,Cineast_ID,Date_Added
## 2 1,1,1,1,2017-02-12 11:52:04
## 3 2,1,4,1,2017-02-12 11:52:04
## 4 3,1,5,3,2017-02-12 11:52:04
## 5 4,1,5,4,2017-02-12 11:52:04
## 6 5,1,3,5,2017-02-12 11:52:04
## 7 6,2,5,2,2017-02-12 11:52:04
## 8 7,2,4,5,2017-02-12 11:52:04
## 9 8,3,2,1,2017-02-12 11:52:04
## 10 9,3,1,1,2017-02-12 11:52:04#movie_ratings_df <- read.csv2("https://github.com/mathsanu/CUNY_MSDA/blob/master/DATA607/W2/movie_ratings.csv", header=FALSE)
#movie_ratings_dfdbWriteTable(mydb, "movie_retings",movie_ratings_df,append=TRUE ) ## [1] TRUEmovie_ratings_rs = dbSendQuery(mydb1, "SELECT * FROM movie_ratings")
movie_ratings_df = fetch(movie_ratings_rs, n=-1)
print(movie_ratings_df)## RecID Movie_ID Rating_ID Cineast_ID Date_Added
## 1 1 1 1 1 2017-02-12 11:52:04
## 2 2 1 4 2 2017-02-12 11:52:04
## 3 3 1 5 3 2017-02-12 11:52:04
## 4 4 1 5 4 2017-02-12 11:52:04
## 5 5 1 3 5 2017-02-12 11:52:04
## 6 6 2 5 2 2017-02-12 11:52:04
## 7 7 2 4 5 2017-02-12 11:52:04
## 8 8 3 2 4 2017-02-12 11:52:04
## 9 9 3 1 1 2017-02-12 11:52:04– Final results of the survey
final_rs = dbSendQuery(mydb1, "SELECT c.Name,c.Year,b.Ceneast_ID,b.Name,b.Year_of_Birth,d.Rating_Name FROM movie_ratings A
join cineast_master B on B.Ceneast_ID= A.Cineast_ID
JOIN movies_master c on c.MovieID = Movie_ID
join rating_master D on D.Rating_ID = A.Rating_ID
")
final_df = fetch(final_rs, n=-1)
print(final_df)## Name Year Ceneast_ID Name Year_of_Birth
## 1 Arrival 2016 1 Inoka Liyanage 1973
## 2 Arrival 2016 2 Lochitha Thambeliyagoda 2007
## 3 Arrival 2016 3 Geoff Tang 1980
## 4 Arrival 2016 4 Maria Bloome 1978
## 5 Arrival 2016 5 Lili Change 1985
## 6 The Jungle Book 2016 2 Lochitha Thambeliyagoda 2007
## 7 The Jungle Book 2016 5 Lili Change 1985
## 8 Eye in the Sky 2016 4 Maria Bloome 1978
## 9 Eye in the Sky 2016 1 Inoka Liyanage 1973
## Rating_Name
## 1 Awful
## 2 Good
## 3 Amazing
## 4 Amazing
## 5 Average
## 6 Amazing
## 7 Good
## 8 Bad
## 9 Awful