examples of sql in R

sql_statements.R

sql_string_value <- function(value) {
  return(ifelse(is.null(value) | is.na(value),"''", paste0("'", gsub("'", "", value), "'")))
}

sql_timestamp_value <- function(value) {
  return(paste0("date(timestamp'", value, "')"))
}

sql_numeric_value <- function(value) {
  return(ifelse(is.null(value) | is.na(value), '0', value))
}

build_insert_into_sql_chunks <- function(dataframes, table_name, chunk_size, opts) {

  df_names = names(dataframes)
  
  begin_stmt = paste(
    "INSERT INTO", table_name,
    "(", paste0(df_names, collapse = ','), ")",
    "VALUES"
  )
  
  classes = sapply(dataframes, class)

  # print(classes)
  n_rows = nrow(dataframes)
  insert_values = c()
  insert_stmt = c()

  for(n in 1:n_rows) {

    values = c()
    for(field_name in df_names) {
      if (opts["timestamp_field"] == field_name) {
        values = c(values, sql_timestamp_value(dataframes[[field_name]][n]))
      } else if (classes[field_name] == "character") {
        values = c(values, sql_string_value(dataframes[[field_name]][n]))
      } else if (classes[field_name] == "numeric") {
        values = c(values, sql_numeric_value(dataframes[[field_name]][n]))
      } else {
        values = c(values, sql_string_value(dataframes[[field_name]][n]))
      }
    }

    insert_value = paste0(
      "(", paste(values, collapse=','), ")"
    )

    insert_values = c(insert_values, insert_value)

    if ((n %% chunk_size == 0) | n == n_rows) {
      values = paste0(insert_values, collapse=",")
      chunk = paste0(begin_stmt, " ", values)

      insert_stmt = c(insert_stmt, chunk)
      insert_values = c()
    }
  }

  return(insert_stmt)
}