13.1 Databases in R

rap_chart_artists <- readxl::read_excel("data/Rap_Charts.xlsx", sheet = "Artists")
rap_chart_singles <- readxl::read_excel("data/Rap_Charts.xlsx", sheet = "Singles")

head(rap_chart_artists)
## # A tibble: 4 x 4
##   Artist_ID First_Name Last_Name Psuedonym  
##       <dbl> <chr>      <chr>     <chr>      
## 1         4 O'Shea     Jackson   Ice Cube   
## 2         3 Marvin     Young     Young M.C. 
## 3         2 Stanley    Burrell   M.C. Hammer
## 4         1 James      Smith     LL Cool J

head(rap_chart_singles)
## # A tibble: 6 x 5
##   Artist_ID  Year Single                     Top_US_Rap_Chart Album       
##       <dbl> <dbl> <chr>                                 <dbl> <chr>       
## 1         1  1984 "\"I Need a Beat\""                      NA Non-album s…
## 2         1  1985 "\"I Can't Live Without M…               NA Radio       
## 3         1  1986 "\"Rock the Bells\""                     NA Radio       
## 4         1  1986 "\"I Can Give You More\""                NA Radio       
## 5         1  1986 "\"You'll Rock\""                        NA Radio       
## 6         1  1987 "\"I'm Bad\""                            NA Bigger and …

13.1.1 CREATE REUSABLE CONNECTION TO DATABASE

# by default this will open up your own database (e.g., aschroed)
my_db_con <- sdalr::con_db(pass = sdalr::get_my_password())

13.1.2 WRITE DATA TO DATABASE TABLES

DBI::dbWriteTable(my_db_con, "rap_chart_artists", rap_chart_artists, row.names = FALSE)
DBI::dbWriteTable(my_db_con, "rap_chart_singles", rap_chart_singles, row.names = FALSE)