R Academy · 课时

参数化查询与事务

Q: 「参数化查询与事务」这节课中我会学到什么？

安全地传递参数，并在 R 中管理多步骤数据库事务 你通过在浏览器中直接运行的动手代码来练习 R Academy，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

安全地传递参数，并在 R 中管理多步骤数据库事务

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

为什么要使用参数化查询？

当用户输入被直接粘贴到 SQL 字符串中时，攻击者可能注入恶意 SQL 代码。参数化查询将 SQL 结构与数据值分离，使注入攻击无法奏效，同时也让代码更整洁、更易读。

library(DBI)

# DANGEROUS: string concatenation (SQL injection risk!)
user_input <- "'; DROP TABLE users; --"
# bad_sql <- paste0("SELECT * FROM users WHERE name = '", user_input, "'")
# dbGetQuery(con, bad_sql)   <- NEVER do this

# SAFE: parameterized query
# dbGetQuery(con, 'SELECT * FROM users WHERE name = $1',
#            params = list(user_input))

cat('Parameterized queries prevent SQL injection')

使用参数调用 dbGetQuery()

dbGetQuery() 会执行 SELECT 语句并将结果作为数据框返回。请传入 params 列表，将值绑定到占位符。占位符语法因驱动程序而异：PostgreSQL 使用 $1，SQLite/MySQL 使用 ?。

library(DBI)

con <- dbConnect(RSQLite::SQLite(), ':memory:')
dbWriteTable(con, 'employees', data.frame(
  name       = c('Alice', 'Bob', 'Carol'),
  department = c('Engineering', 'Marketing', 'Engineering'),
  salary     = c(90000, 75000, 95000)
))

# Parameterized SELECT — SQLite uses ?
result <- dbGetQuery(
  con,
  'SELECT name, salary FROM employees WHERE department = ?',
  params = list('Engineering')
)
print(result)

dbDisconnect(con)

dbExecute()——INSERT、UPDATE、DELETE

dbExecute() 会运行修改数据的 SQL 语句（INSERT、UPDATE、DELETE），并返回受影响的行数。请使用 params 安全地绑定值。这是执行写入操作的正确函数。

library(DBI)

con <- dbConnect(RSQLite::SQLite(), ':memory:')
dbWriteTable(con, 'users', data.frame(
  id     = 1:3,
  name   = c('Alice', 'Bob', 'Carol'),
  active = c(TRUE, FALSE, TRUE)
))

# Safe UPDATE with parameters
rows_affected <- dbExecute(
  con,
  'UPDATE users SET active = ? WHERE id = ?',
  params = list(TRUE, 2)
)
cat('Rows updated:', rows_affected, '\n')

# Verify
result <- dbGetQuery(con, 'SELECT * FROM users WHERE id = 2')
cat('Bob active:', result$active)

dbDisconnect(con)

在一个查询中使用多个参数

您可以将所有参数放入 params 列表中，从而绑定多个参数。它们会按顺序绑定到 SQL 字符串中的占位符（? 或 $1、$2……）。请始终确保列表元素数量与占位符数量一致。

library(DBI)

con <- dbConnect(RSQLite::SQLite(), ':memory:')
dbWriteTable(con, 'products', data.frame(
  id    = 1:4,
  name  = c('Apple', 'Banana', 'Cherry', 'Date'),
  price = c(1.5, 0.8, 3.0, 5.0),
  stock = c(100, 200, 50, 30)
))

# Filter by two parameters
result <- dbGetQuery(
  con,
  'SELECT name, price FROM products WHERE price > ? AND stock > ?',
  params = list(1.0, 40)
)
print(result)

dbDisconnect(con)

参数化 INSERT

参数化 INSERT 语句可以安全地添加新记录。请将每一列的值分别绑定为参数。对于批量插入，请改用带数据框的 dbAppendTable()；它速度更快，而且 DBI 会自动处理绑定。

library(DBI)

con <- dbConnect(RSQLite::SQLite(), ':memory:')
dbExecute(con, 'CREATE TABLE logs (level TEXT, message TEXT, ts TEXT)')

