R Academy · Pelajaran

Pertanyaan Berparameter dan Transaksi

Hantar parameter dengan selamat dan urus transaksi pangkalan data berbilang langkah dalam R.

Pelajaran 4 daripada 413 langkah

Pertanyaan Berparameter dan Transaksi ialah pelajaran R Academy percuma di CoddyKit. Ini ialah pelajaran 4 daripada 4. Sebanyak 3 pelajaran dalam laluan pembelajaran ini boleh dibaca sepenuhnya secara percuma — selepas itu, CoddyKit PRO membuka akses kepada semua pelajaran, serta latihan praktikal dengan penyunting kod terbina dalam dan tutor kecerdasan buatan yang tersedia 24/7. Pelajaran ini merupakan sebahagian daripada laluan pembelajaran R Academy, dan kemajuan anda disegerakkan merentas web serta aplikasi CoddyKit. Kursus R Academy merangkumi sejumlah 4 pelajaran.

Mengapa Pertanyaan Berparameter?

Apabila input pengguna ditampal terus ke dalam rentetan SQL, penyerang boleh menyuntik kod SQL berniat jahat. Pertanyaan berparameter memisahkan struktur SQL daripada nilai data, menjadikan serangan suntikan mustahil serta menjadikan kod anda lebih kemas dan mudah dibaca.

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() dengan Parameter

dbGetQuery() melaksanakan pernyataan SELECT dan mengembalikan hasil sebagai bingkai data. Hantar senarai params untuk mengikat nilai pada ruang letak. Sintaks ruang letak berbeza mengikut pemacu: $1 untuk PostgreSQL, ? untuk 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() menjalankan pernyataan SQL yang mengubah data (INSERT, UPDATE, DELETE) dan mengembalikan bilangan baris yang terjejas. Gunakan params untuk pengikatan nilai yang selamat. Inilah fungsi yang betul untuk operasi penulisan.

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)

Berbilang Parameter dalam Satu Pertanyaan

Anda boleh mengikat berbilang parameter dengan menyediakan semuanya dalam senarai params. Parameter tersebut diikat mengikut susunan pada ruang letak (? atau $1, $2, ...) dalam rentetan SQL. Sentiasa padankan bilangan elemen senarai dengan bilangan ruang letak.

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 Berparameter

Pernyataan INSERT berparameter menambah rekod baharu dengan selamat. Ikat setiap nilai lajur sebagai parameter. Untuk INSERT pukal, gunakan dbAppendTable() dengan bingkai data — cara ini lebih pantas dan DBI mengendalikan pengikatan secara automatik.

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() dan dbCommit() — Transaksi

Transaksi mengumpulkan berbilang pernyataan SQL sebagai satu unit atomik: sama ada semuanya berjaya atau tiada satu pun yang berjaya. Gunakan dbBegin() untuk bermula, kemudian dbCommit() untuk memuktamadkan semua perubahan bersama-sama.

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() — Membatalkan Transaksi

Jika mana-mana pernyataan dalam transaksi gagal, panggil dbRollback() untuk membatalkan semua perubahan yang dibuat sejak dbBegin(). Ini memastikan pangkalan data kekal dalam keadaan yang konsisten. Sentiasa pasangkan dbBegin() dengan sama ada dbCommit() atau 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() — Corak yang Lebih Selamat

dbWithTransaction() membungkus blok kod anda dalam transaksi secara automatik. Ia melakukan commit jika blok berjaya dan melakukan rollback jika berlaku sebarang ralat. Cara ini lebih kemas dan kurang terdedah kepada ralat berbanding memanggil dbBegin() / dbCommit() / dbRollback() secara manual.

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)

Pengendalian Ralat dalam Transaksi

Menggabungkan tryCatch() dengan dbWithTransaction() memberikan pelaporan ralat yang kemas. Apabila blok dalaman mencetuskan ralat, dbWithTransaction() melakukan rollback secara automatik, dan pengendali ralat anda boleh merekodkan atau mencetuskan semula isu tersebut.

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)

INSERT Kelompok untuk Prestasi

Memasukkan banyak baris satu demi satu dalam gelung adalah perlahan. Dua pendekatan yang lebih baik: gunakan dbAppendTable() untuk memasukkan seluruh bingkai data sekali gus, atau bungkus INSERT individu dalam satu transaksi — pangkalan data menulis perubahan pada cakera sekali sahaja pada akhir transaksi, menjadikan INSERT pukal dalam transaksi jauh lebih pantas daripada INSERT dengan commit automatik.

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() — Sentiasa Tutup Sambungan

