R - 数据库

R - 现代数据库交互

虽然 R 在内存分析方面功能强大，但大多数实际数据都存储在关系型数据库中（例如 PostgreSQL、MySQL、SQL Server）。现代 R 提供了一种标准化框架，可以无缝、高效且安全地与这些数据库进行交互。

DBI 和 dbplyr 框架

数据库交互基于两个核心包：

• DBI (Database Interface)：提供了一套通用、一致的函数（dbConnect、dbGetQuery、dbWriteTable），适用于所有数据库。无论数据库后端是什么，您都可以编写相同的 R 代码。
• 后端驱动程序：一个特定的包，用于将 DBI 命令转换为特定数据库的命令。对于 MySQL/MariaDB，现代标准是 RMariaDB。
• dbplyr：一个 dplyr 后端，允许您编写熟悉的 dplyr 代码（例如 filter()、group_by()），它会将其转换为 SQL。这使您无需手动编写 SQL 即可利用数据库的处理能力。

设置：安装包

# 标准化的数据库接口
install.packages("DBI")

# 适用于 MySQL 和 MariaDB 的现代驱动程序
install.packages("RMariaDB")

# 用于在数据库中使用 dplyr 动词
install.packages("tidyverse")

# 用于安全地管理凭据
install.packages("config")

连接到数据库

**安全最佳实践：**切勿在脚本中硬编码凭据（用户名、密码、主机）。请使用配置文件或环境变量。我们将演示如何使用 config 包。

1. 创建 config.yml 文件

在您的项目目录下，创建一个名为 config.yml 的文件，其中包含您的数据库凭据：

config.yml

default:
  dbname: "sakila" # MySQL 的示例数据库
  host: "127.0.0.1"
  user: "root"
  password: "your_password_here"

2. 从 R 中连接

library(DBI)
library(RMariaDB)

# 从配置文件中安全地加载凭据
conf <- config::get()

# 创建连接对象
# 使用 tryCatch 块优雅地处理连接错误
con <- tryCatch({
  dbConnect(
    RMariaDB::MariaDB(),
    dbname = conf$dbname,
    host = conf$host,
    user = conf$user,
    password = conf$password
  )
}, error = function(e) {
  stop("数据库连接失败：", e$message)
})

cat("成功连接到数据库！\n")

# 列出表以验证连接
print(dbListTables(con))

使用 DBI 查询数据

dbGetQuery 函数发送 SQL 查询并将完整结果集作为数据帧检索。为了安全起见，请始终使用带 ? 占位符的参数化查询来防止 SQL 注入。

# 获取前 5 个演员的基本查询
actors_df <- dbGetQuery(con, "SELECT * FROM actor LIMIT 5")
print(actors_df)

# 安全的参数化查询
last_name_filter <- "TORN"
query <- "SELECT actor_id, first_name, last_name FROM actor WHERE last_name = ?"

torn_actors_df <- dbGetQuery(con, query, params = list(last_name_filter))
print(torn_actors_df)

使用 dbplyr 查询数据

最强大的现代工作流程是使用 dbplyr 编写在数据库上运行的 dplyr 代码。

library(dplyr)

# 1. 创建一个“远程”表对象。这不会将数据拉取到 R 中。
remote_film_table <- tbl(con, "film")

# 2. 编写 dplyr 代码。dbplyr 会将其转换为 SQL。
long_films_query <- remote_film_table %>%
  filter(length > 180, rating == "R") %>%
  select(film_id, title, length, rating) %>%
  arrange(desc(length))

# 3. 使用 `show_query()` 查看生成的 SQL
show_query(long_films_query)

# 4. 使用 `collect()` 执行查询并将结果拉取到 R 中
long_films_df <- collect(long_films_query)
print(head(long_films_df))

写入数据和管理表

DBI 包提供了安全且一致的函数，用于将数据帧写入表并管理它们。

# 示例：将 R 内置的 'mtcars' 数据集写入数据库
# `overwrite = TRUE` 将在表存在时删除它。请谨慎使用。
if (dbExistsTable(con, "mtcars_r")) {
  cat("表 'mtcars_r' 已存在。正在先将其删除。\n")
  dbRemoveTable(con, "mtcars_r")
}
dbWriteTable(con, "mtcars_r", mtcars, row.names = TRUE)
cat("已成功将 'mtcars' 写入表 'mtcars_r'。\n")

# 使用安全的参数化语句修改数据
# dbExecute 用于不返回结果的语句（UPDATE、INSERT、DELETE）
update_statement <- "UPDATE mtcars_r SET disp = ? WHERE hp = ?"
dbExecute(con, update_statement, params = list(170.0, 110))
cat("已更新 'mtcars_r' 表。\n")

# 完成后删除表
dbRemoveTable(con, "mtcars_r")
cat("已删除 'mtcars_r' 表。\n")

# 完成后务必断开连接
dbDisconnect(con)
cat("数据库连接已关闭。\n")