RPostgreSQL数据库编程指南

微信扫一扫,分享到朋友圈

RPostgreSQL数据库编程指南

R的极客理想系列文章,涵盖了R的思想,使用,工具,创新等的一系列要点,以我个人的学习和体验去诠释R的强大。

R语言作为统计学一门语言,一直在小众领域闪耀着光芒。直到大数据的爆发,R语言变成了一门炙手可热的数据分析的利器。随着越来越多的工程背景的人的加入,R语言的社区在迅速扩大成长。现在已不仅仅是统计领域,教育,银行,电商,互联网….都在使用R语言。

要成为有理想的极客,我们不能停留在语法上,要掌握牢固的数学,概率,统计知识,同时还要有创新精神,把R语言发挥到各个领域。让我们一起动起来吧,开始R的极客理想。

关于作者:

  • 张丹,分析师/程序员/Quant: R,Java,Nodejs
  • blog:http://fens.me
  • email: bsspirit@gmail.com

转载请注明出处:

http://blog.fens.me/r-rpostgressql/

前言

PostgreSQL和R通常可以一起用于数据分析,PostgreSQL作为数据库引擎,R作为统计工具。在R语言中,有2个包可以直接通过R连接PostgreSQL数据库,分别是RPostgreSQL包和RPostgres包,本文将先介绍用RPostgreSQL包进行直接连接PostgreSQL数据库。间接的连接方法还有RJDBC,RODBC等,以后再有专门的文章进行介绍。

PostgreSQL的数据库安装和基本信息,请参考文章 在Ubuntu上安装PostgreSQL

目录

  1. RPostgreSQL包介绍
  2. 安装RPostgreSQL包
  3. RPostgreSQL包的API使用
  4. window客户端环境的中文乱码解决方案

1. RPostgreSQL包介绍

RPostgreSQL一个R语言程序包,由官方提供访问 PostgreSQL数据库的R语言接口程序,RPostgreSQL主要依赖于DBI项目,以访问 PostgreSQL 数据库系统。CRAN中的项目地址

https://cran.r-project.org/web/packages/RPostgreSQL/index.html

2. 安装RPostgreSQL包

RPostgreSQL包的安装过程非常简单,就是一条语句就能够搞定了,不管是在Window环境还是Linux环境。

在Linux Ubuntu中,需要先安装libpq-dev系统库,不然会出现libpq-fe.h错误

In file included from RS-PQescape.c:7:
RS-PostgreSQL.h:23:14: fatal error: libpq-fe.h: No such file or directory
23 | #    include "libpq-fe.h"
|              ^~~~~~~~~~~~

安装libpq-dev系统库

~ sudo apt install libpq-dev

安装和加载RPostgreSQL包

> install.packages("RPostgreSQL")
> library(RPostgreSQL)

3. RPostgreSQL包的API使用

RPostgreSQL包,提供了很多的API函数,API的使用过程,我们可以分成6个部分来介绍。

  • 3.1 数据库连接
  • 3.2 数据库状态查看
  • 3.3 数据直接查询
  • 3.4 数据交互查询
  • 3.5 数据批量插入
  • 3.6 事务处理

3.1 建立数据库链接

# 加载PostgreSQL驱动
> drv = dbDriver("PostgreSQL")
# 建立数据库连接
> con1 = dbConnect(drv,  dbname="testdb",user = "test",  password = "test", host ="192.168.1.5")

3.2 数据库状态查看

# 再建立2个数据库连接
> con2 = dbConnect(drv,  dbname="testdb",user = "test",  password = "test", host ="192.168.1.5")
> con3 = dbConnect(drv,  dbname="testdb",user = "test",  password = "test", host ="192.168.1.5")

查看驱动状态

> dbGetInfo(drv)
$drvName
[1] "PostgreSQL"
$connectionIds                   # 连接1
$connectionIds[[1]]
<postgresqlconnection>
$connectionIds[[2]]              # 连接2
<postgresqlconnection>
$connectionIds[[3]]              # 连接3
<postgresqlconnection>
$fetch_default_rec
[1] 500
$managerId
<postgresqldriver>
$length
[1] 16
$num_con
[1] 3
$counter
[1] 9

查看con1数据连接状态

> dbGetInfo(con1)
$host
[1] "192.168.1.5"
$port
[1] "5432"
$user
[1] "test"
$dbname
[1] "testdb"
$serverVersion
[1] "12.0.4"
$protocolVersion
[1] 3
$backendPId
[1] 27263
$rsId
list()

