由于大部分业务信息都存储在数据库中。 无论使用哪种编程语言，都必须对它们执行各种操作。
在本文中，我将讨论在R中使用数据库的两个接口。大多数示例演示了如何使用Microsoft SQL Server，但是，所有代码示例都将与其他数据库一起使用，例如：MySQL，PostgreSQL，SQLite，ClickHouse ，Google BigQuery等。
目录内容
所需软件
为了重复使用本文中描述的使用DBMS的所有示例，您将需要下面列出的以下免费软件：
- 语言R ;
- RStudio开发环境；
- 数据库管理系统，可供选择：
3.1。 Microsoft SQL服务器
3.2。 的MySQL
3.3。 PostgreSQL的
DBI软件包
DBI软件包是与R中的数据库进行交互的最流行，最方便的方法。
DBI为您提供了一组可以用来管理数据库的功能。 但是，要连接到数据库，您需要安装其他软件包，这些软件包是各种数据库管理系统（DBMS）的驱动程序。
基本DBI功能列表
dbConnect与数据库的连接；dbWriteTable将表写入数据库；dbReadTable从数据库加载表；dbGetQuery加载查询执行的结果；dbSendQuery向数据库发送查询；dbFetch从结果集中提取元素；dbExecute执行更新/删除/向表中插入数据的请求；dbGetInfo请求有关请求或连接结果的信息；dbListFields请求表字段列表；dbListTables查询数据库表列表；dbExistsTable检查数据库中是否存在表；dbRemoveTable从数据库中删除表；dbDisconnect与数据库断开连接。
数据库连接
要与数据库进行交互，必须首先连接到它们。 根据您计划使用的DBMS，您将需要一个额外的软件包，下面是最常用的软件包的列表。
odbc通过ODBC接口进行连接的驱动程序；RSQLite -SQLite驱动程序；RMySQL / RMariaDB -MySQL和MariaDB的驱动程序；RPostgreSQL - RPostgreSQL驱动程序；bigrquery -Google BigQuery的驱动程序；RClickhouse / clickhouse - clickhouse驱动程序；RMSSQL用于Microsoft SQL Server（MS SQL）的驱动程序， 在撰写本文时仅在GitHub上提供 。
DBI软件包是基本R软件包随附的，但是必须使用install.packages(" ")命令install.packages(" ")安装作为数据库驱动程序的软件包。
要从GitHub安装软件包，您还需要其他软件包devtools 。 例如， RMSSQL软件包当前未发布在主R软件包存储库中，请使用以下代码进行安装：
install.packages("devtools") devtools::install_github("bescoto/RMSSQL")
使用odbc包连接到Microsoft SQL Server的示例
在R会话中使用任何软件包之前，必须首先使用library(" ")函数library(" ")进行连接。
我并非没有理由选择Microsoft SQL Server作为主要的DBMS，它将提供本文中的大多数示例。 事实是，这是一个相当流行的数据库，但与此同时，它仍然没有用于从CRAN上发布的R连接的驱动程序。
但是幸运的是，SQL Server与几乎所有其他数据库一样，都具有用于连接的ODBC （英语开放数据库连接）接口。 有许多软件包可通过R中的ODBC接口连接到DBMS。 首先，我们将研究通过odbc包进行连接。
通过odbc接口轻松连接数据库 # odbc install.packages("odbc") # library(odbc) # MS SQL con <- dbConnect(drv = odbc(), Driver = "SQL Server", Server = "localhost", Database = "mybase", UID = "my_username", PWD = "my_password", Port = 1433)
在dbConnect()函数中，您需要传递该函数作为drv的第一个参数，该函数是用于连接到DBMS的驱动程序（ odbc() ）。 通常将此类功能与DBMS称为相同功能，并且随附有作为DBI驱动程序的软件包。
接下来，您需要列出连接参数。 要通过ODBC连接到MS SQL，必须指定以下参数：
- 驱动程序-ODBC驱动程序的名称；
- 服务器-SQL服务器的IP地址；
- 数据库-要连接的数据库的名称；
- UID-数据库用户名；
- PWD-密码；
- 端口-与SQL Server连接的端口，默认端口为1433。
Windows附带有用于连接到Microsoft SQL Server的ODBC驱动程序，但它的名称可能不同。 您可以在ODBC数据源管理器中查看已安装驱动程序的列表。 您可以通过以下方式在Windows 10中启动数据源管理员：
- 32位版本：
%systemdrive%\Windows\SysWoW64\Odbcad32.exe - 64位版本：
%systemdrive%\Windows\System32\Odbcad32.exe
您还可以使用odbcListDrivers()函数获得PC上安装的所有驱动程序的列表。
name attribute value <chr> <chr> <chr> 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 7 SQL Server UsageCount 1 8 MySQL ODBC 5.3 ANSI Driver UsageCount 1 9 MySQL ODBC 5.3 Unicode Driver UsageCount 1 10 Simba ODBC Driver for Google BigQuery Description Simba ODBC Driver for Google BigQuery2.0 # ... with 50 more rows
您可以通过以下链接下载其他DBMS的ODBC驱动程序：
对于各种DBMS，用于连接的参数名称可能不同，例如：
- PostgreSQL / MySQL / MariaDB-用户，密码，主机，端口，dbname;
- GoogleBigQuery-项目，数据集；
- ClickHouse-用户，密码，数据库，端口，主机；
使用ODBC数据源管理员，您可以运行向导来创建ODBC数据源。 为此，只需打开管理员，转到“自定义DSN”选项卡，然后单击“添加...”按钮。
使用管理员创建数据源时，请给它命名DSN（数据源名称）。
在上面的示例中，我们使用DSN“ my_test_source”创建了一个数据源。 现在，我们可以使用此源连接到Microsoft SQL Server，而无需在代码中指定其他连接参数。
使用DSN通过odbc接口连接到数据库 # DSN con <- dbConnect(odbc(), DSN = "my_test_source", UID = "my_username", PWD = "my_password")
您可以使用odbcListDataSources()函数查看在PC上创建的所有ODBC数据源的名称。
name description <chr> <chr> 1 BQ Simba ODBC Driver for Google BigQuery 2 BQ_main Simba ODBC Driver for Google BigQuery 3 BQ ODBC Simba ODBC Driver for Google BigQuery 4 OLX Simba ODBC Driver for Google BigQuery 5 Multicharts Simba ODBC Driver for Google BigQuery 6 PostgreSQL35W PostgreSQL Unicode(x64) 7 hillel_bq Simba ODBC Driver for Google BigQuery 8 blog_bq Simba ODBC Driver for Google BigQuery 9 MyClientMSSQL SQL Server 10 local_mssql SQL Server 11 MSSQL_localhost SQL Server 12 my_test_source SQL Server 13 Google BigQuery Simba ODBC Driver for Google BigQuery
使用RMSSQL包连接到Microsoft SQL Server的示例
RMSSQL不在CRAN上发布，因此您可以使用devtools软件包从GitHub安装它。
install.packages("devtools") devtools::install_github("bescoto/RMSSQL")
使用DBI RMSSQL驱动程序的连接示例 # library(RJDBC) library(RMSSQL) library(DBI) # RMSSQL con <- dbConnect(MSSQLServer(), host = 'localhost', user = 'my_username', password = 'my_password', dbname = "mybase")
在大多数情况下，使用DBI包处理数据库时，将以这种方式进行连接。 即 安装所需的驱动程序包之一，将dbConnect函数作为drv参数的值传递，该驱动程序函数用于连接到所需的DBMS。
连接到MySQL，PostgreSQL，SQLite和BigQuery的示例 # MySQL library(RMySQL) con <- dbConnect(MySQL(), host = 'localhost', user = 'my_username', password = 'my_password', dbname = "mybase", host = "localhost") # PostrgeSQL library(RPostgreSQL) con <- dbConnect(PostgreSQL(), host = 'localhost', user = 'my_username', password = 'my_password', dbname = "mybase", host = "localhost") # PostrgeSQL library(RPostgreSQL) con <- dbConnect(PostgreSQL(), host = 'localhost', user = 'my_username', password = 'my_password', dbname = "mybase", host = "localhost") # SQLite library(RSQLite) # connection or create base con <- dbConnect(drv = SQLite(), "localhost.db") # Google BigQuery library(bigrquery) con <- dbConnect(drv = bigquery(), project = "my_proj_id", dataset = "dataset_name")
如何在R脚本中隐藏数据库密码
上面，我给出了一些示例，可用于连接到任何数据库，但是它们有一个缺点，这种形式将所有数据库访问（包括密码）都以文本形式存储在脚本本身中。
如果所有脚本都在PC上本地部署和运行，并且同时受密码保护，那么很可能不会出现问题。 但是，如果您与同一台服务器上的某人一起工作，那么将数据库中的密码存储在脚本文本中并不是最佳解决方案。
每个操作系统都有一个用于管理凭据的实用程序。 例如，在Windows上，这是凭据管理器。 您可以将用于通过keyring包连接到数据库的密码添加到此存储中。 该软件包是跨平台的，以上示例可在任何操作系统上运行，至少在Windows，MacOS和Linux上适用。
# install.packages("keyring") # library(keyring) library(RMSSQL) # key_set_with_value(service = "mssql", username = "my_username", password = "my_password") # RMSSQL con <- dbConnect(MSSQLServer(), host = 'localhost', user = 'my_username', password = key_get("mssql", "my_username"), dbname = "mybase")
即 使用key_set_with_value()函数，可以将密码添加到凭据存储中，然后使用key_get()请求密码，只有将其添加到存储中的用户才能请求密码。 使用keyring您不仅可以存储数据库中的密码keyring还可以存储任何服务中的密码以及使用API时的授权令牌。
创建表并写入数据库
写入数据库是通过dbWriteTable()函数执行的。
dbWriteTable()函数的参数：
必填参数为粗体，斜体为可选
dbConnect使用dbConnect函数创建的DBMS连接对象；- name -DBMS中要写入数据的表的名称；
- value -R中的表（data.frame / data.table / tibble_frame类的对象），该表中的数据将被写入DBMS；
- row.names-将具有行号的row_names列添加到表中， 默认为FALSE 。
- overwrite-如果DBMS中已经存在具有name参数中指定名称的表，则覆盖该表； 默认值为FALSE ；否则为false 。
- append-如果DBMS中已经存在具有在name参数中指定的名称的表，则追加数据； 默认值为FALSE ；否则为false 。
- field.types-接受命名向量，并在写入DBMS时在每个字段中设置数据类型， 默认为NULL ；
- 临时 -允许您在DBMS中创建临时表，直到断开与数据库的连接为止，这些表才可用； 默认值为FALSE 。
通过DBI将数据写入DBMS的示例 # library(odbc) # DSN con <- dbConnect(odbc(), DSN = "my_test_source", UID = "my_username", PWD = "my_password") # iris, R iris dbWriteTable(conn = con, name = "iris", value = iris) # dbDisconnect(con)
要查看数据库中的表，请使用dbListTables()函数，删除表dbRemoveTable()
查询表列表并从DBMS删除表的示例 # library(odbc) # con <- dbConnect(odbc(), DSN = "my_test_source", UID = "my_username", PWD = "my_password") # dbListTables(con) # iris dbRemoveTable(con, "iris") # dbDisconnect(con)
从DBMS读取数据
使用DBI您可以查询整个表或执行SQL查询的结果。 dbReadTable()和dbGetQuery()函数用于执行这些操作。
从DBMS查询虹膜表的示例 # library(odbc) # con <- dbConnect(odbc(), DSN = "my_test_source", UID = "my_username", PWD = "my_password") # iris iris dbiris <- dbReadTable(con, "iris") # dbDisconnect(con)
从DBMS加载SQL执行结果的示例 # library(odbc) # con <- dbConnect(odbc(), DSN = "my_test_source", UID = "my_username", PWD = "my_password") # setosa <- dbGetQuery(con, "SELECT * FROM iris WHERE Species = 'setosa'") # dbDisconnect(con)
DBMS（DML）中的数据处理
上面dbGetQuery()的dbGetQuery()函数专门用于查询数据样本（SELECT）。
对于诸如UPDATE，INSERT，DELETE之类的数据操作操作， DBI存在dbExecute()函数。
在DBMS中处理数据的示例代码 # library(odbc) # con <- dbConnect(odbc(), DSN = "my_test_source", UID = "my_username", PWD = "my_password") # (INSERT) dbExecute(con, "INSERT INTO iris (row_names, [Sepal.Length], [Sepal.Width], [Petal.Length], [Petal.Width], [Species]) VALUES (51, 5.0, 3.3, 1.7, 0.3, 'new_values')") # (UPDATE) dbExecute(con, "UPDATE iris SET [Species] = 'old_value' WHERE row_names = 51") # (DELETE) dbExecute(con, "DELETE FROM iris WHERE row_names = 51") # dbDisconnect(con)
DBMS中的事务
事务是顺序的读写操作。 事务的结尾可以是保存更改（提交，提交）或取消更改（回滚，回滚）。 关于数据库，事务是一系列查询，被视为单个查询。
引用文章“交易及其控制机制”
事务将几个SQL语句封装在一个基本单元中。 在DBI事务的开始由dbBegin()启动，然后由dbCommit()确认或由dbRollback()取消。 在任何情况下，DBMS都保证：将所有语句或全部语句都不应用于数据。
例如，在交易过程中，让我们向虹膜表添加51行，然后将Sepal.Width值更改为5行，并从表中删除43行。
交易代码示例 # library(odbc) # con <- dbConnect(odbc(), DSN = "my_test_source", UID = "my_username", PWD = "my_password") # dbGetQuery(con, "SELECT * FROM iris WHERE row_names IN (5, 43, 51)") # row_names Sepal.Length Sepal.Width Petal.Length Petal.Width Species # 1 5 5.0 3.6 1.4 0.2 setosa # 2 43 4.4 3.2 1.3 0.2 setosa # 3 51 7.0 3.2 4.7 1.4 versicolor # dbBegin(con) # dbExecute(con, "INSERT INTO iris (row_names, [Sepal.Length], [Sepal.Width], [Petal.Length], [Petal.Width], [Species]) VALUES (51, 5.0, 3.3, 1.7, 0.3, 'new_values')") # dbExecute(con, "UPDATE iris SET [Sepal.Width] = 8 WHERE row_names = 5") # 43 dbExecute(con, "DELETE FROM iris WHERE row_names = 43") # dbCommit(con) # dbGetQuery(con, "SELECT * FROM iris WHERE row_names IN (5, 43, 51)") # row_names Sepal.Length Sepal.Width Petal.Length Petal.Width Species # 1 5 5 8.0 1.4 0.2 setosa # 2 51 7 3.2 4.7 1.4 versicolor # 3 51 5 3.3 1.7 0.3 new_values
交易取消代码示例 # library(odbc) # con <- dbConnect(odbc(), DSN = "my_test_source", UID = "my_username", PWD = "my_password") # dbGetQuery(con, "SELECT * FROM iris WHERE row_names IN (5, 43, 51)") # row_names Sepal.Length Sepal.Width Petal.Length Petal.Width Species # 1 5 5.0 3.6 1.4 0.2 setosa # 2 43 4.4 3.2 1.3 0.2 setosa # 3 51 7.0 3.2 4.7 1.4 versicolor # dbBegin(con) # dbExecute(con, "INSERT INTO iris (row_names, [Sepal.Length], [Sepal.Width], [Petal.Length], [Petal.Width], [Species]) VALUES (51, 5.0, 3.3, 1.7, 0.3, 'new_values')") # dbExecute(con, "UPDATE iris SET [Sepal.Width] = 8 WHERE row_names = 5") # 43 dbExecute(con, "DELETE FROM iris WHERE row_names = 43") # dbRollback(con) # dbGetQuery(con, "SELECT * FROM iris WHERE row_names IN (5, 43, 51)") # row_names Sepal.Length Sepal.Width Petal.Length Petal.Width Species # 1 5 5.0 3.6 1.4 0.2 setosa # 2 43 4.4 3.2 1.3 0.2 setosa # 3 51 7.0 3.2 4.7 1.4 versicolor
RODBC套件
RODBC软件包提供了一个独立的接口，用于通过ODBC接口连接和使用DBMS。
RODBC不兼容DBI ，即 您不能在DBI软件包提供的函数中使用使用RODBC创建的连接对象。
RODBC软件包的主要功能
odbcConnect通过DSN连接到DBMS；odbcDriverConnect通过连接字符串连接到数据库；sqlQuery将查询发送到DBMS，并获取其执行结果。 支持任何类型的查询：SELECT，UPDATE，INSERT，DELETE。sqlFetch从DBMS获取整个表；sqlTables获取数据库中表的列表。sqlSave在数据库中创建一个新表，或向现有表中添加新数据；sqlUpdate更新DBMS中已经存在的表中的数据；sqlDrop删除DBMS中的表；odbcClose终止与DBMS的连接。
使用RODBC的示例
在我看来， RODBC功能不如DBI ，但它具有使用DBMS所需的所有必要功能。
通过RODBC与DBMS交互的示例 # library(RODBC) # con_string <- odbcDriverConnect(connection = "Driver=SQL Server;Server=localhost;Database=mybase;UID=my_username;PWD=my_password;Port=1433") # DSN con_dsn <- odbcConnect(dsn = "my_test_source", uid = "my_username", pwd = "my_password") # sqlSave(con_dsn, dat = iris, tablename = "iris") # iris sqlSave(con_dsn, dat = iris, tablename = "iris", append = TRUE) # 4 sqlFetch(con_dsn, "iris", rownames = FALSE, max = 4) # R iris[1, 5] <- "virginica" # sqlUpdate(con_dsn, dat = iris, tablename = "iris") # 4 sqlFetch(con_dsn, "iris", rownames = FALSE, max = 4) # sqlDrop(con_dsn, sqtable = "iris") # odbcCloseAll()
交易性
默认情况下， RODBC事务性处于关闭状态。 交易管理由两个功能执行。
odbcSetAutoCommit在正常和事务DBMS操作模式之间切换；odbcEndTran确认或取消交易。
odbcSetAutoCommit函数使用autoCommit参数打开和关闭事务模式。
在RODBC中以事务模式工作的示例 # library(RODBC) # DSN con_dsn <- odbcConnect(dsn = "my_test_source", uid = "my_username", pwd = "my_password") # sqlSave(con_dsn, dat = iris, tablename = "iris") # odbcSetAutoCommit(con_dsn, autoCommit = FALSE) # 4 sqlFetch(con_dsn, "iris", rownames = FALSE, max = 4) # rownames SepalLength SepalWidth PetalLength PetalWidth Species # 1 1 5.1 3.5 1.4 0.2 setosa # 2 2 4.9 3.0 1.4 0.2 setosa # 3 3 4.7 3.2 1.3 0.2 setosa # 4 4 4.6 3.1 1.5 0.2 setosa # R iris[1, 5] <- "virginica" # sqlUpdate(con_dsn, dat = iris, tablename = "iris") # 4 sqlFetch(con_dsn, "iris", rownames = FALSE, max = 4) # rownames SepalLength SepalWidth PetalLength PetalWidth Species # 1 1 5.1 3.5 1.4 0.2 virginica # 2 2 4.9 3.0 1.4 0.2 setosa # 3 3 4.7 3.2 1.3 0.2 setosa # 4 4 4.6 3.1 1.5 0.2 setosa # odbcEndTran(con_dsn, commit = FALSE) # 4 sqlFetch(con_dsn, "iris", rownames = FALSE, max = 4) # rownames SepalLength SepalWidth PetalLength PetalWidth Species # 1 1 5.1 3.5 1.4 0.2 setosa # 2 2 4.9 3.0 1.4 0.2 setosa # 3 3 4.7 3.2 1.3 0.2 setosa # 4 4 4.6 3.1 1.5 0.2 setosa # sqlUpdate(con_dsn, dat = iris, tablename = "iris") # odbcEndTran(con_dsn, commit = TRUE) # 4 sqlFetch(con_dsn, "iris", rownames = FALSE, max = 4) # rownames SepalLength SepalWidth PetalLength PetalWidth Species # 1 1 5.1 3.5 1.4 0.2 virginica # 2 2 4.9 3.0 1.4 0.2 setosa # 3 3 4.7 3.2 1.3 0.2 setosa # 4 4 4.6 3.1 1.5 0.2 setosa # odbcClose(con_dsn)
结论
本文介绍的使用R， DBI和RODBC语言处理数据库的两种方法非常通用，几乎可以与任何DBMS一起使用。
不同DBMS之间的唯一操作区别是连接过程。 对于大多数流行的DBMS，有单独的程序包是驱动程序。 对于其余的DBMS，必须使用odbc或RODBC通过ODBC接口配置连接。 不管您选择了哪种DBMS，其他所有操作都将保持不变。 根据正在使用的DBMS支持的SQL方言，发送SQL查询是一个例外。