在Shiny R中从SQLite检索用户名和密码

最初设定

最初，您需要创建数据库-存储db.sqlite所有表（用户数据）的文件。文件应与以下文件夹位于同一文件夹中ui.R；server.R如果没有，请更改dbname="relative_path/db.sqlite"参数中的路径。

library(RSQLite)
# creates connection to SQLite db, if not exists - creates one
db <- dbConnect(SQLite(), dbname="db.sqlite")

# create table users where logins and passwords are stored
dbSendQuery(conn = db,
        "CREATE TABLE users
        (username TEXT,
        password TEXT)")

# insert some initial data to work with
dbSendQuery(db, "INSERT INTO users ( username, password)
                 VALUES ( 'ester', 'silva' );")

代码修改

而不是你的台词。

Id.username <- which(my_username == Username)
Id.password <- which(my_password == Password)
if (length(Id.username) > 0 & length(Id.password) > 0) {
  if (Id.username == Id.password) {
    USER$Logged <- TRUE
   } 
 }

将此内容从R切换到SQLite功能。查询的结果是rowid用户的，可以视为uid并进一步传递以获得用户数据。

      query <- sprintf({"
        SELECT rowid 
        FROM users 
        WHERE username='%s' and password ='%s'"}, 
        Username, Password, serialize=F) 
      db   <- RSQLite::dbConnect(RSQLite::SQLite(), dbname="db.sqlite")
      user <- RSQLite::dbGetQuery(db, query) 
      RSQLite::dbDisconnect(db)
      if ( length(user$rowid)==1 ) {
        USER$Logged <- TRUE
      }

工作实例

library(shiny)
Logged = FALSE;
my_username <- "ester"
my_password <- "silva"
ui2 <- function(){tagList(tabPanel("Test"))}

ui1 <- function(){
  tagList(
    div(id = "login",
        wellPanel(textInput("userName", "Username"),
                  passwordInput("passwd", "Password"),
                  br(),actionButton("Login", "Log in"))),
    tags$style(type="text/css", "#login {font-size:10px;   text-align: left;position:absolute;top: 40%;left: 50%;margin-top: -100px;margin-left: -150px;}")
  )}

server = (function(input, output,session) {

  USER <- reactiveValues(Logged = Logged)

  observe({ 
    if (USER$Logged == FALSE) {
      if (!is.null(input$Login)) {
        if (input$Login > 0) {
          Username <- isolate(input$userName)
          Password <- isolate(input$passwd)
          query <- sprintf({"
            SELECT rowid 
            FROM users 
            WHERE username='%s' and password ='%s'"}, 
                           Username, Password, serialize=F) 
          db   <- RSQLite::dbConnect(RSQLite::SQLite(), dbname="db.sqlite")
          user <- RSQLite::dbGetQuery(db, query) 
          RSQLite::dbDisconnect(db)
          if ( length(user$rowid)==1 ) {
            USER$Logged <- TRUE
          }
        } 
      }
    }    
  })
  observe({
    if (USER$Logged == FALSE) {

      output$page <- renderUI({
        div(class="outer",do.call(bootstrapPage,c("",ui1())))
      })
    }
    if (USER$Logged == TRUE) 
    {
      output$page <- renderUI({
        div(class="outer",do.call(navbarPage,c(inverse=TRUE,title = "Contratulations you got in!",ui2())))
      })
      print(ui)
    }
  })
})
ui = (htmlOutput("page"))


shinyApp(ui = ui, server = server)