8.3 DBI包

R包DBI将与DBMS16的连接分为“前端”和“后端”,DBI定义了一个由DBI后端实现的接口,接口定义了一些类和方法,支持以下一些操作:

  • 连接/断开与DBMS的连接
  • 在DBMS中创建和执行语句
  • 从语句中提取结果

通过DBI可以连接大部分主流关系型数据库,如:PostgreSQL,SQL Server,Oralce,MySQL,SQLite。

DBI介绍

8.3.1 安装

从CRAN安装发行的DBI版本:

install.packages('DBI')

从Github安装开发的DBI版本:

# install.packages("devtools")
devtools::install_github("r-dbi/DBI")

8.3.2 连接数据库

  • 连接MS SQL SERVER

通过以下代码即可连接微软的sql server,服务器172.16.88.2(即IP地址)的数据库,成功连接后即可与数据库交互。

library(DBI)
con <- dbConnect(
  drv = odbc::odbc(), 
  Driver = "SQL Server", 
  server = "172.16.88.2",
  database = "spb", 
  uid = "zhongyf", 
  pwd = "Zyf123456"
)

查询数据库编码方式,从而选择连接数据库时相应的编码方式。

con <- dbConnect(
  drv = odbc::odbc(), Driver = "ODBC Driver 17 for SQL Server",
  server = "172.16.88.2", database = "spb", uid = "zhongyf", pwd = "Zyf123456"
)

#查看编码是否是936 代表中文简体
sql <- "SELECT COLLATIONPROPERTY( 'chinese_prc_ci_as', 'codepage' )"

dbGetQuery(con,sql)

# same above
# dbExecute(con,sql)

# 关闭数据库连接
DBI::dbDisconnect(con)
  • 连接mysql

MySQL()函数来源RMySQL包,用来创建<MySQLDriver>驱动,以下代码可连接到阿里云的MySQL数据库。

library(RMySQL)
con <- dbConnect(MySQL(),
  dbname = "test", user = "test_admin", password = "30HL1234M7#¥lD6gxjB",
  host = "prd-public-mypersonal.mysql.test.zhangjiabei.rds.aliyuncs.com"
)

或者通过本地已安装驱动连接数据库

con <- DBI::dbConnect(odbc::odbc(),
  Driver = "MySQL ODBC 8.0 Unicode Driver",
  Server = "localhost", UID = "root", PWD = "123456", Database = "mysql",
  Port = 3306
)

mysql数据库默认端口是3306,访问不通时记得检查3306端口是否开放。

  • 连接oracle

由ROracle包构造驱动。

library(ROracle)
drv <-dbDriver("Oracle")
connect.string <- '(DESCRIPTION =
                    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.16.88.129)(PORT = 1521))
                  (CONNECT_DATA =
                      (SERVER = DEDICATED)
                    (SERVICE_NAME = bidev)
                  ))' #连接字符串

con <- dbConnect(drv,username = "query", password = "query",dbname = connect.string)

8.3.3 读写数据库

dbGetQuery()函数查询数据并直接返回,dbExecute()执行sql语句,是dbSendStatement()的封装。

# dbGetQuery 直接查询
res_table <- dbGetQuery(con,'select * from table') #直接获取sql查询结果

#dbReadTable直接读取
dbReadTable(con,'tbl_name') #直接读取数据库中某表

# dbSendQuery 执行一个查询任务 
res <- dbSendQuery(conn = con,statement = 'select * FROM tab')
dbFetch(res)
dbClearResult(res)

# dbExecute
dbExecute(con,'delete from table where num <=1000') # 执行sql语句

# dbWriteTable()
# 上传数据,指定表名,需上传的数据框df,overwrite是否覆盖,append是否可追加
dbWriteTable(conn = con,name = '表名',value = df,overwrite=TURE,append=FALSE)

参数化查询

library(DBI)
con <- dbConnect(RSQLite::SQLite(), ":memory:")
dbWriteTable(con, "iris", iris)

iris_result <- dbSendQuery(con, "SELECT * FROM iris WHERE [Petal.Width] > ?")
dbBind(iris_result, list(2.3))
dbFetch(iris_result)
#>   Sepal.Length Sepal.Width Petal.Length Petal.Width   Species
#> 1          6.3         3.3          6.0         2.5 virginica
#> 2          7.2         3.6          6.1         2.5 virginica
#> 3          5.8         2.8          5.1         2.4 virginica
#> 4          6.3         3.4          5.6         2.4 virginica
#> 5          6.7         3.1          5.6         2.4 virginica
#> 6          6.7         3.3          5.7         2.5 virginica
dbBind(iris_result, list(3))
dbFetch(iris_result)
#> [1] Sepal.Length Sepal.Width  Petal.Length Petal.Width  Species     
#> <0 行> (或0-长度的row.names)
dbClearResult(iris_result)
dbDisconnect(con)

实际运用中,我们从数据库(SQL Server)查询不同订单周期时的销售数据:

con <- dbConnect(odbc::odbc(), .connection_string = "driver={ODBC Driver 17 for SQL Server};server=172.16.88.2;database=test;uid=zhongyf;pwd=Zyf123456", timeout = 10)

