Blog Archives

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/