在R中使用DBI连接PostgreSQL及解决连接报错与dbplyr适配问题

问题根因 #

• DBI连接报错的核心原因是odbc包的dbConnect参数和PostgreSQL ODBC驱动要求的参数不匹配，驱动没有识别到你传入的host参数，默认尝试连接本地PostgreSQL服务，才会出现Unix套接字找不到的报错
• 你之前用odbcDriverConnect生成的是RODBC标准的连接对象，不符合dbplyr要求的DBI连接规范，所以无法适配dbplyr

方案1：修正DBI连接参数 #

将参数名对齐PostgreSQL ODBC驱动的要求，额外加上验证可用的PROTOCOL=TCPIP参数即可，代码如下：

library(DBI)
library(dplyr)
library(dbplyr)
library(odbc)

# 运行前先关闭之前残留的RODBC连接，避免警告
odbcCloseAll()

dbicon <-  DBI::dbConnect(
               odbc::odbc(),
               Driver = "PostgreSQL Unicode",
               Database = "Postgres_xyz_db",
               Server = "some_xyz.amazonaws.com",
               Port = "5432",
               UID = "user",
               PWD = "users_password",
               PROTOCOL = "TCPIP")

# 带schema的表用in_schema写法，避免转义错误
tbl_qry <- tbl(dbicon, in_schema("mydb_demo", "demo_table"))
tbl_qry %>% head()

方案2：直接复用现有可用连接串 #

如果方案1仍有问题，可以直接把已经验证可用的连接字符串传给DBI，不需要拆分参数，完全避免参数写错的问题：

library(DBI)
library(dplyr)
library(dbplyr)
library(odbc)

# 运行前先关闭之前残留的RODBC连接，避免警告
odbcCloseAll()

driver.name <- "PostgreSQL Unicode"
db.name <- "Postgres_xyz_db"
host.name <- "some_xyz.amazonaws.com" 
port <-"5432"
user.name <-"user"
pwd <- "users_password"

con.text <- paste("DRIVER=",driver.name,
                  ";Database=",db.name,
                  ";Server=",host.name,
                  ";Port=",port,
                  ";PROTOCOL=TCPIP",
                  ";UID=", user.name,
                  ";PWD=",pwd,sep="")

# 直接传入连接串生成符合DBI标准的连接对象
dbicon <- DBI::dbConnect(odbc::odbc(), .connection_string = con.text)

# 测试查询
tbl_qry <- tbl(dbicon, in_schema("mydb_demo", "demo_table"))
tbl_qry %>% head()

内容的提问来源于stack exchange，提问作者ViSa