SQLite Interface Class for R
I wrote an interface class to help with working with the SQLite database.
Methods include, selectQuery, updateQuery, readTable, writeTable
see code below
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 |
# sql Interface Class library(RSQLite) library(DBI) # ---------------------------------------------------------- #' class for reading from sqllite dbase #' @param databaase character for name of database to connect too #' @param query character for query to process #' sqlInterface <- setRefClass("sqlInterface", fields = list(database = "character" ,query = "character" ,sql_db = "SQLiteConnection" ,destination_table = "character" )) sqlInterface$methods( connect = function() { #' connect to sqlite database sql_db <<- dbConnect(RSQLite::SQLite(), .self$database) } ,disconnect = function() { #' close database connection dbDisconnect(sqldb) } #' connect to sqlite database run query and return results #' @return query_results data.frame with query results ,selectQuery = function() { #.self$connect sqldb <- dbConnect(RSQLite::SQLite(), .self$database) # run the query query_result <- dbGetQuery(sqldb, .self$query) #.self$disconnect dbDisconnect(sqldb) #return the df return(query_result) } #' connect to sqlite database run update query #' @return rows_updated by query ,updateQuery = function(update_query) { if(is.null(update_query)==FALSE) query <<- update_query #.self$connect sqldb <- dbConnect(RSQLite::SQLite(), .self$database) query_result <- dbSendStatement(sqldb, .self$query) dbHasCompleted(query_result) rows_updated <- dbGetRowsAffected(query_result) dbClearResult(query_result) #.self$disconnect dbDisconnect(sqldb) return(rows_updated) } #' read a table into a dataframe #' @param tablename character name of source table #' @return dataframe from the source table ,readTable = function(tablename = "character") { sqldb <- dbConnect(RSQLite::SQLite(), .self$database) return(dbReadTable(sqldb, tablename)) dbDisconnect(sqldb) } #' write an entire dataframe to an sqlite table #' @param output_dataframe data frame to send to dbase #' @param remote_table destination table in the database #' @param overwrite_table overwrite the table DEFAULT is FALSE #' @param append_table append to destination table DEFAULT is TRUE ,writeTable = function(output_dataframe = "data.frame" , remote_table = NULL , overwrite_table = NULL , append_table = NULL) { # set default values if(is.null(remote_table)==FALSE) destination_table <<- remote_table if(is.null(overwrite_table)) overwrite_table <- FALSE if(is.null(append_table)) append_table <- TRUE #write to the table sqldb <- dbConnect(RSQLite::SQLite(), .self$database) dbWriteTable(sqldb, .self$destination_table , output_dataframe , overwrite = overwrite_table , append = append_table) dbDisconnect(sqldb) } ) |
Leave a Reply