R/Mix.SQL_Function.R

In Ehsan-F/R-Mixtape: Mixtape: Enhancing the model making process

Mix.SQL <- function(table = NULL, database = 'DS_Reporting', server = 'EC2AMAZ-J0IS0BR', query = NULL,
                    sf_lender_filter = F) {
  
  require(RODBC)
  require(tidyverse)
  
  #-- Load Connection
  conn <- odbcDriverConnect(
    connection = paste(
      'driver={SQL Server};', 
      'server=', server, ';',
      'database=', database, ';', 
      'trusted_connection=true',
      sep = ""
    )
  )
  

  #-- SELECT Query
  if (is.null(query)) {
    
    ds_sql <- sqlQuery(
      
      channel = conn,
      
      query = paste(
        'SELECT * FROM ',
        table,
        sep = ""
      )
      
    ) %>% as_tibble()
    
  } else {
    
    ds_sql <- sqlQuery(
      
      channel = conn,
      
      query = query
      
    ) %>% as_tibble()
    
  }

  #-- Close connection
  odbcClose(conn)
  
  
  #-- Change Factor variables to Character
  factor_vars <- which(sapply(ds_sql, FUN = function(x) is.factor(x))) %>% names()
  ds_sql <- ds_sql %>% 
    mutate_at(
      .vars = factor_vars,
      .funs = as.character
    )
  
  #-- Lender Filter
  if (sf_lender_filter == T) {
    ds_sql <- ds_sql %>% 
      filter(Lender == 'SalaryFinance')
  }
  
  #-- Output
  ds_sql
}



Mix.SQL.Write <- function(df = NULL, table = NULL, database = 'DS_Reporting', server = 'EC2AMAZ-J0IS0BR') {
  
  require(RODBC)
  require(tidyverse)
  
  #-- Load Connection
  conn <- odbcDriverConnect(
    connection = paste(
      'driver={SQL Server};', 
      'server=', server, ';',
      'database=', database, ';', 
      'trusted_connection=true',
      sep = ""
    )
  )
  
  #-- Write
  sqlSave(
    channel = conn,
    dat = df,
    tablename = table,
    rownames = F,
    safer = F
  )
  
  #-- Close connection
  odbcClose(conn)
  
}