0Pricing
R Academy · Урок

Параметризованные запросы и транзакции

Безопасно передавайте параметры и управляйте многоэтапными транзакциями базы данных в R.

«Параметризованные запросы и транзакции» — бесплатный урок R Academy на CoddyKit. Это урок 4 из 4. Ты можешь прочитать весь урок бесплатно ниже — а потом практиковать его прямо в браузере с встроенным редактором кода и ИИ-репетитором 24/7. Это часть пути обучения 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, чтобы связать значения с заполнителями. Синтаксис заполнителей зависит от драйвера: $1 для PostgreSQL, ? для 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. Они связываются по порядку с заполнителями (? или $1, $2, ...) в строке SQL. Всегда следите, чтобы количество элементов списка совпадало с количеством заполнителей.

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 так, чтобы либо обе выполнились успешно, либо база данных не изменилась. Какой подход лучше всего выбрать?

Параметризованные запросы и транзакции — основные выводы

Безопасные и надежные операции с базами данных в R с помощью DBI:

  • Никогда не объединяйте входные данные пользователя со строками SQL — всегда используйте params
  • dbGetQuery(con, sql, params = list(...)) — безопасный SELECT
  • dbExecute(con, sql, params = list(...)) — безопасные INSERT/UPDATE/DELETE
  • Заполнители: ? для SQLite/MySQL, $1 / $2 для PostgreSQL
  • 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)

Часто задаваемые вопросы

Урок «Параметризованные запросы и транзакции» бесплатный?

Да — полный текст урока «Параметризованные запросы и транзакции» бесплатно доступен здесь в веб-версии. Чтобы практиковать его интерактивно (встроенный редактор кода и ИИ-репетитор 24/7) и разблокировать остальной курс R Academy, подпишись на CoddyKit PRO. Курс R Academy содержит 4 уроков всего.

Чему я научусь в уроке «Параметризованные запросы и транзакции»?

Безопасно передавайте параметры и управляйте многоэтапными транзакциями базы данных в R. Ты практикуешь R Academy с помощью реального кода, который запускаешь прямо в браузере, и ИИ-репетитор 24/7 отвечает на твои вопросы во время урока.

Нужен ли мне опыт, чтобы начать R Academy?

Предыдущий опыт не требуется. R Academy на CoddyKit структурирован для всех уровней — от новичков до продвинутых, поэтому ты можешь начать отсюда или с самого начала и учиться в своем темпе. Это урок 4 из 4.

Сколько времени занимает урок «Параметризованные запросы и транзакции»?

Большинство уроков CoddyKit занимают около 5–10 минут. Каждый из них компактный и интерактивный, поэтому ты постоянно делаешь прогресс и продолжаешь с того же места в веб-версии и приложении.

Можно ли писать и запускать код в этом уроке R Academy?

Да. Каждый урок R Academy включает встроенный редактор кода, поэтому ты пишешь и запускаешь реальный код прямо в браузере и получаешь моментальную обратную связь от AI — локальная установка не требуется.

Все уроки этого курса

  1. Основы DBI и RSQLite
  2. Подключение к PostgreSQL и MySQL
  3. dbplyr: SQL через синтаксис dplyr
  4. Параметризованные запросы и транзакции
← Назад к R Academy