# Parameterized INSERT
dbExecute(
  con,
  'INSERT INTO logs (level, message, ts) VALUES (?, ?, ?)',
  params = list('INFO', 'User logged in', as.character(Sys.time()))
)

result <- dbGetQuery(con, 'SELECT * FROM logs')
print(result)

dbDisconnect(con)

dbBegin() 和 dbCommit()——事务

事务会将多条 SQL 语句组合成一个原子单元：要么全部成功，要么全部不执行。请使用 dbBegin() 开始事务，然后使用 dbCommit() 一次性提交所有更改。

library(DBI)

con <- dbConnect(RSQLite::SQLite(), ':memory:')
dbExecute(con, 'CREATE TABLE accounts (id INTEGER, balance REAL)')
dbExecute(con, "INSERT INTO accounts VALUES (1, 1000), (2, 500)")

# Transfer 200 from account 1 to account 2
dbBegin(con)
  dbExecute(con, 'UPDATE accounts SET balance = balance - ? WHERE id = ?',
            params = list(200, 1))
  dbExecute(con, 'UPDATE accounts SET balance = balance + ? WHERE id = ?',
            params = list(200, 2))
dbCommit(con)

result <- dbGetQuery(con, 'SELECT * FROM accounts')
print(result)
dbDisconnect(con)

dbRollback()——撤销事务

如果事务中的任何语句失败，请调用 dbRollback()，撤销自 dbBegin() 以来所做的所有更改。这样可以使数据库保持一致状态。请始终将 dbBegin() 与 dbCommit() 或 dbRollback() 配对使用。

library(DBI)

con <- dbConnect(RSQLite::SQLite(), ':memory:')
dbExecute(con, 'CREATE TABLE orders (id INTEGER, amount REAL)')
dbExecute(con, 'INSERT INTO orders VALUES (1, 500)')

# Simulate a failed transaction
dbBegin(con)
tryCatch({
  dbExecute(con, 'UPDATE orders SET amount = ? WHERE id = ?',
            params = list(600, 1))
  stop('Simulated error during processing')  # something goes wrong
  dbCommit(con)
}, error = function(e) {
  dbRollback(con)
  cat('Transaction rolled back:', e$message, '\n')
})

# Amount is still 500 (rollback worked)
result <- dbGetQuery(con, 'SELECT * FROM orders')
cat('Amount after rollback:', result$amount)
dbDisconnect(con)

dbWithTransaction()——更安全的模式

dbWithTransaction() 会自动将您的代码块包装在事务中。如果代码块执行成功，它会提交事务；如果发生任何错误，则会回滚事务。与手动调用 dbBegin() / dbCommit() / dbRollback() 相比，这种方式更简洁，也不容易出错。

library(DBI)

con <- dbConnect(RSQLite::SQLite(), ':memory:')
dbExecute(con, 'CREATE TABLE ledger (account TEXT, amount REAL)')
dbExecute(con, "INSERT INTO ledger VALUES ('Alice', 1000), ('Bob', 500)")

# Automatic transaction management:
dbWithTransaction(con, {
  dbExecute(con, "UPDATE ledger SET amount = amount - 100 WHERE account = 'Alice'",
            params = list())
  dbExecute(con, "UPDATE ledger SET amount = amount + 100 WHERE account = 'Bob'",
            params = list())
})
# Committed automatically on success

print(dbGetQuery(con, 'SELECT * FROM ledger'))
dbDisconnect(con)

事务中的错误处理

将 tryCatch() 与 dbWithTransaction() 结合使用，可以实现清晰的错误报告。当内部代码块抛出错误时，dbWithTransaction() 会自动回滚，而您的错误处理程序可以记录或重新抛出该问题。

library(DBI)

con <- dbConnect(RSQLite::SQLite(), ':memory:')
dbExecute(con, 'CREATE TABLE inventory (item TEXT, qty INTEGER)')
dbExecute(con, "INSERT INTO inventory VALUES ('Widget', 100)")

