SQl_R assignment

Introduction:

This assignment presents the steps and the coding process taken from connecting to mySQL server to building a movie_ratings_tbl on mySQL Workbench and assigning it a new name, i.e., Films_ratings

Loading libraries

# | label: load - libraries
library(tidyverse)

── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
✔ dplyr     1.1.2     ✔ readr     2.1.4
✔ forcats   1.0.0     ✔ stringr   1.5.0
✔ ggplot2   3.4.3     ✔ tibble    3.2.1
✔ lubridate 1.9.2     ✔ tidyr     1.3.0
✔ purrr     1.0.1     
── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
✖ dplyr::filter() masks stats::filter()
✖ dplyr::lag()    masks stats::lag()
ℹ Use the conflicted package (<http://conflicted.r-lib.org/>) to force all conflicts to become errors

library(gt)

Name of the imported SQL data set

# |label: The original data set from SQL
"Movies_ratings_tbl"

[1] "Movies_ratings_tbl"

Connecting to server

Connection parameters

# |label: connection -  parameters

server <- "localhost"
database <- "flights"
username <- "root"

Creating a connection string

# | label: connection-string
connection_string <- paste(
  "Driver={SQL Server};Server=", server, ";Database=", database,
  ";Uid=", username, ";Pwd=", "password", sep=""
)

Establishing a connection

#|label: test-connection
conn <- "odbcDriverConnect(connection_string)"

# |label: execute-query
query <- "SELECT * FROM movies"
data <- "sqlQuery(conn, query)"
print(query)

[1] "SELECT * FROM movies"

# | label: print-connections_string
connection_string <- paste(
  "Driver={MySQL Server};localhost=", server, ";flights=", database,
  ";root=", username, ";Pwd=", "password", sep=""
)
print(connection_string)

[1] "Driver={MySQL Server};localhost=localhost;flights=flights;root=root;Pwd=password"

The results

From mySQL Movies_ratings_tbl to Films_ratings in R:

1. Below is the gt version the mySQL Workbench data set:

library(gt)

# Create a data frame with the specified column names
data <- data.frame(
  "Ticket to paradise" = numeric(5),
  "Canary" = numeric(5),
  "Office race" = numeric(5),
  "Amerikatsi" = numeric(5),
  "A Hunting in Venice" = numeric(5),
  "Kompromat" = numeric(5)
)

# Add the rows with names
data$Name <- c("David", "Richard", "Kelly", "Florence", "Max")

# Reorder the columns to have 'Name' first
data <- data[, c("Name", colnames(data)[1:6])]
print(data)

      Name Ticket.to.paradise Canary Office.race Amerikatsi A.Hunting.in.Venice
1    David                  0      0           0          0                   0
2  Richard                  0      0           0          0                   0
3    Kelly                  0      0           0          0                   0
4 Florence                  0      0           0          0                   0
5      Max                  0      0           0          0                   0
  Kompromat
1         0
2         0
3         0
4         0
5         0