R Academy · 课时

DBI 与 RSQLite 基础

Q: 「DBI 与 RSQLite 基础」这节课中我会学到什么？

使用 DBI 连接 SQLite 数据库、运行查询并获取结果 你通过在浏览器中直接运行的动手代码来练习 R Academy，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

使用 DBI 连接 SQLite 数据库、运行查询并获取结果

DBI 与 RSQLite 基础 是 CoddyKit 上的免费 R Academy 课时。这是第 1 节课，共 4 节。你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。这是 R Academy 学习路径的一部分，你的进度在网页和 CoddyKit 应用中同步。 R Academy 课程共包含 4 节课。

使用 DBI 在 R 中访问数据库

DBI 软件包为 R 中的数据库访问提供统一接口。将 DBI 与后端驱动软件包结合后，同一组函数可以用于不同的数据库后端（SQLite、PostgreSQL、MySQL 等）。RSQLite 是最易上手的后端，无需安装服务器。

library(DBI)
library(RSQLite)

# DBI + RSQLite: connect to an in-memory SQLite database
# ':memory:' creates a fresh database in RAM
con <- dbConnect(RSQLite::SQLite(), ':memory:')

cat('Connection class:', class(con), '\n')
cat('Database backend:', dbGetInfo(con)$dbname, '\n')

# Always close connection when done
dbDisconnect(con)
cat('Connection closed.')

dbConnect() — 创建连接

dbConnect(drv, ...) 创建数据库连接。第一个参数是驱动对象。对于 SQLite，应使用 RSQLite::SQLite()。其他参数（例如数据库文件路径）取决于后端。

library(DBI)
library(RSQLite)

# In-memory database (disappears when connection closes)
con_mem <- dbConnect(RSQLite::SQLite(), ':memory:')

# File-based SQLite database (persists to disk)
# con_file <- dbConnect(RSQLite::SQLite(), '/tmp/mydb.sqlite')

# Always wrap connections in tryCatch or use on.exit()
on.exit(dbDisconnect(con_mem))

cat('Connected! Is valid:', dbIsValid(con_mem))

dbWriteTable() — 将数据加载到数据库

dbWriteTable(con, 'table_name', df) 创建一个表，并将数据框加载到其中。设置 overwrite=TRUE 可替换现有表，设置 append=TRUE 可向现有表添加行。

library(DBI)
library(RSQLite)

con <- dbConnect(RSQLite::SQLite(), ':memory:')

# Load a data frame into the database
students <- data.frame(
  id = 1:5,
  name = c('Alice','Bob','Carol','Dave','Eve'),
  score = c(85, 92, 78, 88, 95)
)

dbWriteTable(con, 'students', students)

# Verify: list tables
cat('Tables in DB:', dbListTables(con), '\n')

dbDisconnect(con)

dbListTables() 和 dbListFields()

dbListTables(con) 返回已连接数据库中所有表的名称。dbListFields(con, 'table') 返回指定表的列名。您可以使用它们来探索未知数据库。

library(DBI)
library(RSQLite)

con <- dbConnect(RSQLite::SQLite(), ':memory:')
dbWriteTable(con, 'students', data.frame(id=1:3, name=c('A','B','C'), score=c(80,90,85)))
dbWriteTable(con, 'courses', data.frame(code=c('R101','R201'), title=c('Intro','Advanced')))

cat('Tables:', paste(dbListTables(con), collapse=', '), '\n')
cat('Students fields:', paste(dbListFields(con, 'students'), collapse=', '), '\n')

dbDisconnect(con)

dbGetQuery() — 执行 SQL 并返回数据

dbGetQuery(con, sql) 执行 SELECT 查询，并将结果作为数据框返回。这是从数据库读取数据的主要函数。它将 dbSendQuery()、dbFetch() 和 dbClearResult() 合并为一步。

library(DBI)
library(RSQLite)

