R Academy · Oppitunti

Parametrisoidut kyselyt ja transaktiot

Välittäkää parametreja turvallisesti ja hallitkaa monivaiheisia tietokantatransaktioita R:ssä.

Oppitunti 4/413 vaihetta

Parametrisoidut kyselyt ja transaktiot on ilmainen R Academy-oppitunti CoddyKitissä. Tämä on oppitunti 4/4. Voit lukea tästä oppimispolusta kokonaan mitkä tahansa 3 oppituntia ilmaiseksi — sen jälkeen CoddyKit PRO avaa kaikki oppitunnit sekä käytännön harjoittelun sisäänrakennetulla koodieditorilla ja ympäri vuorokauden toimivalla tekoälytuutorilla. Oppitunti kuuluu R Academy-oppimispolkuun, ja edistymisesi synkronoituu verkon ja CoddyKit-sovelluksen välillä. R Academy-kurssilla on yhteensä 4 oppituntia.

Miksi parametrisoituja kyselyitä käytetään?

Kun käyttäjän syöte liitetään suoraan SQL-merkkijonoon, hyökkääjä voi syöttää siihen haitallista SQL-koodia. Parametrisoidut kyselyt erottavat SQL-rakenteen data-arvoista, mikä tekee injektiohyökkäyksistä mahdottomia ja koodista selkeämpää sekä helpommin luettavaa.

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() parametreilla

dbGetQuery() suorittaa SELECT-lauseen ja palauttaa tulokset data framenä. Välittäkää params-lista, jotta arvot sidotaan paikkamerkkeihin. Paikkamerkkien syntaksi vaihtelee ajurin mukaan: $1 PostgreSQL:ssä ja ? SQLite/MySQL:ssä.

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() suorittaa dataa muokkaavia SQL-lauseita (INSERT, UPDATE, DELETE) ja palauttaa muutoksen kohteena olevien rivien määrän. Käyttäkää params-parametria arvojen turvalliseen sitomiseen. Tämä on oikea funktio kirjoitusoperaatioihin.

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)

Useita parametreja yhdessä kyselyssä

Voitte sitoa useita parametreja antamalla ne kaikki params-listassa. Ne sidotaan järjestyksessä SQL-merkkijonon paikkamerkkeihin (? tai $1, $2, ...). Varmistakaa aina, että listan alkioiden määrä vastaa paikkamerkkien määrää.

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)

Parametrisoitu INSERT

Parametrisoidut INSERT-lauseet lisäävät uusia tietueita turvallisesti. Sitokaa jokaisen sarakkeen arvo parametrina. Käyttäkää massalisäyksiin sen sijaan dbAppendTable()-funktiota data framen kanssa — se on nopeampi, ja DBI huolehtii sidonnasta automaattisesti.

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() ja dbCommit() — Transaktiot

Transaktio ryhmittelee useita SQL-lauseita yhdeksi atomiseksi kokonaisuudeksi: joko kaikki onnistuvat tai mikään ei onnistu. Käyttäkää dbBegin()-funktiota aloittamiseen ja sitten dbCommit()-funktiota kaikkien muutosten viimeistelemiseen yhdessä.

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() — Transaktion kumoaminen

Jos jokin transaktion lause epäonnistuu, kutsukaa dbRollback()-funktiota kumoamaan kaikki dbBegin()-funktion jälkeen tehdyt muutokset. Näin tietokanta säilyy eheässä tilassa. Yhdistäkää dbBegin() aina joko dbCommit()- tai dbRollback()-funktioon.

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() — Turvallisempi toimintatapa

dbWithTransaction() suorittaa koodilohkon automaattisesti transaktion sisällä. Se vahvistaa muutokset, jos lohko onnistuu, ja peruuttaa ne, jos tapahtuu virhe. Tämä on selkeämpää ja vähemmän virhealtista kuin dbBegin()- / dbCommit()- / dbRollback()-funktioiden kutsuminen manuaalisesti.

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)

Virheenkäsittely transaktioiden sisällä

tryCatch()- ja dbWithTransaction()-funktioiden yhdistäminen mahdollistaa selkeän virheraportoinnin. Kun sisempi lohko aiheuttaa virheen, dbWithTransaction() peruuttaa muutokset automaattisesti, ja virheenkäsittelijä voi kirjata ongelman tai välittää sen uudelleen.

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)

Eräkäsittelyyn perustuvat lisäykset suorituskyvyn parantamiseksi

Useiden rivien lisääminen yksi kerrallaan silmukassa on hidasta. Kaksi parempaa tapaa ovat koko data framen lisääminen kerralla dbAppendTable()-funktiolla tai yksittäisten lisäysten suorittaminen yhden transaktion sisällä — tietokannat kirjoittavat muutokset levylle vain kerran lopussa, joten massalisäys transaktion sisällä on paljon nopeampaa kuin automaattisesti vahvistettavat lisäykset.

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() — Sulkekaa yhteydet aina