safe_update <- function(con, item, qty_change) {
  tryCatch(
    dbWithTransaction(con, {
      dbExecute(con,
        'UPDATE inventory SET qty = qty + ? WHERE item = ?',
        params = list(qty_change, item))
      cat('Updated', item, 'by', qty_change, '\n')
    }),
    error = function(e) cat('Failed (rolled back):', e$message, '\n')
  )
}

safe_update(con, 'Widget', -30)
print(dbGetQuery(con, 'SELECT * FROM inventory'))
dbDisconnect(con)

提升性能的批量插入

在循环中逐行插入许多行的速度很慢。有两种更好的方式：使用 dbAppendTable() 一次性插入整个数据框，或者将各次插入放在一个事务中——数据库只会在最后统一提交磁盘写入，因此事务中的批量插入会比自动提交插入快得多。

library(DBI)

con <- dbConnect(RSQLite::SQLite(), ':memory:')
dbExecute(con, 'CREATE TABLE events (user_id INTEGER, action TEXT)')

# Fast: insert a whole data frame at once
new_events <- data.frame(
  user_id = c(101, 102, 103, 104),
  action  = c('login', 'view', 'purchase', 'logout')
)
dbAppendTable(con, 'events', new_events)

result <- dbGetQuery(con, 'SELECT COUNT(*) AS n FROM events')
cat('Rows inserted:', result$n)

dbDisconnect(con)

dbDisconnect()——始终关闭连接

数据库连接会占用服务器资源。完成操作后，请始终调用 dbDisconnect(con)。在函数开头使用 on.exit(dbDisconnect(con))，可以确保即使发生错误，连接也会关闭。

library(DBI)

# Pattern: use on.exit to guarantee disconnection
run_query <- function(sql) {
  con <- dbConnect(RSQLite::SQLite(), ':memory:')
  on.exit(dbDisconnect(con), add = TRUE)  # always runs

  dbExecute(con, 'CREATE TABLE t (x INTEGER)')
  dbExecute(con, 'INSERT INTO t VALUES (1), (2), (3)')
  result <- dbGetQuery(con, sql)
  result  # con is closed automatically after return
}

df <- run_query('SELECT * FROM t WHERE x > 1')
cat('Rows:', nrow(df))
# on.exit fires after return — connection cleanly closed

快速检查

您需要将两条相关的 UPDATE 语句组合起来，使它们要么全部成功，要么都不改变数据库。哪种方式最好？

参数化查询与事务——要点

使用 DBI 在 R 中执行安全可靠的数据库操作：

绝不要将用户输入拼接到 SQL 字符串中——始终使用 params
dbGetQuery(con, sql, params = list(...))——安全的 SELECT
dbExecute(con, sql, params = list(...))——安全的 INSERT/UPDATE/DELETE
占位符：SQLite/MySQL 使用 ?，PostgreSQL 使用 $1 / $2
dbBegin() + dbCommit() + dbRollback()——手动控制事务
dbWithTransaction(con, { ... })——自动提交/回滚（推荐）
dbAppendTable(con, 'tbl', df)——快速批量插入
on.exit(dbDisconnect(con))——始终关闭连接

library(DBI)

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

dbExecute(con, 'CREATE TABLE transfers (from_id INT, to_id INT, amount REAL)')

# Safe parameterized insert inside a transaction:
dbWithTransaction(con, {
  dbExecute(con,
    'INSERT INTO transfers (from_id, to_id, amount) VALUES (?, ?, ?)',
    params = list(1, 2, 250.00)
  )
})

result <- dbGetQuery(con, 'SELECT * FROM transfers')
cat('Transfer recorded:', result$amount)

常见问题解答

「参数化查询与事务」课时是免费的吗？

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

「参数化查询与事务」这节课中我会学到什么？

安全地传递参数，并在 R 中管理多步骤数据库事务你通过在浏览器中直接运行的动手代码来练习 R Academy，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

学习 R Academy 需要有经验吗？

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

「参数化查询与事务」课时需要多长时间？

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

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

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

此课程中的所有课时

← 返回 R Academy