CODEWITHSUNDEEP
mysqlsqlrshiny
Raghavan vmvs
Raghavan vmvs

Reputation: 1265

Run an SQL Query from R Shiny by clicking on the input text box

I have created a shiny App with a run button to run an sql query to retrieve data from mysql database. the Data so retrieved will act as the input of the R shiny 

  library(DBI)
  library(bigrquery)
  library(readr)
  library(shiny)

The above step imports the libraries.

Next we create the database connection parameters

    project <- "A-dev"
    dataset <- "A_table"
    con <- dbConnect( bigrquery::bigquery(), project = project, dataset = 
    dataset)

Within the dataset, are a set of columns that contain the parameters we need to extract. The query used to extract these column values is as follows

query2 <- "select C_name from A_table group by 
C_name"

The above query will create a list of values to act as a drop down input selection in R shiny

The following is the structure of the App.

The UI of the shiny App is created as follows. The layout consists of a Run button to run the query, and a set of drop downs that will return the results of the query 

   ui <- fluidPage(

   sidebarLayout(sidebarPanel(actionButton(inputId = "Button", label = "run 
  Query"),uiOutput(outputId = "List1")),
   mainPanel(dataTableOutput(outputId = "Table1")) ))

The Server Part of the App is as follows

     server <- function(input, output, session) {

      ##### Here the Action button is connected
       my_data <- reactive({ 
       if(input$Button == 0)
       {return(0) }
       isolate({
       input$Button


    query2 <- "select C_name from A_table group by C_name"


      df <- query_exec(query2, project = project, useLegacySql = FALSE, 
      max_pages = Inf)
      df })})

     #### HERE WE CREATE THE DROP DOWN MENU PART

    output$List1<-renderUI({ 
      my_data=my_data()
      choices<-unique(my_data) 
      #creates State select box object called in ui
        selectInput(inputId = "List1", #name of input
              label = "List1:", #label displayed in ui
              choices =choices, selected = NULL, multiple = TRUE, selectize 
           = T) }) #default choice (not required)


      output$Table1<-renderDataTable({###CREATE TABLE OUTPUT
      data.frame(my_data()) })}

We now Run the App as follows

         shinyApp(ui, server)

When we press the run query button, the App runs and the drop down menu is filled dynamically from the sql database.

My question is : Is it possible to run the query by merely clicking the select drop down box. In this case the Run query button will be rendered redundant.

Upvotes: 0

Views: 1569

Answers (1)

Eli Berkow
Eli Berkow

Reputation: 2725

It's tricky to test this without any dummy data but if I understand correctly please test the below:

server <- function(input, output, session) {

    ##### Here the Action button is connected
    my_data <- reactiveVal({
            query2 <- "select C_name from A_table group by C_name"


            df <- query_exec(query2, project = project, useLegacySql = FALSE, 
                             max_pages = Inf)
            df })

    #### HERE WE CREATE THE DROP DOWN MENU PART

    output$List1<-renderUI({ 
        my_data=my_data()
        choices<-unique(my_data) 
        #creates State select box object called in ui
        selectInput(inputId = "List1", #name of input
                    label = "List1:", #label displayed in ui
                    choices =choices, selected = NULL, multiple = TRUE, selectize 
                    = T) #default choice (not required)})
    })

        output$Table1<-renderDataTable({###CREATE TABLE OUTPUT
            data.frame(my_data()) })}

Although this would run at start-up and you requested when selecting the dropdown so I may be misunderstanding your use case?

Upvotes: 1

Related Questions