R之RMySQL
2024-08-30 20:17:50
linux，mysql和R的版本信息：
- Linux naci 3.19.0-16-generic #16-Ubuntu SMP
- Server version: 5.6.24-0ubuntu2 (Ubuntu)
- R version 3.1.2 (2014-10-31) -- "Pumpkin Helmet"
mysql的linux安装，参照上一篇关于liunx下安装mysql的文章。
1. 安装RMySQL包
在装RMySQL包之前，确定电脑上装了libmysqlclient-dev，否则在安装时会出现错误。
~$ sudo apt-get install libmysqlclient-dev #安装客户端的开发支持
~$ R
> install.packages("RMySQL","/home/beili/R/packages") #安装RMySQL包
> .libPaths(RMySQL)
2.RMySQL支持的相关函数
辅助函数
2.1 dbConnect，dbDisconnect #数据库连接函数
2.2 dbListFields ,dbListTables,dbGetInfo,dbListResults,summary,dbGetException,dbExistsTable #查看数据库或者参数信息
查询函数
2.3 dbGetQuery #查询函数
2.4 dbReadTable #读取表的数据
更新函数
2.5 dbWriteTable #创建数据库表或者将数据写入对应的表
2.6 dbRemoveTable # 删除数据库中的表
dbSendQuery函数
2.7 dbSendQuery ,dbClearResult #将query交给数据库引擎
2.8 dbColumnInfo，dbGetRowsAffected，dbGetRowCount，dbHasCompleted #查看数据库引擎执行结果
2.9 dbFetch，fetch #将dbSendQuery函数的结果抽取出来
2.10 dbNextResult，dbMoreResults #一条一条读取结果
事务函数
2.11 dbCommit，dbBegin，dbRollback
2.1 dbConnect和dbDisconnect函数
函数调用格式如下：
dbConnect(drv, dbname,username,password ,host,port，client.flag=CLIENT_MULTI_STATEMENTS..)
dbDisconnect(conn, ...）
注：client.flag=CLIENT_MULTI_STATEMENTS表示客户端允许执行多个statement。
由于和传统的设置参数差不多，所以下面直接看例子：
> conn=dbConnect(MySQL(),dbname="test",username="root",password="mdcl") #建立连接
> summary(conn,verbose=T) #查看连接信息
<MySQLConnection:0,0>
User: root
Host: localhost
Dbname: test
Connection type: Localhost via UNIX socket
MySQL server version: 5.6.24-0ubuntu2
MySQL client version: 5.6.24
MySQL protocol version: 10
MySQL server thread id: 4 Results:
> dbDisconnect(conn) #关闭连接
注：本地连接不用设置host和port，另外summary属于RMySQL的函数，是S4类，用户mysql驱动的信息
2.2 dbListFields ,dbListTables,dbGetInfo,dbListResults,summary,dbGetException查看数据库或者参数信息
函数调用格式如下：
dbListFields(conn, name, ...) #查看表的字段信息
dbListTables(conn, ...) #查看数据库下表
dbGetInfo(dbObj, what = "", ...) #获取mysql数据变量的信息,what指定得到list中的哪个值
dbListResults(conn, ...) #列出conn的查询结果
summary(object, verbose = FALSE, ...)#查看某个变量的相信信息
dbGetException(conn, ...) #查看conn的异常信息
dbExistsTable(conn, name, ...)
例如：
> dbListFields(conn,"people")
[1] "name" "sex" "age"
> dbListTables(conn)
[1] "people"
> dbGetInfo(conn)
$host
[1] "localhost" $user
[1] "root" $dbname
[1] "test"
... > dbListResults(conn)
list()
> dbGetException(conn)
$errorNum
[1] 0 $errorMsg
[1] ""
> dbExistsTable(conn, "people")
[1] TRUE
2.3 dbGetQuery 数据库查询
dbGetQuery实际要执行一连串的动作，在内部实际上会执行dbSendQuery函数，然后当dbHasCompleted为TRUE之后，又自动fetch出返回的结果，最后由on.exit保证执行dbClearResult。该函数支持各种各样的query，当执行select操作时，返回data.frame类型的结果；当执行insert/update操作时，返回NULL。
函数调用格式：
dbGetQuery(conn, statement, ...)
例子：
> res=dbGetQuery(conn,"select * from people")
> class(res)
[1] "data.frame"
> res
name sex age
1 bao M 12
2 qiao M 12
> dbGetQuery(conn,"show tables")
Tables_in_test
1 people
2 stu
3 student
> dbGetQuery(conn,"drop table student")
NULL
> dbGetQuery(conn,"show tables")
Tables_in_test
1 people
2 stu
2.4 dbReadTable 读取整个表的数据
调用格式：
dbReadTable(conn, name, row.names,check.names = TRUE, ...)
其中 row.names=k,表示第k列作为每一行的名字。
例子：
> dbReadTable(conn,"stu",row.names=1)
stuid name age
1 1 qiao 51
2 2 bao 27
3 3 qiao 1
4 4 bao 26
2.5 dbWriteTable 将数据写入数据库
调用格式：
dbWriteTable(conn, name, value, row.names=T,overwrite,append，...)
其中row.names表示是否将row.names写入数据库的，作为单独的一列；overwrite=T表示会覆盖掉原先的数据；append=T表示在原先的数据后面进行插入。
例子：
> dbGetQuery(conn,"truncate table stu")
NULL
> dbReadTable(conn,"stu",row.names=1)
[1] stuid name age
<0 行> (或0-长度的row.names)
> dbWriteTable(conn,"stu",student,append=T)
[1] TRUE
> dbReadTable(conn,"stu",row.names=1)
stuid name age
1 1 qiao 51
2 2 bao 27
3 3 qiao 1
4 4 bao 26
2.6 dbRemoveTable 删除表实际上执行了“Drop table name”的命令，在2.3中给出了drop的例子。
调用格式：
dbRemoveTable(conn,name)
例子：
> dbListTables(conn)
[1] "people" "stu"
> dbRemoveTable(conn,"people")
[1] TRUE
> dbListTables(conn)
[1] "stu"
2.7 dbSendQuery ,dbClearResult
- dbSendQuery函数提交查询，并在服务器那端同步执行，但是并不抽取其中的结果，因此需要配合dbFetch来使用，最后抽取完毕之后，使用dbClearResult来清洗返回的结果。既然有了dbReadTable函数了，为什么还要有dbSendQuery呢？如果需要返回的数据很大的情况下，而R又装不下，如果使用dbReadTable那么铁定不行了。dbSendQuery函数来说，DBMS执行了query语句，可能生成了大量的数据，不同的数据库驱动可能有不同的处理方式，有的存储在服务器端，一点一点发给R；有的传给客户端，但是不会一下子传给R。
- dbClearResult函数释放占用的资源。
调用格式：
dbSendQuery(conn, statement)
dbClearResult(res, ...)
如果dbConnect中client.flag没有设置下，只能执行一条statement，而且不dbClearResult执行的res是不能再执行dbSendQuery函数的，也就是说正确的执行方式如下：
rs1=dbSendQuery(conn, statement)
...
dbClearResult(rs1, ...)
rs2=dbSendQuery(conn, statement)
...
dbClearResult(rs2, ...)
而下面的调用格式是错的：
rs1=dbSendQuery(conn, statement)
rs2=dbSendQuery(conn, statement)
...
dbClearResult(rs1, ...)
dbClearResult(rs2, ...)
例子：
> res=dbSendQuery(conn,"select * from stu")
> dbGetInfo(res)
$statement
[1] "select * from stu" $isSelect
[1] 1 $rowsAffected
[1] -1 $rowCount
[1] 0 $completed
[1] 0 $fieldDescription
$fieldDescription[[1]]
NULL > res2=dbSendQuery(conn,"select * from stu")
错误于.local(conn, statement, ...) :
connection with pending rows, close resultSet before continuing
> dbClearResult(res)
[1] TRUE
> res2=dbSendQuery(conn,"select * from stu")
2.8 dbColumnInfo，dbGetRowsAffected，dbGetRowCount，dbHasCompleted
这一系列的函数，其实是dbSendQuery的辅助函数，得到的信息，使用dbGetInfo（res）都能得到。
重点说一下dbHasCompleted函数,函数含义是服务器端的数据是否被抽取完毕：
调用格式：
dbHasCompleted(res,...)
注：只有查询的数据全部被R抽取完，该函数才能返回TRUE值，具体使用见2.9中的例子。
2.9 dbFetch，fetch
fetch是比较老的一个版本，dbFetch和fetch功能相同，不过比较鼓励使用dbFetch。
调用格式：
dbFetch(res,n,...)
ftch(res,n,)
注：n表示读取的行数，当n=-1时，表示全部抽取。
例子：
> dbWriteTable(conn, "mtcars", mtcars)
[1] TRUE > res <- dbSendQuery(conn, "SELECT * FROM mtcars WHERE cyl = 4")
> dbFetch(res)
row_names mpg cyl disp hp drat wt qsec vs am gear carb
1 Datsun 710 22.8 4 108.0 93 3.85 2.320 18.61 1 1 4 1
2 Merc 240D 24.4 4 146.7 62 3.69 3.190 20.00 1 0 4 2
3 Merc 230 22.8 4 140.8 95 3.92 3.150 22.90 1 0 4 2
4 Fiat 128 32.4 4 78.7 66 4.08 2.200 19.47 1 1 4 1
5 Honda Civic 30.4 4 75.7 52 4.93 1.615 18.52 1 1 4 2
6 Toyota Corolla 33.9 4 71.1 65 4.22 1.835 19.90 1 1 4 1
7 Toyota Corona 21.5 4 120.1 97 3.70 2.465 20.01 1 0 3 1
8 Fiat X1-9 27.3 4 79.0 66 4.08 1.935 18.90 1 1 4 1
9 Porsche 914-2 26.0 4 120.3 91 4.43 2.140 16.70 0 1 5 2
10 Lotus Europa 30.4 4 95.1 113 3.77 1.513 16.90 1 1 5 2
11 Volvo 142E 21.4 4 121.0 109 4.11 2.780 18.60 1 1 4 2
> dbClearResult(res)
[1] TRUE > res <- dbSendQuery(conn, "SELECT * FROM mtcars")
> while (!dbHasCompleted(res)) {
+ chunk <- fetch(res, 10)
+ print(nrow(chunk))
+ }
[1] 10
[1] 10
[1] 10
[1] 2
> dbClearResult(res)
[1] TRUE
2.10 dbNextResult，dbMoreResults
- dbMoreResults是针对多个query statement来说的，如果还有额外的结果集需要处理，则返回TRUE，否则返回FALSE
- dbNextResult 接着处理下一个statement的结果集。
调用格式：
dbNextResult(con, ...)
dbMoreResult(con,...)
例子：
> con=dbConnect(MySQL(),dbname="test",username="root",password="mdcl",client.flag=CLIENT_MULTI_STATEMENTS)
> dbListTables(con)
[1] "mtcars" "stu"
> sql <- "SELECT cyl FROM mtcars LIMIT 5; SELECT vs FROM mtcars LIMIT 5"
> rs1 <- dbSendQuery(con, sql)
> dbFetch(rs1, n = -1)
cyl
1 6
2 6
3 4
4 6
5 8
> if (dbMoreResults(con)) {
+ rs2 <- dbNextResult(con)
+ dbFetch(rs2, n = -1)
+ }
vs
1 0
2 0
3 1
4 1
5 0
> dbClearResult(rs1)
[1] TRUE
> dbClearResult(rs2)
[1] TRUE
2.11 dbCommit ，dbBegin，dbRollback
三个事务函数，和数据库中的概念是一致的。
调用方式：
dbBegin(conn, ...)
dbCommit(conn, ...)
dbRollback(conn, ...)
最新文章
- 【JavaWeb学习】文件的上传和下载
- 线性分式变换（linear fractional transformation）
- 创意十足的web布局及交互设计
- header
- ios中autolayout
- [under the hood]Reduce EXE and DLL Size with LIBCTINY.LIB
- android手机出现sqlite3 not found的解决方法
- TypeC一个微软开发的超简单.NET依赖注入/IoC容器
- HDU 2487 Ugly window
- 开始使用Logstash
- 数据库分库分表(sharding)系列(二) 全局主键生成策略
- python中使用ctypes调用MinGW生成的动态链接库（dll）
- 卡尔曼滤波（Kalman Filter）
- Java socket详解(转)
- java面向对象的思想（java三大特性）
- HTML DOM classList 属性
- n个月后兔子的个数问题（for循环）
- BZOJ.3693.圆桌会议(Hall定理 线段树)
- 关于Unity中的Mesh Collider碰撞器
- JS控制函数执行次数（可带参数）