查看原文
其他

R:关系型数据库管理

2017-08-23 徐洲更 生信媛

R操作关系型数据库

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

简介

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

  • 数据库驱动, 负责与数据库进行交互,比如说与SQLite数据库交互要用到RSQLite,如果是MYSQL,则是RMYSQL

  • 连接对象,建立与数据库的连接,前端操作经由该连接完成

  • 结果, 操作的结果在这里显示


具体操作

首先加载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语法书,虽然忘得差不多了。

您可能也对以下帖子感兴趣

文章有问题?点此查看未经处理的缓存