- MySQL准备
- 数据库准备
- 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
2mysql> 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)