Tietokantayhteydet kuluttavat palvelimen resursseja. Kutsukaa aina dbDisconnect(con)-funktiota, kun olette valmis. on.exit(dbDisconnect(con))-kutsun käyttäminen funktion alussa varmistaa, että yhteys suljetaan myös virheen tapahtuessa.

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

Pikatarkistus

Teidän on ryhmiteltävä kaksi toisiinsa liittyvää UPDATE-lausetta siten, että joko molemmat onnistuvat tai kumpikaan ei muuta tietokantaa. Mikä toimintatapa on paras?

Vastaa interaktiivisessa oppitunnissa

Parametrisoidut kyselyt ja transaktiot — Tärkeimmät opit

Turvalliset ja luotettavat tietokantaoperaatiot R:ssä DBI:n avulla:

Älkää koskaan yhdistäkö käyttäjän syötettä SQL-merkkijonoihin — käyttäkää aina params-parametria
dbGetQuery(con, sql, params = list(...)) — turvallinen SELECT
dbExecute(con, sql, params = list(...)) — turvallinen INSERT/UPDATE/DELETE
Paikkamerkit: ? SQLite/MySQL:lle, $1 / $2 PostgreSQL:lle
dbBegin() + dbCommit() + dbRollback() — transaktioiden manuaalinen hallinta
dbWithTransaction(con, { ... }) — automaattinen vahvistus/peruutus (suositeltu)
dbAppendTable(con, 'tbl', df) — nopea massalisäys
on.exit(dbDisconnect(con)) — sulkekaa yhteydet aina

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)

Aloita maksutta

Opi R tekoälytuutorin avulla — ilmaiseksi

Kirjoita ja suorita oikeaa koodia selaimessa, saa välitöntä apua tekoälytuutorilta ympäri vuorokauden ja jatka siitä, mihin jäit, verkossa tai sovelluksessa.

Kurssit: 43
Oppitunnit: 159

Aloita opiskelu maksutta Näytä koko kurssi

Usein kysytyt kysymykset

Onko oppitunti ”Parametrisoidut kyselyt ja transaktiot” ilmainen?

Kyllä — voit lukea täällä verkossa kokonaan ilmaiseksi mitkä tahansa R Academy-oppimispolun 3 oppituntia, myös oppitunnin “Parametrisoidut kyselyt ja transaktiot”. Sen jälkeen CoddyKit PRO avaa kaikki oppitunnit sekä interaktiiviset harjoitukset sisäänrakennetulla koodieditorilla ja ympäri vuorokauden toimivalla tekoälytuutorilla. R Academy-kurssilla on yhteensä 4 oppituntia.

Mitä opin oppitunnilla ”Parametrisoidut kyselyt ja transaktiot”?

Välittäkää parametreja turvallisesti ja hallitkaa monivaiheisia tietokantatransaktioita R:ssä. Harjoittelet R Academy-aihetta koodilla, jonka suoritat suoraan selaimessa. Ympäri vuorokauden käytettävissä oleva tekoälytuutori vastaa kysymyksiisi oppitunnin aikana.

Tarvitsenko kokemusta aloittaakseni R Academy-opiskelun?

Aiempi kokemus ei ole tarpeen. CoddyKitin R Academy-oppimispolku sopii vasta-alkajista edistyneisiin, joten voit aloittaa tästä tai alusta ja edetä omaan tahtiisi. Tämä on oppitunti 4/4.

Kuinka kauan ”Parametrisoidut kyselyt ja transaktiot”-oppitunnin suorittaminen kestää?

Useimmat CoddyKitin oppitunnit kestävät noin 5–10 minuuttia. Jokainen oppitunti on lyhyt ja interaktiivinen, joten edistyt tasaisesti ja voit jatkaa siitä, mihin jäit – sekä verkossa että sovelluksessa.

Voinko kirjoittaa ja suorittaa koodia tällä R Academy-oppitunnilla?

Kyllä. Jokainen R Academy-oppitunti sisältää sisäänrakennetun koodieditorin, joten voit kirjoittaa ja suorittaa oikeaa koodia suoraan selaimessa ja saada välitöntä palautetta tekoälyltä – paikallista asennusta ei tarvita.

Kaikki tämän kurssin oppitunnit

DBI:n ja RSQLiten perusteet
Yhdistäminen PostgreSQL- ja MySQL-tietokantoihin
dbplyr: SQL dplyr-syntaksilla
Parametrisoidut kyselyt ja transaktiot

← Takaisin: R Academy