关闭连接

# 关闭连接1
> dbDisconnect(con1)
[1] TRUE
# 查看con1状态
> isPostgresqlIdCurrent(con1)
[1] FALSE
# 查睦con1信息
> dbGetInfo(con1)
Error in postgresqlConnectionInfo(dbObj, ...) :
expired PostgreSQLConnection dbObj

关闭全部连接

# 通过循环关闭全部连接
> for(con in dbListConnections(drv)){
+   dbDisconnect(con)
+ }
# 查询驱动状态,没有活动连接
> dbGetInfo(drv)
$drvName
[1] "PostgreSQL"
$connectionIds   # 没有活动连接
list()
$fetch_default_rec
[1] 500
$managerId
<postgresqldriver>
$length
[1] 16
$num_con
[1] 0
$counter
[1] 9

3.3 数据直接查询

数据直接查询可以通过直接读取整个表数据dbReadTable()函数,或者执行查询语句dbGetQuery()函数来实现。

建立新连接

> con1 = dbConnect(drv,  dbname="testdb",user = "test",  password = "test", host ="192.168.1.5")

查看数据库中,所有的表。

# 所有表的列表
> dbListTables(con1)
[1] "account" "iris"
# 确认表名是否存在
> dbExistsTable(con1,"iris")
[1] TRUE

通过表名,读取整个表的数据

> dbReadTable(con1,"iris")
Sepal.Length Sepal.Width Petal.Length Petal.Width Species
1          5.1         3.5          1.4         0.2  setosa
2          4.9         3.0          1.4         0.2  setosa
3          4.7         3.2          1.3         0.2  setosa
4          4.6         3.1          1.5         0.2  setosa
5          5.0         3.6          1.4         0.2  setosa

通过SQL语句,读取表中的数据

> dbGetQuery(con1,  "SELECT * FROM iris")
row.names Sepal.Length Sepal.Width Petal.Length Petal.Width Species
1         1          5.1         3.5          1.4         0.2  setosa
2         2          4.9         3.0          1.4         0.2  setosa
3         3          4.7         3.2          1.3         0.2  setosa
4         4          4.6         3.1          1.5         0.2  setosa
5         5          5.0         3.6          1.4         0.2  setosa

3.4 数据交互查询

数据交互查询,可以通过发送一条sql请求dbSendQuery()函数来实现,返回数据访问句柄,再用dbFetch()函数来分批次获取数据。

# 发送SQL
> rs = dbSendQuery(con1,  statement  =  "SELECT * FROM iris")
# 判断rs句柄是否完成
> dbHasCompleted(rs)
[1] FALSE
# 取数据n=-1时,取全表数据
> dbFetch(rs,n=-1)
row.names Sepal.Length Sepal.Width Petal.Length Petal.Width Species
1         1          5.1         3.5          1.4         0.2  setosa
2         2          4.9         3.0          1.4         0.2  setosa
3         3          4.7         3.2          1.3         0.2  setosa
4         4          4.6         3.1          1.5         0.2  setosa
5         5          5.0         3.6          1.4         0.2  setosa
# 判断rs句柄是否完成
> dbHasCompleted(rs)
[1] TRUE
# 取完数据后,句柄rs就失效了
> dbFetch(rs,n=1)
data frame with 0 columns and 0 rows

我们在获得句柄后,可以查到关于句柄的详细信息。

