R:关系型数据库管理

R操作关系型数据库

基本要求: R语言基础,懂得一定SQL语法, 懂得使用搜索引擎
目标: 学会使用DBI操作SQLite数据库

简介

R本身不具备数据库操作能力,需要额外的扩展包--DBI(database interface)。DBI将数据库管理分为前端和后端。用户在前端使用对应的API, 经由DBI转换成相应的底层操作.
DBI分为三个部分:

  • 数据库驱动, 负责与数据库进行交互,比如说与SQLite数据库交互要用到RSQLite,如果是MYSQL,则是RMYSQL
  • 连接对象,建立与数据库的连接,前端操作经由该连接完成
  • 结果, 操作的结果在这里显示
R:关系型数据库管理
DBI结构

具体操作

首先加载R包

library(DBI)
library(RSQLite)

第一步: 连接或创建数据库:dbConnect ,建立连接对象,后续的操作都基于该对象。

常用命令为:dbExecutedbSendStatement, 前者适用于数据量不大时,后者适用数据量较大时

# 新建或连接已有的磁盘上数据库
con <- dbConnect(RSQLite::SQLite(), 'test.db')
# 临时在内存中建立
con <- dbConnect(RSQLite::SQLite(), ":memory:")

参数1: 数据库驱动引擎(RSQLite::SQLite());
参数2: 数据库连接参数(如user, password, host, port, dbname),SQLite只需要提供数据库命名(不存在则新建),:memory: 则表示在仅保存在内存中.

第二步: 在R上实现数据库的常用操作: 增删改查

:建立表并增加记录

# 导入已有的数据框, 参数为“连接对象, 表名,数据框”
dbWriteTable(con, 'mtcar', mtcars )
# 建立新表
dbExecute(con, 'CREATE TABLE test (name char(10), age int(10))')
dbExecute(con, 'INSERT INTO test (name, age) VALUES ("Tom", 25), ("Jelly",26)')
# 数据量过大,可以批次插入
rs <- dbSendStatement(con, 'INSERT INTO test (name, age) VALUES ("Tom", 25), ("Jelly",26)' )
dbHasCompleted(rs) # 判断操作是否完成
dbGetRowAffected(rs) # 返回影响的行
dbClearResult(rs) # 清空内存

: 有两种方法, 一类获取所有数据,一类分批获取数据

# 方法1
dbGetQuery(con, 'SELECT * FROM test')
# 方法2
con <- dbConnect(RSQLite::SQLite(), ":memory:")
dbWriteTable(con, "mtcars", mtcars)
rs <- dbSendQuery(con, "SELECT * FROM mtcars WHERE cyl = 4;")
dbFetch(rs, n=10) # 获取10行
dbFetch(rs, n=10) # 再获取10行
dbClearResult(rs) # 清空rs句柄中的内容
dbDisconnect(con)

: 删除记录

# 删除记录前,要先查询
dbGetQuery(con, 'SELECT * FROM mtcar WHERE mpg > 30')
# 删除记录
dbExecute(con, 'DELETE FROM mtcar WHERE mpg > 30')
# 删除所有记录,谨慎操作
dbExecute(con, 'DROP TABLE test')
dbRemoveTable(con, 'test')

: 修改记录

# 修改记录前也是先查找
dbGetQuery(con, 'SELECT * FROM mtcar WHERE am ==1')
# 然后我们将mpg=21,且qsec=16.46 记录 的mpg修改为21.1
dbExecute(con, 'UPDATE mtcar SET mpg = 21.1 WHERE mpg =21.0 AND qsec =16.46')
# 事后验证
dbGetQuery(con, 'SELECT * FROM mtcar WHERE mpg = 21.1')

一般而言,增查改删操作最好能够有反悔药。dbBegin(con)会记录一系列操作,直到你呈交修改dbCommit(con)回滚修改dbRollback(con)

con <- dbConnect(RSQLite::SQLite(), ":memory:")
dbWriteTable(con, "cash", data.frame(amount = 100))
# 测试dbCommit
dbBegin(con)
withdrawal <- 300
dbExecute(con, "UPDATE cash SET amount = amount + ?", list(withdrawal))
dbCommit(con)
# 测试回滚
dbBegin(con)
withdrawl <- 5000
dbExecute(con, "UPDATE cash SET amount = amount + ?", list(withdrawal))
## 查看结果
dbGetQuery(con,'SELECT * FROM cash')
## 回滚
dbRollback(con)
## 检查结果
dbGetQuery(con,'SELECT * FROM cash')

所有操作完毕,结束连接dbDisconnect(con)

下面一些命令也比较常用,用法比较简单。

  • dbWriteTable(连接对象, 表名, 数据框): 将数据框保存为表
  • dbReadTable(连接对象, 表名 ): 读取指定表中所有内容
  • dbListTable(连接对象): 列出数据库中包含的表格
  • dbListFields(连接对象, 表名): 列出制定表格列名
  • dbReadTable(连接对象, 表名): 将指定表读取为数据框

最后感言:感谢自己大学时候看过几本SQL语法书,虽然忘得差不多了。

上一篇:OAF_文件系列9_实现OAF解析Excel并读取至数据库JXL


下一篇:WordPress设置阿里云OSS或七牛云存储及CDN无响应的解决办法及工具合集