8.3 DBI包
R包DBI将与DBMS16的连接分为“前端”和“后端”，DBI定义了一个由DBI后端实现的接口,接口定义了一些类和方法,支持以下一些操作：
- 连接/断开与DBMS的连接
- 在DBMS中创建和执行语句
- 从语句中提取结果
通过DBI可以连接大部分主流关系型数据库，如:PostgreSQL,SQL Server,Oralce,MySQL,SQLite。
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() 查看Database Management System,即数据库管理系统↩︎