8.2 DBI

8.2.1 安装

install.packages('DBI')

8.2.2 连接数据库

• 连接MS 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"
)

如果你用windows系统，通过DBI包连接数据库发现乱码时，根据数据库编码指定encoding参数即可，常规在win下连接sqlserver设置encoding = “GBK”。

library(DBI)
#根据数据库编码方式指定encoding
con <- dbConnect(
  drv = odbc::odbc(), Driver = "SQL Server", server = "172.16.88.2",
  database = "spb", uid = "zhongyf", pwd = "Zyf123456", encoding = "GBK"
)
# 查看本机可用驱动 如缺少相应驱动则安装，ODBC Driver 17 for SQL Server 就是个人安装的驱动

Drivers_tbl <- odbc::odbcListDrivers() 
head(Drivers_tbl)

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

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端口是否开放。

8.2.3 执行sql任务

dbGetQuery()函数处理由DBI包创建的con连接查询任务,dbExecute()执行一些数据库任务

# 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') #类似任务

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

8.2.4 函数介绍

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

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

#查看数据版本连接信息
dbGetInfo(con)

# 数据库中的全部表名
dbListTables(con) #win下中文表名还是会乱码

# 删除表
dbRemoveTable(con,'tbl_name')

# 关闭连接
dbDisconnect(con)