mysql - R中的selectInput从MySql获取数据

Question

我几天前才开始使用 MySql。如果这是一个非常基本的查询，请原谅我。

我在 MySql 中有一个表，有四列:内容、media_provider、日期和 Sentimet。 Sentimet 分为三个级别，即 pos、neg 和 neu。

使用 Shiny 的 selectInput，我尝试获取所有标记为 pos、neg 或 neu 的记录。代码如下:

ui <- shinyUI(fluidPage(
    titlePanel("Generic grapher"),
    sidebarLayout(
        sidebarPanel(

            selectInput(inputId = "wafer", label = "Select sentiment", choices = c("pos", "neg", "neu"), 
                        multiple = FALSE, selectize = TRUE, width = "40%"),

            actionButton("do", "An action button")
        ),

        mainPanel(
            verbatimTextOutput("value"),
            verbatimTextOutput("que"),
            verbatimTextOutput("wq_print"),
            dataTableOutput(outputId="post")
        )
    )
)
)

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

    values <- reactiveValues()
    values$df <- data.frame()

    d <- eventReactive(input$do, { input$wafer })

    output$value <- renderPrint({ d() }) 

    a <- reactive({ paste("SELECT * FROM apparel WHERE sentimet = ", d(), sep="") })

    output$que <- renderPrint({ a() }) 

    observe({
        if (!is.null(d())) {
            wq <- reactive({  query( a() ) })

            output$wq_print <- renderPrint({ print(str(wq())) })

            values$df <- rbind(isolate(values$df), wq())
        }
    })

    output$post <- renderDataTable({ values$df })  

})

shinyApp(ui, server)

当我运行时，出现以下错误:

Warning: Error in .local: could not run statement: Unknown column 'neg' in 'where clause'

我知道MySql将“factor”neg视为列“neg”，因此我将selectInput中的选择选项从“pos”更改为“pos”= 1，MySql查找值1，并返回NULL。我改变了 1 =“pos”，“pos”=“pos”，没有任何效果。但是，当我直接在 MySql 中提供查询“SELECT * FROM dress WHERE Sentimet =“pos”;”时工作完美。不确定如何使用 selectInput。下面提供了一个 3 行可重现的示例:

structure(list(CONTENT = c("@myntra Good Morning If you are born poor its not your mistake, But if you die poor its your mistake. -Bill Gates Good Day", 
"@myntra i have been mailing my issue daily since past week.All i get in reply is an auto generated assurance mail. 1st time pissed wd myntra", 
"@myntra I'm a big Arsenal fan & made a big PUMA collection! ¥Ë_ Shared that collection yesterday. Wanna win... ¥Ë_¥Ë_¥Ë_ #myPUMAcollection"
), MEDIA_PROVIDER = c("TWITTER", "TWITTER", "TWITTER"), PUBLISH_DATE = structure(list(
    sec = c(0, 0, 0), min = c(30L, 22L, 11L), hour = c(7L, 7L, 
    7L), mday = c(21L, 21L, 21L), mon = c(10L, 10L, 10L), year = c(115L, 
    115L, 115L), wday = c(6L, 6L, 6L), yday = c(324L, 324L, 324L
    ), isdst = c(0L, 0L, 0L), zone = c("IST", "IST", "IST"), 
    gmtoff = c(NA_integer_, NA_integer_, NA_integer_)), .Names = c("sec", 
"min", "hour", "mday", "mon", "year", "wday", "yday", "isdst", 
"zone", "gmtoff"), class = c("POSIXlt", "POSIXt"), tzone = "Asia/Calcutta"), 
    sentimet = c("neg", "pos", "neu")), .Names = c("CONTENT", 
"MEDIA_PROVIDER", "PUBLISH_DATE", "sentimet"), class = "data.frame", row.names = c(NA, 
3L))

Answer 1

sentimet 列中的值是字符，因此您需要用单引号将它们括起来:

a <- reactive({ paste0("SELECT * FROM apparel WHERE sentimet = '", d(), "'") })