Load librarys needed to connect to mySQL database
library(dbConnect)## Loading required package: RMySQL## Loading required package: DBI## Loading required package: gWidgetslibrary(RMySQL)The following established a connection between the msql server and R. the dbname (database name) is set by the sql script. This connection will vary for others on seperate machines and the password and user must be set within the script appropriately. Also note, this script will work if the MySQL Server was installed with Legacy Authentification Method. Compatible with MySQL 5.x. The newer authentification method is not yet supported without other modifications to the system not within the scope of this assignment.
# user, password and hostname hidden in r script
connection = dbConnect(MySQL(),user = username, password = SQLpassword, dbname = 'database1', host = hostname, port = 3306)The following script creates an sql query that will be used to extract data from the MySQL connection that has been established in R. The table is then printed as an output.
query1 = "SELECT * from movierankings"
movie.survey<-dbGetQuery(connection,query1)
movie.survey## ID Name Movie Rank
## 1 1 Joshua Lion King 1
## 2 2 Joshua Toy Story 4 5
## 3 3 Joshua Spider Man 3
## 4 4 Joshua John Wick 3 NA
## 5 5 Joshua Avengers End Game 2
## 6 6 Joshua Aladdin 4
## 7 7 Yara Lion King 1
## 8 8 Yara Toy Story 4 3
## 9 9 Yara Spider Man NA
## 10 10 Yara John Wick 3 2
## 11 11 Yara Avengers End Game 5
## 12 12 Yara Aladdin 4
## 13 13 Mike Lion King 3
## 14 14 Mike Toy Story 4 NA
## 15 15 Mike Spider Man 2
## 16 16 Mike John Wick 3 NA
## 17 17 Mike Avengers End Game 1
## 18 18 Mike Aladdin 4
## 19 19 Xing Lion King 2
## 20 20 Xing Toy Story 4 1
## 21 21 Xing Spider Man 3
## 22 22 Xing John Wick 3 4
## 23 23 Xing Avengers End Game 5
## 24 24 Xing Aladdin 6
## 25 25 Dario Lion King 3
## 26 26 Dario Toy Story 4 5
## 27 27 Dario Spider Man 2
## 28 28 Dario John Wick 3 4
## 29 29 Dario Avengers End Game 1
## 30 30 Dario Aladdin NA