con <- dbConnect(RSQLite::SQLite(), ':memory:')
dbWriteTable(con, 'students',
  data.frame(id=1:5, name=c('Alice','Bob','Carol','Dave','Eve'),
             score=c(85,92,78,88,95)))

# Run SQL and get results as data frame
high_scorers <- dbGetQuery(con,
  'SELECT name, score FROM students WHERE score >= 88 ORDER BY score DESC'
)

print(high_scorers)
dbDisconnect(con)

dbFetch() — 分块获取结果

对于大型结果集，请使用三步方法：dbSendQuery() 发送查询，dbFetch(con, n=1000) 获取包含 n 行的数据块，dbClearResult() 负责清理。这可以控制大型表的内存使用量。

library(DBI)
library(RSQLite)

con <- dbConnect(RSQLite::SQLite(), ':memory:')
dbWriteTable(con, 'data', data.frame(x=1:100, y=rnorm(100)))

# Three-step approach for chunked reading
rs <- dbSendQuery(con, 'SELECT * FROM data WHERE x <= 5')
chunk <- dbFetch(rs, n=3)  # Get first 3 rows
cat('Rows fetched so far:', nrow(chunk), '\n')
print(chunk)
dbClearResult(rs)  # Always clear!

dbDisconnect(con)

dbExecute() — 执行非 SELECT SQL

dbExecute(con, sql) 执行会修改数据的 SQL（INSERT、UPDATE、DELETE、CREATE、DROP），并返回受影响的行数。对于不返回行的 DDL 和 DML 操作，请使用它。

library(DBI)
library(RSQLite)

con <- dbConnect(RSQLite::SQLite(), ':memory:')
dbWriteTable(con, 'students',
  data.frame(id=1:4, name=c('Alice','Bob','Carol','Dave'), score=c(85,92,78,88)))

# Update a record
rows_affected <- dbExecute(con,
  'UPDATE students SET score = 95 WHERE name = \'Alice\''
)
cat('Rows updated:', rows_affected, '\n')

# Verify
print(dbGetQuery(con, 'SELECT * FROM students WHERE name = \'Alice\''))
dbDisconnect(con)

dbExistsTable() 和 dbRemoveTable()

dbExistsTable(con, 'name') 检查表是否存在（返回 TRUE/FALSE）。dbRemoveTable(con, 'name') 删除表。在可能多次运行的脚本中，它们适合用于安全地完成初始化和清理。

library(DBI)
library(RSQLite)

con <- dbConnect(RSQLite::SQLite(), ':memory:')

cat('Before: exists?', dbExistsTable(con, 'results'), '\n')

dbWriteTable(con, 'results', data.frame(x=1:3, y=4:6))
cat('After write: exists?', dbExistsTable(con, 'results'), '\n')

dbRemoveTable(con, 'results')
cat('After remove: exists?', dbExistsTable(con, 'results'), '\n')

dbDisconnect(con)

dbDisconnect() — 关闭连接

完成操作后，始终使用 dbDisconnect(con) 关闭数据库连接。打开的连接会占用内存和文件句柄。在函数中使用 on.exit(dbDisconnect(con))，可以确保即使发生错误也能完成清理。

library(DBI)
library(RSQLite)

# Safe connection pattern using on.exit()
safe_query <- function(query) {
  con <- dbConnect(RSQLite::SQLite(), ':memory:')
  on.exit(dbDisconnect(con))  # Runs even if error!
  dbWriteTable(con, 'data', data.frame(x=1:5, y=6:10))
  dbGetQuery(con, query)
}

result <- safe_query('SELECT * FROM data WHERE x > 3')
print(result)

将 mtcars 加载到 SQLite

下面是一个实用示例：将内置的 mtcars 数据集加载到 SQLite，然后对其运行 SQL 查询。此示例展示了完整的 DBI 工作流程，并让您练习如何从 R 使用 SQL。

library(DBI)
library(RSQLite)

con <- dbConnect(RSQLite::SQLite(), ':memory:')

