0Pricing
R Academy · 강의

매개변수화된 쿼리와 트랜잭션

R에서 매개변수를 안전하게 전달하고 여러 단계의 데이터베이스 트랜잭션을 관리합니다.

매개변수화된 쿼리와 트랜잭션은(는) CoddyKit의 무료 R Academy 강의입니다. 이것은 4개 중 4번째 강의입니다. 아래에서 전체 강의를 무료로 읽을 수 있으며, 내장 코드 에디터와 24/7 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)

자주 묻는 질문

“매개변수화된 쿼리와 트랜잭션” 강의는 무료인가요?

네 — “매개변수화된 쿼리와 트랜잭션” 전체 내용을 이 웹사이트에서 무료로 읽을 수 있습니다. 인터랙티브하게 실습하려면(내장 코드 에디터와 24/7 AI 튜터), CoddyKit PRO로 업그레이드하면 R Academy 강의 전체를 잠금 해제할 수 있습니다. R Academy 강의에는 총 4개의 강의가 포함되어 있습니다.

“매개변수화된 쿼리와 트랜잭션”에서 뭘 배우나요?

R에서 매개변수를 안전하게 전달하고 여러 단계의 데이터베이스 트랜잭션을 관리합니다. 브라우저에서 직접 실행하는 실습 코드로 R Academy을(를) 배우며, 24/7 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(으)로 돌아가기