Yin's Blog
0%

[tutorial]Shiny连接MySQL

Posted on Edited on
  1. MySQL准备
  2. 数据库准备
  3. Shiny连接

前置条件：0. 本文环境：windows 7 旗舰版；1. MySQL已经安装（为了方便，可选择利用AppServ一键安装）；2. 已安装R、RStudio环境。

MySQL准备

  • 利用AppServ，选择MySQL Start启动MySQL
  • 进入cmd
    1
    2
    3
    4
    5
    6
    7
    > mysql -u username -p
    # 输入密码，进入mysql server
    mysql>
    mysql>exit;
    #也可以查看MySQL版本
    > mysql -V
    # 这里是5.7.17

数据库准备

  • 建立db：shiny_db

    1
    2
    mysql> CREATE DATABASE shiny_db CHARACTER SET utf8 COLLATE utf8_general_ci;
    # MySQL支持utf8编码

  • 建立table：shiny_table

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    # 显示数据库；
    mysql> show databases;
    # 使用数据库；
    mysql> use shiny_db;
    # 建立表格
    mysql> create table shiny_table(
        -> id smallint unsigned,
        -> 成绩 float not null,
        -> primary key (id));
    Query OK, 0 rows affected (0.29 sec)
    # 补充：float(6，2)的含义是，数据float型，数据长度是6，小数点后保留2位。
    # 插入数据（可选）
    mysql> insert into shiny_table
        -> (id, 成绩)
        -> values (1,90.5);
    Query OK, 1 row affected (0.06 sec)

Shiny连接

  • RStudio编写代码: shiny_mysql_conn.R（需要的时候，save with encoding… utf-8)
  • 具体代码：
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    # 相关库导入
    library(shiny)
    library(RMySQL)

    # shiny的ui部分
    ui <- fluidPage(
      plotOutput("plot")
    )

    # shiny的server部分

    server <- function(input, output) {
      library(RMySQL)
      # 连接MySQL的参数
      options(mysql = list(
        "host" = "", #endpoint from RDS MYSQl instance OR IP: 127.0.0.1
        "port" = 3306, #Default port is 3306
        "user" = "你的用户名", #root unless changed
        "password" = "你的密码" 
      ))
      
      DB_NAME <- "shiny_db" #MySQL database name
      TABLE_NAME <- "shiny_table" #MySQL table name
      
      # MySQL写入函数
      save_data_mysql <- function(data) {
        db <- dbConnect(MySQL(), dbname = DB_NAME,
                        host = options()$mysql$host,
                        port = options()$mysql$port,
                        user = options()$mysql$user,
                        password = options()$mysql$password)
        query <-
          sprintf("INSERT INTO %s (%s) VALUES ('%s')",
                  TABLE_NAME,
                  paste(names(data), collapse = ", "),
                  paste(data, collapse = "', '")
          )
        dbGetQuery(db, query)
        dbDisconnect(db)
      }
      # MySQL读取函数
      load_data_mysql <- function() {
        # 建立conn，此处db为conn对象
        db <- dbConnect(MySQL(), dbname = DB_NAME,
                        host = options()$mysql$host,
                        port = options()$mysql$port,
                        user = options()$mysql$user,
                        password = options()$mysql$password)
        # 检索语句                
        query <- sprintf("SELECT * FROM %s", TABLE_NAME)
        
        # 中文编码处理
        #dbSendQuery(db, 'set character set "utf8"')
        dbSendQuery(db, 'set names gbk')
        # 获取数据
        data <- dbGetQuery(db, query)
        # 关闭数据库conn
        dbDisconnect(db)
        # 返回data.frame
        data
      }
      
      data <- load_data_mysql()
      
      # renderPlot() to draw
      output$plot <- renderPlot({
        plot(x = data$id, y = data$成绩, type = "b")
      })
    }

    # shinyApp
    shinyApp(ui, server)