Loading Necessary Libraries

I decided to connect to an SQL server directly for my project. The ODBC library allows for connecting to an SQL database, while the rstudioapi library allows for me to place a prompt for the password when someone tries to connect to it.

library("odbc")
library("rstudioapi")

Connecting

By selecting “new connection” in rstudio, I was able to retrieve the driver connection syntax, “odbc::odbc(), .connection_string =”Driver={MySQL ODBC 8.1 Unicode Driver};“, to form the connection with the sql server.

con <- dbConnect(odbc::odbc(), .connection_string = "Driver={MySQL ODBC 8.1 Unicode Driver};", Server = "localhost", Database = "week_2_assignment", UID = "root", PWD = rstudioapi::askForPassword("Database Password"), Port = 3306)

Load Data

I queried all of the data and stored it into a dataframe. To deal with the movies that people hadn’t watched, I simply set it to Null in SQL, which will return an NA value when loaded into an R dataframe.

df <- dbGetQuery(con, "SELECT * FROM movies")
df
##   person_id first_name last_name kill_bill forrest_gump the_godfather
## 1         1       Nick         H         4            3             4
## 2         2        Max         B         3            2             5
## 3         3      Emily         P         2            3             2
## 4         4       Ryan         D         3            2             5
## 5         5       John         H         5           NA             5
##   the_dark_knight jaws pulp_fiction
## 1               3    4            4
## 2               4    3            3
## 3               5    4            2
## 4               4    3            3
## 5               3   NA            4