result <- dbSendQuery(con, "SELECT * FROM sales_order WHERE [单据日期] between ? and ? ") # ?在本处是占位符
dbBind(result,params = list('2021-01-01','2021-01-05'))
dbFetch(result)

dbBind(result,params = list('2021-05-01','2021-05-05'))
dbFetch(result)
dbClearResult(result)

以上方式可以实现参数化查询,减少代码量,但是由于查询速度相比dbGetQuery()较慢,我一般不采用。 关于sql语句中?的用法请自行查阅?dbBind()。DBI当前未指定占位符格式,将来可能会支持统一的占位符语法

8.3.4 其它函数介绍

查看数据库信息,查看表名,删除表,关闭连接等常用操作.

  • 查看数据库信息
dbGetInfo(con)
#> $db.version
#> [1] "3.35.5"
#> 
#> $dbname
#> [1] ":memory:"
#> 
#> $username
#> [1] NA
#> 
#> $host
#> [1] NA
#> 
#> $port
#> [1] NA
  • 数据库读写权限

返回连接账户是否只有只读权限。

dbIsReadOnly(con)
#> [1] FALSE
  • 查看全部表名

win下SQL Server中文表名会乱码

dbListTables(con) 
#> [1] "flights" "测试表"
  • 读取表
dt <- dbReadTable(con,'测试表')
head(dt)
#>   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
  • 查询
rs <- dbSendQuery(con, "SELECT * from flights limit 100 ")
dt <- dbFetch(rs)
head(dt)
#>   year month day dep_delay arr_delay carrier origin dest air_time distance hour
#> 1 2014     1   1        14        13      AA    JFK  LAX      359     2475    9
#> 2 2014     1   1        -3        13      AA    JFK  LAX      363     2475   11
#> 3 2014     1   1         2         9      AA    JFK  LAX      351     2475   19
#> 4 2014     1   1        -8       -26      AA    LGA  PBI      157     1035    7
#> 5 2014     1   1         2         1      AA    JFK  LAX      350     2475   13
#> 6 2014     1   1         4         0      AA    EWR  LAX      339     2454   18
dbClearResult(rs) # 记得清除结果
  • 是否存在表
dbExistsTable(con,'测试表') 
#> [1] TRUE
  • 创建表
con <- dbConnect(RSQLite::SQLite(), ":memory:")
dbCreateTable(con, "iris", iris)
dbAppendTable(con, "iris", iris)
dbReadTable(con, "iris")
dbDisconnect(con)
  • 删除表
dbRemoveTable(con,'测试表')
# same above
# dbExecute(con,'delete from 测试表')
  • 是否有效
dbIsValid(con)

rs <- dbSendQuery(con, "SELECT * from flights")
dbIsValid(rs)
dbClearResult(rs)
dbIsValid(rs)
  • 执行任务

执行语句,返回受影响的行数。

dbExecute(conn, statement, ...)
dbExecute(con,'delete from 测试表')
  • 关闭连接

在完成连接后,最后关闭连接。

dbDisconnect(con)

通过学习odbc,DBI包,我们已经对R与数据库交互有一定了解。连接数据库并返回我们查询的结果,最后上传数据到数据库中,以上三步能正确使用即可满足我们数据分析师绝大部分需求。但是我们可能会对odbc与DBI的关系感到困惑,由于我并不太了解数据库,仅谈谈自己的理解,不一定正确。

以上三步,配合R的定时任务,可以完成ETL(Extract-Transform-Load).

odbc提供了与odbc类数据库的连接方式,odbc基于DBI包,我们在安装odbc包时会自动安装DBI包。odbc负责连接odbc类数据库,DBI提供后端服务,换句话说odbc连接数据库,其它的交互通过DBI包完成。通过比较以下连接方式来理解:

# 方式一  odbc 
con <- DBI::dbConnect(odbc::odbc(),
  Driver = "MySQL ODBC 8.0 Unicode Driver",
  Server = "localhost", UID = "root", PWD = "123456", Database = "test",
  Port = 3306
)

# 方式二 RMySQL
library(RMySQL)
con <- dbConnect(MySQL(),
  dbname = "test", user = "root", password = "123456",
  host = "localhost"
)

方式二与方式一都可以成功连接本地的mysql数据库,方式二是通过RMySQLB包的MySQL()功能提供的驱动程序。

但是不同的连接方式可能导致读写效率有明显的差异,详见oracle连接差异。

library(ROracle)
# 方式一
# use ROracle packages connect databse
drv <-dbDriver("Oracle")
connect.string <- '(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.88.131)(PORT = 1521))(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = ghbi) ))'
con_oracle <- dbConnect(drv,username = "pub_query", password = "pub_query",dbname = connect.string,encoding='utf8')

# 方式二
# use odbc packages connect databse
con_odbc <- dbConnect(odbc::odbc(), .connection_string = "Driver={Oracle in OraClient11g_home1};DBQ=172.16.88.131:1521/ghbi;UID=pub_query;PWD=pub_query;", timeout = 10)

# Oracle in OraClient11g_home1 根据实际驱动更改
# 通过odbc::odbcListDrivers() 查看

  1. Database Management System,即数据库管理系统↩︎