I wrote an interface class to help with working with the SQLite database.<\/p>\n
Methods include, selectQuery, updateQuery, readTable, writeTable<\/p>\n
<\/p>\n
see code below<\/p>\n
<\/p>\n
# sql Interface Class\r\nlibrary(RSQLite)\r\nlibrary(DBI)\r\n\r\n# ----------------------------------------------------------\r\n#' class for reading from sqllite dbase\r\n#' @param databaase character for name of database to connect too\r\n#' @param query character for query to process\r\n#'\r\n\r\nsqlInterface <- setRefClass(\"sqlInterface\", fields = list(database = \"character\"\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t ,query = \"character\"\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t ,sql_db = \"SQLiteConnection\"\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t ,destination_table = \"character\"\r\n\t\t\t\t\t\t\t))\r\nsqlInterface$methods(\r\n\t\t\tconnect = function()\r\n\t\t\t{\r\n\t\t\t\t#' connect to sqlite database\r\n\t\t\t\tsql_db <<- dbConnect(RSQLite::SQLite(), .self$database)\r\n\t\t\t}\r\n\t\t\t,disconnect = function()\r\n\t\t\t{\r\n\t\t\t\t#' close database connection\r\n\t\t\t\tdbDisconnect(sqldb)\r\n\t\t\t}\r\n\t\t\t#' connect to sqlite database run query and return results\r\n\t\t\t#' @return query_results data.frame with query results\r\n\t\t\t,selectQuery = function()\r\n\t\t\t{\r\n\t\t\t\t#.self$connect\r\n\t\t\t\tsqldb <- dbConnect(RSQLite::SQLite(), .self$database)\r\n\t\t\t\t# run the query\r\n\t\t\t\tquery_result <- dbGetQuery(sqldb, .self$query)\r\n\t\t\t\t#.self$disconnect\r\n\t\t\t\tdbDisconnect(sqldb)\r\n\t\t\t\t#return the df\r\n\t\t\t\treturn(query_result)\r\n\t\t\t}\r\n\t\t\t#' connect to sqlite database run update query\r\n\t\t\t#' @return rows_updated by query\r\n\t\t\t,updateQuery = function(update_query)\r\n\t\t\t{\r\n\t\t\t\tif(is.null(update_query)==FALSE) query <<- update_query\r\n\t\t\t\t#.self$connect\r\n\t\t\t\tsqldb <- dbConnect(RSQLite::SQLite(), .self$database)\r\n\r\n\t\t\t\tquery_result <- dbSendStatement(sqldb, .self$query)\r\n\t\t\t\tdbHasCompleted(query_result)\r\n\r\n\t\t\t\trows_updated <- dbGetRowsAffected(query_result)\r\n\t\t\t\tdbClearResult(query_result)\r\n\r\n\t\t\t\t#.self$disconnect\r\n\t\t\t\tdbDisconnect(sqldb)\r\n\t\t\t\treturn(rows_updated)\r\n\t\t\t}\r\n\t\t\t#' read a table into a dataframe\r\n\t\t\t#' @param tablename character name of source table\r\n\t\t\t#' @return dataframe from the source table\r\n\t\t\t,readTable = function(tablename = \"character\")\r\n\t\t\t{\r\n\t\t\t\tsqldb <- dbConnect(RSQLite::SQLite(), .self$database)\r\n\t\t\t\treturn(dbReadTable(sqldb, tablename))\r\n\t\t\t\tdbDisconnect(sqldb)\r\n\t\t\t}\r\n\r\n\t\t\t#' write an entire dataframe to an sqlite table\r\n\t\t\t#' @param output_dataframe data frame to send to dbase\r\n\t\t\t#' @param remote_table destination table in the database\r\n\t\t\t#' @param overwrite_table overwrite the table DEFAULT is FALSE\r\n\t\t\t#' @param append_table append to destination table DEFAULT is TRUE\r\n\t\t\t,writeTable = function(output_dataframe = \"data.frame\"\r\n\t\t\t\t\t\t\t\t , remote_table = NULL\r\n\t\t\t\t\t\t\t\t , overwrite_table = NULL\r\n\t\t\t\t\t\t\t\t , append_table = NULL)\r\n\t\t\t{\r\n\t\t\t\t# set default values\r\n\t\t\t\tif(is.null(remote_table)==FALSE) destination_table <<- remote_table\r\n\t\t\t\tif(is.null(overwrite_table)) \toverwrite_table <- FALSE\r\n\t\t\t\tif(is.null(append_table))\t\tappend_table <- TRUE\r\n\r\n\t\t\t\t#write to the table\r\n\t\t\t\tsqldb <- dbConnect(RSQLite::SQLite(), .self$database)\r\n\t\t\t\tdbWriteTable(sqldb, .self$destination_table\r\n\t\t\t\t\t\t\t , output_dataframe\r\n\t\t\t\t\t\t\t , overwrite = overwrite_table\r\n\t\t\t\t\t\t\t , append = append_table)\r\n\t\t\t\tdbDisconnect(sqldb)\r\n\t\t\t}\r\n)\r\n<\/pre>\n <\/p>\n","protected":false},"excerpt":{"rendered":"
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<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_crdt_document":"","footnotes":""},"categories":[4,5,6],"tags":[14,30,35,36],"series":[],"class_list":["post-154","post","type-post","status-publish","format-standard","hentry","category-code","category-r","category-sql","tag-sql","tag-code","tag-r","tag-class"],"_links":{"self":[{"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/posts\/154","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/comments?post=154"}],"version-history":[{"count":1,"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/posts\/154\/revisions"}],"predecessor-version":[{"id":155,"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/posts\/154\/revisions\/155"}],"wp:attachment":[{"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/media?parent=154"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/categories?post=154"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/tags?post=154"},{"taxonomy":"series","embeddable":true,"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/series?post=154"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}