PricingLoginStart free
R Academy · レッスン

パラメーター化クエリとトランザクション

パラメーターを安全に渡し、Rで複数ステップのデータベーストランザクションを管理します。

レッスン 4/413 ステップ

「パラメーター化クエリとトランザクション」はCoddyKit上の無料R Academyレッスンです。 これはレッスン4/4です。 下記で完全なレッスンを無料で読むことができます。その後、ブラウザ内の組み込みコードエディタと24時間対応の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)

1つのクエリで複数のパラメーターを使う

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文を1つのアトミックな単位にまとめます。すべて成功するか、どれも実行されないかのどちらかになります。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)

パフォーマンス向上のための一括挿入

ループで1行ずつ挿入すると時間がかかります。より良い方法は2つあります。dbAppendTable()を使ってデータフレーム全体を一度に挿入する方法と、個々の挿入を1つのトランザクションで囲む方法です。後者では、データベースが最後に1回だけディスクへの書き込みをコミットするため、自動コミットで挿入するよりも大幅に高速になります。

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

クイックチェック

関連する2つの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 — 無料

ブラウザでリアルコードを書いて実行し、24/7 の AI チューターから瞬時にサポートを受け、ウェブまたはアプリで続きから学習できます。

コース
43
レッスン
159
無料で学習を始めるコース全体を表示

よくある質問

「パラメーター化クエリとトランザクション」レッスンは無料ですか？

はい。「パラメーター化クエリとトランザクション」の完全なテキストはこのウェブで無料で読めます。インタラクティブに演習し（組み込みコードエディタと24時間対応のAIチューター）、R Academyコースの残りをアンロックするには、CoddyKit PROにアップグレードしてください。 R Academyコースには全4レッスンが含まれています。

「パラメーター化クエリとトランザクション」で何を学びますか？

パラメーターを安全に渡し、Rで複数ステップのデータベーストランザクションを管理します。 ブラウザで直接実行するハンズオンコードでR Academyを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

R Academyを始めるのに経験は必要ですか？

事前経験は必要ありません。CoddyKitのR Academyは初級者から上級者向けに構成されているため、ここから始めるか最初から始めて、自分のペースで進むことができます。 これはレッスン4/4です。

「パラメーター化クエリとトランザクション」レッスンにはどのくらい時間がかかりますか？

ほとんどのCoddyKitレッスンは約5～10分かかります。各レッスンはコンパクトでインタラクティブなので、着実に進歩し、ウェブとアプリ全体で正確に前回の場所から再開できます。

このR Academyレッスンでコードを書いて実行できますか？

はい。すべてのR Academyレッスンに組み込みコードエディタが含まれているため、ブラウザでリアルコードを書いて実行し、即座のAIフィードバックを取得できます。ローカル設定は不要です。

このコースのすべてのレッスン

  1. DBIとRSQLiteの基礎
  2. PostgreSQLとMySQLへの接続
  3. dbplyr：dplyr構文でSQLを扱う
  4. パラメーター化クエリとトランザクション
← R Academyに戻る