# Load mtcars into the database
dbWriteTable(con, 'cars', mtcars, overwrite=TRUE)

# SQL query
result <- dbGetQuery(con, '
  SELECT cyl,
         COUNT(*) AS n,
         ROUND(AVG(mpg), 1) AS avg_mpg,
         ROUND(AVG(hp), 0) AS avg_hp
  FROM cars
  GROUP BY cyl
  ORDER BY cyl
')

print(result)
dbDisconnect(con)

DBI 工作流程总结

无论数据库后端是什么，DBI 工作流程都遵循一致的模式：连接 → 加载/查询 → 断开连接。只需在 dbConnect() 中更换驱动，同一段代码就可以用于 SQLite、PostgreSQL、MySQL 及其他数据库。

library(DBI)
library(RSQLite)

# Complete DBI workflow
con <- dbConnect(RSQLite::SQLite(), ':memory:')

# 1. Create table
dbWriteTable(con, 'orders',
  data.frame(order_id=1:4, customer=c('Alice','Bob','Alice','Carol'),
             amount=c(100,200,150,80)))

# 2. Query
totals <- dbGetQuery(con,
  'SELECT customer, COUNT(*) orders, SUM(amount) total
   FROM orders GROUP BY customer ORDER BY total DESC')
print(totals)

# 3. Clean up
dbDisconnect(con)

快速检查

在 DBI 中，dbGetQuery() 和 dbExecute() 有什么区别？

回顾：DBI 和 RSQLite

DBI 和 RSQLite 的要点：

dbConnect(RSQLite::SQLite(), ':memory:') — 创建内存中的 SQLite 连接
dbWriteTable(con, 'name', df) — 将数据框加载到表中
dbListTables(con) — 列出表；dbListFields(con, 'tbl') — 列出列
dbGetQuery(con, sql) — 执行 SELECT，返回数据框
dbFetch(rs, n=1000) — 对大型结果进行分块获取
dbExecute(con, sql) — 执行 INSERT/UPDATE/DELETE/DDL
dbDisconnect(con) — 始终关闭连接；在函数中使用 on.exit()
只需更换驱动，DBI 就可以与 PostgreSQL、MySQL、BigQuery 等配合使用

library(DBI)
library(RSQLite)

con <- dbConnect(RSQLite::SQLite(), ':memory:')
on.exit(dbDisconnect(con))

dbWriteTable(con, 'scores',
  data.frame(student=c('Alice','Bob','Carol'), score=c(85,92,78)))

dbExecute(con, 'UPDATE scores SET score = score + 5 WHERE score < 80')

dbGetQuery(con, 'SELECT * FROM scores ORDER BY score DESC')

常见问题解答

「DBI 与 RSQLite 基础」课时是免费的吗？

是的 — 「DBI 与 RSQLite 基础」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 AI 导师）并解锁 R Academy 课程的其余内容，请升级到 CoddyKit PRO。 R Academy 课程共包含 4 节课。

「DBI 与 RSQLite 基础」这节课中我会学到什么？

使用 DBI 连接 SQLite 数据库、运行查询并获取结果你通过在浏览器中直接运行的动手代码来练习 R Academy，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

学习 R Academy 需要有经验吗？

无需任何先前经验。CoddyKit 上的 R Academy 课程适合初学者到高级学习者，你可以从这里开始或从头开始，按照自己的节奏学习。这是第 1 节课，共 4 节。

「DBI 与 RSQLite 基础」课时需要多长时间？

大多数 CoddyKit 课程大约需要 5–10 分钟。每节课都很精短且互动，所以你能稳步进步，并在网页和应用中从离开的地方继续。

我能在这节 R Academy 课中编写并运行代码吗？

能。每节 R Academy 课都包含内置代码编辑器，你可以在浏览器中直接编写并运行真实代码，并获得即时 AI 反馈 — 无需本地设置。

此课程中的所有课时

← 返回 R Academy