0Pricing
R Academy · Aula

Consultas parametrizadas e transações

Passe parâmetros com segurança e gerencie transações de banco de dados com várias etapas em R.

Consultas parametrizadas e transações é uma aula grátis de R Academy no CoddyKit. Esta é a aula 4 de 4. Você pode ler a aula completa abaixo gratuitamente — depois pratica ao vivo no navegador com um editor de código integrado e um tutor de IA 24/7. Faz parte do caminho de aprendizado de R Academy, e seu progresso é sincronizado entre a web e o app CoddyKit. O curso de R Academy inclui 4 aulas no total.

Por que usar consultas parametrizadas?

Quando a entrada do usuário é inserida diretamente em uma string SQL, um invasor pode injetar código SQL malicioso. Consultas parametrizadas separam a estrutura SQL dos valores dos dados, impossibilitando ataques de injeção e também tornando seu código mais limpo e fácil de ler.

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() com parâmetros

dbGetQuery() executa uma instrução SELECT e retorna os resultados como um quadro de dados. Passe uma lista params para vincular valores aos marcadores de posição. A sintaxe dos marcadores varia conforme o driver: $1 para PostgreSQL, ? para 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() executa instruções SQL que modificam dados (INSERT, UPDATE, DELETE) e retorna o número de linhas afetadas. Use params para vincular valores com segurança. Essa é a função correta para operações de gravação.

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)

Vários parâmetros em uma consulta

Você pode vincular vários parâmetros fornecendo todos eles na lista params. Eles são vinculados, na ordem, aos marcadores de posição (? ou $1, $2, ...) da string SQL. Combine sempre o número de elementos da lista com o número de marcadores de posição.

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 parametrizado

Instruções INSERT parametrizadas adicionam novos registros com segurança. Vincule o valor de cada coluna como um parâmetro. Para inserções em massa, use dbAppendTable() com um quadro de dados — é mais rápido, e o DBI gerencia a vinculação automaticamente.

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() e dbCommit() — Transações

Uma transação agrupa várias instruções SQL em uma única unidade atômica: ou todas são bem-sucedidas, ou nenhuma é. Use dbBegin() para iniciá-la e, depois, dbCommit() para finalizar todas as alterações em conjunto.

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() — Desfazendo uma transação

Se alguma instrução em uma transação falhar, chame dbRollback() para desfazer todas as alterações feitas desde dbBegin(). Isso mantém o banco de dados em um estado consistente. Combine sempre dbBegin() com dbCommit() ou 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() — Um padrão mais seguro

dbWithTransaction() envolve automaticamente seu bloco de código em uma transação. Ele confirma a transação se o bloco for bem-sucedido e desfaz as alterações se ocorrer algum erro. Isso é mais limpo e menos sujeito a erros do que chamar manualmente 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)

Tratamento de erros dentro de transações

Combinar tryCatch() com dbWithTransaction() proporciona um relatório de erros claro. Quando o bloco interno gera um erro, dbWithTransaction() desfaz as alterações automaticamente, e seu manipulador de erros pode registrar o problema ou lançá-lo novamente.

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)

Inserções em lote para melhorar o desempenho

Inserir muitas linhas uma por uma em um laço é lento. Duas abordagens melhores: use dbAppendTable() para inserir um quadro de dados inteiro de uma vez ou envolva inserções individuais em uma única transação — os bancos de dados confirmam as gravações em disco uma vez no final, tornando a inserção em massa dentro de uma transação muito mais rápida do que inserções com confirmação automática.

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() — Sempre feche as conexões

As conexões com o banco de dados consomem recursos no servidor. Sempre chame dbDisconnect(con) quando terminar. Usar on.exit(dbDisconnect(con)) no início de uma função garante que a conexão seja fechada mesmo que ocorra um erro.

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

Verificação rápida

Você precisa agrupar duas instruções UPDATE relacionadas para que ambas sejam bem-sucedidas ou nenhuma altere o banco de dados. Qual abordagem é a melhor?

Consultas parametrizadas e transações — Principais conclusões

Operações de banco de dados seguras e confiáveis em R com DBI:

  • Nunca concatene a entrada do usuário em strings SQL — use sempre params
  • dbGetQuery(con, sql, params = list(...)) — SELECT seguro
  • dbExecute(con, sql, params = list(...)) — INSERT/UPDATE/DELETE seguros
  • Marcadores de posição: ? para SQLite/MySQL, $1 / $2 para PostgreSQL
  • dbBegin() + dbCommit() + dbRollback() — controle manual de transações
  • dbWithTransaction(con, { ... }) — confirmação/desfazimento automáticos (preferível)
  • dbAppendTable(con, 'tbl', df) — inserção rápida em massa
  • on.exit(dbDisconnect(con)) — sempre feche as conexões
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)

Perguntas Frequentes

A aula “Consultas parametrizadas e transações” é grátis?

Sim — o texto completo de “Consultas parametrizadas e transações” é grátis para ler aqui na web. Para praticá-la interativamente (um editor de código integrado e um tutor de IA 24/7) e desbloquear o restante do curso de R Academy, atualize para CoddyKit PRO. O curso de R Academy inclui 4 aulas no total.

O que vou aprender em “Consultas parametrizadas e transações”?

Passe parâmetros com segurança e gerencie transações de banco de dados com várias etapas em R. Você pratica R Academy com código prático que executa diretamente no navegador, e um tutor de IA 24/7 responde suas dúvidas enquanto trabalha na aula.

Preciso ter experiência prévia para começar R Academy?

Nenhuma experiência prévia é necessária. R Academy no CoddyKit é estruturado para alunos iniciantes até avançados, então você pode começar aqui ou desde o início e aprender no seu ritmo. Esta é a aula 4 de 4.

Quanto tempo leva a aula “Consultas parametrizadas e transações”?

A maioria das aulas CoddyKit leva cerca de 5–10 minutos. Cada uma é compacta e interativa, então você faz progresso constante e retoma exatamente de onde parou entre web e app.

Posso escrever e executar código nesta aula de R Academy?

Sim. Cada aula de R Academy inclui um editor de código integrado, então você escreve e executa código real direto no navegador e recebe feedback de IA instantaneamente — nenhuma configuração local necessária.

Todas as aulas deste curso

  1. Fundamentos de DBI e RSQLite
  2. Conectando-se ao PostgreSQL e ao MySQL
  3. dbplyr: SQL com a sintaxe do dplyr
  4. Consultas parametrizadas e transações
← Voltar para R Academy