8.2 odbc包

官方说明:The goal of the odbc package is to provide a DBI-compliant interface to Open Database Connectivity (ODBC) drivers. This allows for an efficient, easy to setup connection to any database with ODBC drivers available, including SQL Server, Oracle, MySQL, PostgreSQL, SQLite and others. The implementation builds on the nanodbc C++ library。

odbc可以运用于包括(SQL Server, Oracle, MySQL,PostgreSQL,SQLite)等odbc驱动程序于DBI兼容的接口，相比起来DBI包适用范围更广。

odbc项目地址

8.2.1 安装包

  • Windows

Windows与ODBC库捆绑在一起，但是每个数据库的驱动程序需要单独安装。windows系统上可以通过以下命令直接安装。

#安装包
install.packages('odbc')
  • 其它系统

其它系统在安装前需要安装依赖环境，如下所示:

 # Macos
 brew install unixodbc
 
 # Debian /Ubuntu
 apt-get install unixodbc unixodbc-dev

8.2.2 驱动安装

由于Win系统绑定ODBC库，不需要安装依赖环境即可正常安装使用R包odbc，其它系统经过上面依赖环境安装，现在系统已经能正常安装odbc包。

最后通过odbc::odbcListDrivers()查看系统上是否存在可用驱动程序。

查看可用系统可用驱动：

head(odbc::odbcListDrivers())
#>         name        attribute value
#> 1 SQL Server         APILevel     2
#> 2 SQL Server ConnectFunctions   YYY
#> 3 SQL Server        CPTimeout    60
#> 4 SQL Server    DriverODBCVer 03.50
#> 5 SQL Server        FileUsage     0
#> 6 SQL Server         SQLLevel     1

1.Windows

默认情况下，win系统即使不安装也可使用驱动“SQL Server,”如下所示：

win-drivers

如果想要安装最新的SQL Server驱动，该如何安装呢?具体安装教程请查看微软安装说明

win系统最新的SQL Server驱动ODBC Driver 17 for SQL Server下载地址

其它系统上如果安装sql server驱动请参照安装说明

2.Macos

Macos安装驱动

brew install unixodbc

# Microsoft SQL Server ODBC Drivers (Recommended)
brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release
brew update
brew install msodbcsql17 mssql-tools

成功安装后如下所示:

mac-odbcdriver

8.2.3 连接数据库

连接数据库需要注意时区、编码，尤其是涉及到时间时区时如果设置有误，可能导致时间数据错误。当你在Win系统上连接Sql Server时，如果你使用的数据库是中文环境时，设置encoding参数中文将不会乱码。如果是linux上通过odbc连接SqlServer,一般情况下可以不用设置编码，另外设置encoding为gbk会报错。

数据库连接有几种方式，详情请参照数据库连接字符串,我们以SQL Server2019数据库，驱动ODBC Driver 17 for SQL Server举例。

1.标准模式

连接字符串

Driver={ODBC Driver 17 for SQL Server};Server=myServerAddress;Database=myDataBase;UID=myUsername;PWD=myPassword;

参数形式:

con <- odbc::dbConnect(drv=odbc(),driver="ODBC Driver 17 for SQL Server",server='172.16.88.2',database='spb',uid='zhongyf',pwd='Zyf123456',encoding='UTF-8',timezone="Asia/Shanghai")

字符串形式：

con <- odbc::dbConnect(drv = odbc(),.connection_string='Driver={ODBC Driver 17 for SQL Server};Server=172.16.88.2;Database=spb;UID=zhongyf;PWD=Zyf123456;',encoding='UTF-8',timezone="Asia/Shanghai")

2.信任连接

通过windows身份认证连接，通过数据库管理员配置相关账户权限即可。

Driver={ODBC Driver 17 for SQL Server};Server=myServerAddress;Database=myDataBase;Trusted_Connection=yes;
library(odbc)
con <- odbc::dbConnect(odbc(),
  Driver = "SQL Server", Server = "Vega", Database = "ghzy",
  Trusted_Connection = "True"
)

3.连接SQL Server实例15

Driver={ODBC Driver 17 for SQL Server};Server=serverName\instanceName;
Database=myDataBase;Trusted_Connection=yes;

4.非标准端口

SQL Server数据库的默认端口是1433，连接非标端口字符串如下所示：

Driver={ODBC Driver 17 for SQL Server};Server=myServerName,myPortNumber;Database=myDataBase;UID=myUsername;PWD=myPassword;

通过配置DSN也可连接数据库，但是这样写的脚本在同事间分享不方便，故不推荐。

win10开始–>运行 打开ODBC数据源(64) 配置。

odbc-dsn

con <- dbConnect(odbc::odbc(), dsn="ghzy")
dbGetInfo(con) #查看连接信息


$dbname
[1] "spb"

$dbms.name
[1] "Microsoft SQL Server"

$db.version
[1] "12.00.5203"

$username
[1] "dbo"

$host
[1] ""

$port
[1] ""

$sourcename
[1] "ghzy"

$servername
[1] "personalSQL"

$drivername
[1] "msodbcsql17.dll"

$odbc.version
[1] "03.80.0000"

$driver.version
[1] "17.05.0002"

$odbcdriver.version
[1] "03.80"

$supports.transactions
[1] TRUE

$getdata.extensions.any_column
[1] FALSE

$getdata.extensions.any_order
[1] FALSE

attr(,"class")
[1] "Microsoft SQL Server" "driver_info"          "list"

8.2.4 读写数据库

1.查询

odbc::dbGetQuery()函数执行sql语句并返回数据

dt <- odbc::dbGetQuery(con,'select * from DT')
head(dt)

2.写入数据库

odbc::dbWriteTable(con,name = '表名',value = dt,overwrite = T ) # 是否覆盖
odbc::dbWriteTable(con,name = '表名',value = dt,append = T ) # 是否追加

由于odbc包的函数都是DBI中一些函数的实现，我们将其它的一些函数放在接下来的DBI包中讲解。

综上，我们使用odbc包主要是作为连接开放式数据库(odbc)中介使用。

  1. 在一台计算机上可以安装多个SQL Server,每个SQL Server就是一个实例，实例分为默认实例和命令实例。↩︎