# 发送SQL,获得句柄rs
> rs = dbSendQuery(con1,  statement  =  "SELECT * FROM iris")
# 查看rs信息
> dbGetInfo(rs)
$statement
[1] "SELECT * FROM iris"
$isSelect
[1] 1
$rowsAffected
[1] -1
$rowCount
[1] 0
$completed
[1] 0
$fieldDescription
$fieldDescription[[1]]
$fieldDescription[[1]]$name
[1] "row.names"    "Sepal.Length" "Sepal.Width"  "Petal.Length" "Petal.Width"  "Species"
$fieldDescription[[1]]$Sclass
[1] 16 14 14 14 14 16
$fieldDescription[[1]]$type
[1]  25 701 701 701 701  25
$fieldDescription[[1]]$len
[1] -1  8  8  8  8 -1
$fieldDescription[[1]]$precision
[1] -1 -1 -1 -1 -1 -1
$fieldDescription[[1]]$scale
[1] -1 -1 -1 -1 -1 -1
$fieldDescription[[1]]$nullOK
[1] TRUE TRUE TRUE TRUE TRUE TRUE
# 查看执行SQL语句
> dbGetStatement(rs)
[1] "SELECT * FROM iris"
# 查看数据结构
> dbColumnInfo(rs)
name    Sclass   type len precision scale nullOK
1    row.names character   TEXT  -1        -1    -1   TRUE
2 Sepal.Length    double FLOAT8   8        -1    -1   TRUE
3  Sepal.Width    double FLOAT8   8        -1    -1   TRUE
4 Petal.Length    double FLOAT8   8        -1    -1   TRUE
5  Petal.Width    double FLOAT8   8        -1    -1   TRUE
6      Species character   TEXT  -1        -1    -1   TRUE
# 查看已获取的行
> dbGetRowCount(rs)
[1] 0
# 获取2行
> ret1 <- dbFetch(rs, 2)
> ret1
row.names Sepal.Length Sepal.Width Petal.Length Petal.Width Species
1         1          5.1         3.5          1.4         0.2  setosa
2         2          4.9         3.0          1.4         0.2  setosa
# 查看已获取的行
> dbGetRowCount(rs)
[1] 2
# 查看句柄是否完成
> dbHasCompleted(rs)
[1] FALSE
# 获取剩余的行
> ret2 <- dbFetch(rs, -1)
> ret2
row.names Sepal.Length Sepal.Width Petal.Length Petal.Width Species
3         3          4.7         3.2          1.3         0.2  setosa
4         4          4.6         3.1          1.5         0.2  setosa
5         5          5.0         3.6          1.4         0.2  setosa
# 查看句柄是否完成
> dbHasCompleted(rs)
[1] TRUE
# 清空rs句柄
> dbClearResult(rs)
[1] TRUE
# 拼接完整数据集
> rbind(ret1,ret2)
row.names Sepal.Length Sepal.Width Petal.Length Petal.Width Species
1         1          5.1         3.5          1.4         0.2  setosa
2         2          4.9         3.0          1.4         0.2  setosa
3         3          4.7         3.2          1.3         0.2  setosa
4         4          4.6         3.1          1.5         0.2  setosa
5         5          5.0         3.6          1.4         0.2  setosa

3.5 数据批量插入

R语言中,数据组织方式是用data.frame的,data.frame可以批量地进行数据操作,在操作数据库时,我们也可以直接用data.frame与PostgreSQL数据库进行这种批量的操作。

当数据库没表时,dbWriteTable()函数会新建一个数据表。

# 新建数据表iris-table,插入5条数据
> dbWriteTable(con1,'iris-table',iris[1:5,])
[1] TRUE
# 读取数据表
> dbReadTable(con1,'iris-table')
Sepal.Length Sepal.Width Petal.Length Petal.Width Species
1          5.1         3.5          1.4         0.2  setosa
2          4.9         3.0          1.4         0.2  setosa
3          4.7         3.2          1.3         0.2  setosa
4          4.6         3.1          1.5         0.2  setosa
5          5.0         3.6          1.4         0.2  setosa

再进行数据表插入,由于表已存在,所以提示不能插入,如果需求追加插入时,可以增加append的合并参数。

# 插入数据失败
> dbWriteTable(con1,'iris-table',iris[6:10,])
[1] FALSE
Warning message:
In postgresqlWriteTable(conn, name, value, ...) :
table iris-table exists in database: aborting assignTable
# 追加插入数据
> dbWriteTable(con1,'iris-table',iris[6:10,],append=TRUE)
[1] TRUE
# 查看数据表,变成了10行。
> dbReadTable(con1,'iris-table')
Sepal.Length Sepal.Width Petal.Length Petal.Width Species
1           5.1         3.5          1.4         0.2  setosa
2           4.9         3.0          1.4         0.2  setosa
3           4.7         3.2          1.3         0.2  setosa
4           4.6         3.1          1.5         0.2  setosa
5           5.0         3.6          1.4         0.2  setosa
6           5.4         3.9          1.7         0.4  setosa
7           4.6         3.4          1.4         0.3  setosa
8           5.0         3.4          1.5         0.2  setosa
9           4.4         2.9          1.4         0.2  setosa
10          4.9         3.1          1.5         0.1  setosa

当表不需要后,可以直接删除数据表

# 删除数据表
> dbRemoveTable(con1,"iris-table")
[1] TRUE
# 删除后,数据表读取失败
> dbReadTable(con1,'iris-table')
Error in postgresqlExecStatement(conn, statement, ...) :
RS-DBI driver: (could not Retrieve the result : ERROR:  relation "iris-table" does not exist
LINE 1: SELECT * from "iris-table"
^
)
Error in names(out) <- make.names(names(out), unique = TRUE) :
NULL是不能有属性的
此外: Warning message:
In postgresqlQuickSQL(conn, statement, ...) :
Could not create execute: SELECT * from "iris-table"