Sambungan pangkalan data menggunakan sumber pada pelayan. Sentiasa panggil dbDisconnect(con) apabila anda selesai. Menggunakan on.exit(dbDisconnect(con)) pada bahagian atas fungsi memastikan sambungan ditutup walaupun berlaku ralat.

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

Semakan Pantas

Anda perlu mengumpulkan dua pernyataan UPDATE yang berkaitan supaya kedua-duanya berjaya atau tiada satu pun mengubah pangkalan data. Apakah pendekatan yang terbaik?

Jawab dalam pelajaran interaktif

Pertanyaan Berparameter dan Transaksi — Perkara Penting

Operasi pangkalan data yang selamat dan boleh dipercayai dalam R dengan DBI:

Jangan sekali-kali menggabungkan input pengguna ke dalam rentetan SQL — sentiasa gunakan params
dbGetQuery(con, sql, params = list(...)) — SELECT yang selamat
dbExecute(con, sql, params = list(...)) — INSERT/UPDATE/DELETE yang selamat
Ruang letak: ? untuk SQLite/MySQL, $1 / $2 untuk PostgreSQL
dbBegin() + dbCommit() + dbRollback() — kawalan transaksi manual
dbWithTransaction(con, { ... }) — commit/rollback automatik (disyorkan)
dbAppendTable(con, 'tbl', df) — INSERT pukal yang pantas
on.exit(dbDisconnect(con)) — sentiasa tutup sambungan

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)

Percuma untuk bermula

Pelajari R dengan tutor kecerdasan buatan — percuma

Tulis dan jalankan kod sebenar dalam pelayar anda, dapatkan bantuan segera daripada tutor kecerdasan buatan yang tersedia 24/7, dan sambung semula dari tempat anda berhenti di web atau dalam aplikasi.

Kursus: 43
Pelajaran: 159

Mula belajar secara percuma Lihat kursus penuh

Soalan Lazim

Adakah pelajaran “Pertanyaan Berparameter dan Transaksi” percuma?

Ya — sebanyak 3 pelajaran dalam laluan pembelajaran R Academy, termasuk “Pertanyaan Berparameter dan Transaksi”, boleh dibaca sepenuhnya secara percuma di web ini. Selepas itu, CoddyKit PRO membuka akses kepada semua pelajaran, serta latihan interaktif dengan penyunting kod terbina dalam dan tutor kecerdasan buatan yang tersedia 24/7. Kursus R Academy merangkumi sejumlah 4 pelajaran.

Apakah yang akan saya pelajari dalam “Pertanyaan Berparameter dan Transaksi”?

Hantar parameter dengan selamat dan urus transaksi pangkalan data berbilang langkah dalam R. Anda berlatih R Academy menggunakan kod praktikal yang dijalankan terus dalam pelayar, manakala tutor kecerdasan buatan 24/7 menjawab soalan anda semasa anda mengikuti pelajaran.

Adakah saya memerlukan pengalaman untuk memulakan R Academy?

Tiada pengalaman terdahulu diperlukan. Pembelajaran R Academy di CoddyKit disusun untuk pelajar daripada peringkat pemula hingga lanjutan, jadi anda boleh bermula di sini atau dari awal dan belajar mengikut kadar anda sendiri. Ini ialah pelajaran 4 daripada 4.

Berapa lamakah pelajaran “Pertanyaan Berparameter dan Transaksi” diambil?

Kebanyakan pelajaran CoddyKit mengambil masa kira-kira 5–10 minit. Setiap pelajaran ringkas dan interaktif, jadi anda boleh membuat kemajuan secara berterusan dan menyambung tepat dari tempat anda berhenti di web atau aplikasi.

Bolehkah saya menulis dan menjalankan kod dalam pelajaran R Academy ini?

Ya. Setiap pelajaran R Academy menyertakan penyunting kod terbina dalam, jadi anda boleh menulis dan menjalankan kod sebenar terus dalam pelayar serta menerima maklum balas kecerdasan buatan serta-merta — tanpa memerlukan persediaan setempat.

Semua pelajaran dalam kursus ini

← Kembali ke R Academy