R/insert_sql.R

#' Bulk insert
#'
#' Bulk insert to database
#' @param df data frame to be inserted into database
#' @param table_name name of table within connection
#' @param loop specifies if each row will be inserted seperately. If FALSE all values will be inserted at once. Default = T
#' @examples
#' oddsandsods:::sqlCreateTable(iris, "iris")
#' iris2 <- iris[sample(1:nrow(iris),30000, replace=T), ]
#'
#' sqlInsert(iris2,
#'           table_name = "iris",
#'           loop = F)
#' SQLInsertSplit(iris2,
#'                table_name = "iris",
#'                chunk_size = 10000)
#'
#' @export
sqlInsert <- function(df, table_name,loop=F, ignore=FALSE){
  # df = iris[1:5,]; df[1,1] <- NA; df[4,5] <- NA; df[2,2] <- "" ;df$id = 1:nrow(df)
  colnames(df) %<>% gsub("[[:punct:]]","_",x=.)

  colClasses <- lapply(df,class)
  FIELDS <- names(colClasses) %>% paste(collapse=", ")

  if(ignore){
    query <- sprintf("INSERT IGNORE INTO %1$s (%2$s) VALUES VALS", table_name, FIELDS)
  } else {
    query <- sprintf("INSERT INTO %1$s (%2$s) VALUES VALS", table_name, FIELDS)
  }

  for(i in 1:ncol(df)){
    if(any(colClasses[[i]] %in% c('character','factor','Date','POSIXct','POSIXt','POSIXlt')))
      df[[i]] <-
        df[[i]] %>% gsub("\'","",x=.) %>% oddsandsods::quoted() %>% gsub("\'NA\'|\'\'","NULL",x=.)

    if(any(colClasses[[i]] %in% c('numeric','integer','logical')))
      if(any(!is.finite(df[[i]])))
        df[[i]] %<>% as.character %>% car:::recode("NA='NULL'")
  }

  VALS <- apply(df, 1, function(x) paste("(",paste(x,collapse=","),")"))

  if(!loop) VALS %<>% paste(collapse = ",")

  for(i in 1:length(VALS))
    dbSendQuery(con,gsub("VALS",VALS[i], query))

  dbClearResult(dbListResults(con)[[1]])
}

#' Bulk insert
#'
#' Bulk insert to database
#' @param df data frame to be inserted into database
#' @param table_name name of table within connection
#' @param loop specifies if each row will be inserted seperately. If FALSE all values will be inserted at once. Default = T
#' @examples
#' sqlCreateTable(iris)
#' iris2 <- iris[sample(1:nrow(iris),30000, replace=T), ]
#'
#' sqlInsert(iris2,
#'           table_name = "iris",
#'           loop = F)
#' SQLInsertSplit(iris2,
#'                table_name = "iris",
#'                chunk_size = 10000)
#'
#' @export
SQLInsertSplit <- function(data, table, chunk_size = 10000, ignore = FALSE){
  idx_1 <- seq(1, nrow(data), chunk_size)
  idx_2 <- c( idx_1[-1], nrow(data) )

  for(i in 1:length(idx_1))
    sqlInsert( data[idx_1[i]:idx_2[i],] , table, ignore=ignore )
}
elo2zero/oddsandsods documentation built on May 16, 2019, 3:04 a.m.