In schardtbc/DBIExt: Extentsion for DBI; new Generics

knitr::opts_chunk$set(
  collapse = TRUE,
  comment = "#>"
  )

Connection to local test database

library(DBI)
library(DBIExt)

config <- list(
    HOST = "localhost",
    DATABASE = "mktdata000",
    USER = "test",
    PASSWORD = "test");

#' get the main database connection
getDBConnection <- function(){
  connection <- DBI::dbConnect(RMariaDB::MariaDB(), host = config[["HOST"]],
                               user = config[["USER"]], password = config[["PASSWORD"]], dbname = config[["DATABASE"]])
}

#' close the database connection
closeDBConnection <- function(connection) {
  DBI::dbDisconnect(connection)
}

db <- getDBConnection();
db

Setup a DBMS table

cid <- 1:26;
cn  <- 100:125;
clc  <- letters[cid];


target.df <- tibble::as_tibble(list(id = cid, cn = cn, cc = clc));
DBI::dbCreateTable(db,"T123",target.df);
dbAddPrimaryKey(db,"T123",primary_key = c("id"))
DBI::dbAppendTable(db,"T123",target.df)

original <- dbReadTable(db, "T123")
original

Setup a data.frame for the update data

cuc  <- LETTERS[cid];
update.df <- tibble::as_tibble(list(id = cid, cc = cuc));
update.df <- dplyr::group_by(update.df,id)

update.df

Perform the Update In The DBMS

res <- dbUpdateTable(db,"T123",update.df);
print(glue::glue("number of records modified in DBMS {res}"))

updated <- dbReadTable(db,"T123");
closeDBConnection(db);

updated