3.6 事务提交和回滚

RPostgreSQL包的事务控制,是通过dbSendQuery()函数,配合事务控制dbBegin()函数,dbRollback()函数,dbCommit()函数来完成的。

我们模拟一个事务的场景,当删除一条数据时,判断这条数据影响多少行,如果删除超过1行则不执行删除进行回滚,如果正好为1行,则进行删除。

# 查看数据集,共5行
> df1<-dbGetQuery(con1, "select * from iris");df1
row.names Sepal.Length Sepal.Width Petal.Length Petal.Width Species
1         1          5.1         3.5          1.4         0.2  setosa
2         2          4.9         3.0          1.4         0.2  setosa
3         3          4.7         3.2          1.3         0.2  setosa
4         4          4.6         3.1          1.5         0.2  setosa
5         5          5.0         3.6          1.4         0.2  setosa
# 开始事务
> dbBegin(con1)
[1] TRUE
# 进行删除
> rs <- dbSendQuery(con1, "Delete from iris where \"Species\" = 'setosa'")
# 删除判断,影响大于1行则回滚,
> if(dbGetInfo(rs, what = "rowsAffected") > 1){
+   warning("Rolling back transaction")
+   dbRollback(con1)
+ }else{
+   dbCommit(con1)
+ }
[1] TRUE
Warning message:
Rolling back transaction           # 进行回滚
# 再查询数据表结果,未发生删除
> dbGetQuery(con1, "select count(*) from iris")
count
1     5

4. window客户端环境的中文乱码解决方案

执行查询,出现中文乱码。我们就需要逐步排查,到底是哪个环节产生了乱码,统一把编码以UTF-8做为编码标准。

# 创建中文data.frame
> acc<-data.frame(
+   user_id=1:3,
+   useranme=c("a1","小明","粉丝日志"),
+   password=c("678fdaiufda","jfdaked21+_~!","E!SM<I*")
+ )
# 建表,插入数据
> dbWriteTable(con1,"acc",acc)
[1] TRUE
# 读取数据
> df3<-dbReadTable(con1,"acc");df3
user_id     useranme      password
1       1           a1   678fdaiufda
2       2       灏忔槑 jfdaked21+_~!
3       3 绮変笣鏃ュ織       E!SM<I*

我们插入的是正确,但读出来的数据是乱码。我们用pgadmin客户端,检查一下数据表,验证一下数据库中数据是否显示正常。

接下来,再检查一下PostgreSQL数据库中testdb库的编码为UTF8。

在服务器上,启动psql查询客户端。

# 启动psql客户端
~ sudo -u postgres psql
# 检查数据库
postgres=# \l
List of databases
Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
|          |          |             |             | postgres=CTc/postgres
template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
|          |          |             |             | postgres=CTc/postgres
testdb    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres         +
|          |          |             |             | postgres=CTc/postgres+
|          |          |             |             | test=CTc/postgres
(4 rows)

再检查一下,客户端通信的字符编码,也是UTF8是正常的。

> dbGetQuery(con1, "SHOW CLIENT_ENCODING")
client_encoding
1            UTF8

最后,考虑到Windows环境下,R的客户端默认编码为GBK,所以我们再试一下,把数据在R中进行编码转型。

# 字符编码转型
> df3a <- as.data.frame(apply(df3, 2, function(x){
+   iconv(x, 'UTF-8', 'GBK')
+ }))
# 查看输出结果
> df3a
user_id useranme      password
1       1       a1   678fdaiufda
2       2     小明 jfdaked21+_~!
3       3 粉丝日志       E!SM<I*

这样就可以解决了,中文乱码的问题了。

我们已经完成,掌握了RPostgreSQL包的各种使用技巧,希望大家理解原理后,能少犯错误,提高工作效率!

转载请注明出处:

http://blog.fens.me/r-postgressql/

微信扫一扫,分享到朋友圈

RPostgreSQL数据库编程指南

[视频]以大象鼻为灵感 科学家研发可抓取220倍于自身重量的柔性抓手

上一篇

双11苹果手机无线充电器怎么选?苹果无线充电器品牌排行

下一篇

你也可能喜欢

RPostgreSQL数据库编程指南

长按储存图像,分享给朋友