I have an app where I am trying to get the data into a sqlite database. somehow I am not able to dbdisconnect the database by using onsessionended and I think this is the reason next time when I run the app and try to create a table it doesn't work properly.
I can paste the entire app and you can run it. It is fine and simple
Just let me know if there is anything I am doing wrong.
I just want to
1> upload the file in db
2> disconnect database
3> reset the fileinput parameter.
But somehow it doesn't work for me.
Please answer me anything you know about these things.
# Load_Libraries ----------------------------------------------------------
library(RPostgres)
library(DBI)
library(data.table)
library(shiny)
library(shinyBS)
library(shinydashboard)
library(dashboardthemes)
library(tidyverse)
library(shinyjs)
library(DT)
library(RSQLite)
library(DBI)
options(shiny.maxRequestSize=200*1024^2)
dir.create(file.path("D:/", "HRdata"), showWarnings = FALSE)
# Header ------------------------------------------------------------------
main_header<- dashboardHeader(
title = shinyDashboardLogoDIY(
boldText="HR",
mainText="Dashboard"
,badgeText = "Beta"
,textSize = 17
,badgeTextColor = "white"
,badgeTextSize = 2
,badgeBackColor = "#40E0D0"
,badgeBorderRadius = 3
)
)
# Sidebar -----------------------------------------------------------------
main_siderbar<- dashboardSidebar(
sidebarMenu(
menuItem(id='file_menu',text = 'upload file',
icon = icon('file-excel-o',"fa-2x",lib='font-awesome'),
radioButtons("file_type", label = ("Please Select the Type of File uploaded"),
choices = list("XLSX" = 'XLSX', "CSV" = 'CSV'),
selected = "XLSX"),
fileInput("file_upload", "Choose a File",
accept = c(
"text/csv",
"xlsx",
"xls",
"text/comma-separated-values,text/plain",
".csv")),
uiOutput('select_sheet'),
selectInput('table_sql',label = 'Please select the Table',
choices =c('casual','permanent','attendence'),
multiple = FALSE
),
actionButton('file_upload_sql',' upload in database',
icon = icon('database',"fa-2x",lib='font-awesome'),
width = '90%')
)
)
)
# Body --------------------------------------------------------------------
main_body<- dashboardBody(
# themes ------------------------------------------------------------------
shinyDashboardThemes(
theme = "purple_gradient"
),
# content -----------------------------------------------------------------
DTOutput('file_data')
)
# Ui_function -------------------------------------------------------------
main_ui<- dashboardPage(
main_header,
main_siderbar,
main_body
)
# Server_Function ---------------------------------------------------------
main_server<- function(input,output,session){
# connect_database --------------------------------------------------------
sql<- reactive({
dbConnect(SQLite(),
dbname='d:/HRdata/maindata.sqlite'
)
})
# Check number of Sheets --------------------------------------------------
output$select_sheet<-renderUI({
req(input$file_upload)
if(input$file_type == 'XLSX'){
if(input$file_upload$name %>% str_detect('.xlsx')){
selectInput('i_select_sheet','Select Sheet',
choices =excel_sheets(input$file_upload$datapath),
multiple = FALSE,selected = 1
)}else{
return(NULL)
}}else{
return(NULL)
}
})
# create_data -------------------------------------------------------------
file_table<-reactive({
req(input$file_upload)
if(input$file_type == 'XLSX'){
if(input$file_upload$name %>% str_detect('.xlsx')){
req(input$i_select_sheet)
setDT(read_excel(input$file_upload$datapath,sheet = input$i_select_sheet))
} else{
return(NULL)
}
}else{
if(input$file_upload$name %>% str_detect('.csv')){
fread(input$file_upload$datapath)
}else{
return(NULL)
}
}
})
# Render_Table ------------------------------------------------------------
output$file_data<-renderDT({
file_table()
})
# Upload_in_SQL -------------------------------------------------------------
observeEvent(input$file_upload_sql,
function(){
dbWriteTable(sql(),
input$table_sql,
value =file_table(),
append=TRUE
)
}
)
# reset_fileinput ---------------------------------------------------------------
observeEvent(input$file_upload_sql,{
reset('file_upload')
})
# show_message ------------------------------------------------------------
observeEvent(input$file_upload_sql,{
showModal(modalDialog(
title = "Important message",
"Please Don't click upload again untill you change the file!",
easyClose = FALSE
))
})
# Session_close -----------------------------------------------------------
session$onSessionEnded(function(session){
dbDisconnect(sql())
})
# endServer ---------------------------------------------------------------
}
# run_app -----------------------------------------------------------------
shinyApp(main_ui,main_server)
see