0Pricing
R Academy · Lezione

Basi di DBI e RSQLite

Si colleghi ai database SQLite, esegua query e recuperi risultati con DBI.

Basi di DBI e RSQLite è una lezione R Academy gratuita su CoddyKit. Questa è la lezione 1 di 4. Puoi leggere la lezione completa qui gratuitamente — poi esercitati direttamente nel browser con un editor di codice integrato e un tutor IA disponibile 24/7. Fa parte del percorso di apprendimento R Academy, e i tuoi progressi si sincronizzano tra il web e l'app CoddyKit. Il corso R Academy include 4 lezioni in totale.

Accesso ai database in R con DBI

Il pacchetto DBI fornisce un'interfaccia unificata per accedere ai database in R. Le stesse funzioni funzionano con diversi backend di database (SQLite, PostgreSQL, MySQL ecc.) abbinando DBI a un pacchetto driver per il backend. RSQLite è il backend più accessibile: non richiede l'installazione di alcun server.

library(DBI)
library(RSQLite)

# DBI + RSQLite: connect to an in-memory SQLite database
# ':memory:' creates a fresh database in RAM
con <- dbConnect(RSQLite::SQLite(), ':memory:')

cat('Connection class:', class(con), '\n')
cat('Database backend:', dbGetInfo(con)$dbname, '\n')

# Always close connection when done
dbDisconnect(con)
cat('Connection closed.')

dbConnect() — Creazione di una connessione

dbConnect(drv, ...) crea una connessione al database. Il primo argomento è l'oggetto driver. Per SQLite: RSQLite::SQLite(). Gli argomenti aggiuntivi, come il percorso del file del database, dipendono dal backend.

library(DBI)
library(RSQLite)

# In-memory database (disappears when connection closes)
con_mem <- dbConnect(RSQLite::SQLite(), ':memory:')

# File-based SQLite database (persists to disk)
# con_file <- dbConnect(RSQLite::SQLite(), '/tmp/mydb.sqlite')

# Always wrap connections in tryCatch or use on.exit()
on.exit(dbDisconnect(con_mem))

cat('Connected! Is valid:', dbIsValid(con_mem))

dbWriteTable() — Caricamento dei dati nel database

dbWriteTable(con, 'table_name', df) crea una tabella e vi carica un data frame. Imposti overwrite=TRUE per sostituire una tabella esistente oppure append=TRUE per aggiungere righe a una tabella esistente.

library(DBI)
library(RSQLite)

con <- dbConnect(RSQLite::SQLite(), ':memory:')

# Load a data frame into the database
students <- data.frame(
  id = 1:5,
  name = c('Alice','Bob','Carol','Dave','Eve'),
  score = c(85, 92, 78, 88, 95)
)

dbWriteTable(con, 'students', students)

# Verify: list tables
cat('Tables in DB:', dbListTables(con), '\n')

dbDisconnect(con)

dbListTables() e dbListFields()

dbListTables(con) restituisce i nomi di tutte le tabelle presenti nel database connesso. dbListFields(con, 'table') restituisce i nomi delle colonne di una tabella specifica. Utilizzi queste funzioni per esplorare un database sconosciuto.

library(DBI)
library(RSQLite)

con <- dbConnect(RSQLite::SQLite(), ':memory:')
dbWriteTable(con, 'students', data.frame(id=1:3, name=c('A','B','C'), score=c(80,90,85)))
dbWriteTable(con, 'courses', data.frame(code=c('R101','R201'), title=c('Intro','Advanced')))

cat('Tables:', paste(dbListTables(con), collapse=', '), '\n')
cat('Students fields:', paste(dbListFields(con, 'students'), collapse=', '), '\n')

dbDisconnect(con)

dbGetQuery() — Esecuzione di SQL e restituzione dei dati

dbGetQuery(con, sql) esegue una query SELECT e restituisce il risultato come data frame. È la funzione principale per leggere dati da un database. Combina dbSendQuery() + dbFetch() + dbClearResult() in un unico passaggio.

library(DBI)
library(RSQLite)

con <- dbConnect(RSQLite::SQLite(), ':memory:')
dbWriteTable(con, 'students',
  data.frame(id=1:5, name=c('Alice','Bob','Carol','Dave','Eve'),
             score=c(85,92,78,88,95)))

# Run SQL and get results as data frame
high_scorers <- dbGetQuery(con,
  'SELECT name, score FROM students WHERE score >= 88 ORDER BY score DESC'
)

print(high_scorers)
dbDisconnect(con)

dbFetch() — Recupero dei risultati a blocchi

Per grandi insiemi di risultati, utilizzi l'approccio in tre passaggi: dbSendQuery() invia la query, dbFetch(con, n=1000) recupera blocchi di n righe e dbClearResult() esegue la pulizia. In questo modo si controlla l'uso della memoria per le tabelle di grandi dimensioni.

library(DBI)
library(RSQLite)

con <- dbConnect(RSQLite::SQLite(), ':memory:')
dbWriteTable(con, 'data', data.frame(x=1:100, y=rnorm(100)))

# Three-step approach for chunked reading
rs <- dbSendQuery(con, 'SELECT * FROM data WHERE x <= 5')
chunk <- dbFetch(rs, n=3)  # Get first 3 rows
cat('Rows fetched so far:', nrow(chunk), '\n')
print(chunk)
dbClearResult(rs)  # Always clear!

dbDisconnect(con)

dbExecute() — Esecuzione di SQL diverso da SELECT

dbExecute(con, sql) esegue istruzioni SQL che modificano i dati (INSERT, UPDATE, DELETE, CREATE, DROP) e restituisce il numero di righe interessate. Lo utilizzi per le operazioni DDL e DML che non restituiscono righe.

library(DBI)
library(RSQLite)

con <- dbConnect(RSQLite::SQLite(), ':memory:')
dbWriteTable(con, 'students',
  data.frame(id=1:4, name=c('Alice','Bob','Carol','Dave'), score=c(85,92,78,88)))

# Update a record
rows_affected <- dbExecute(con,
  'UPDATE students SET score = 95 WHERE name = \'Alice\''
)
cat('Rows updated:', rows_affected, '\n')

# Verify
print(dbGetQuery(con, 'SELECT * FROM students WHERE name = \'Alice\''))
dbDisconnect(con)

dbExistsTable() e dbRemoveTable()

dbExistsTable(con, 'name') verifica se una tabella esiste (restituisce TRUE/FALSE). dbRemoveTable(con, 'name') elimina una tabella. Sono utili per configurare e terminare in modo sicuro gli script che possono essere eseguiti più volte.

library(DBI)
library(RSQLite)

con <- dbConnect(RSQLite::SQLite(), ':memory:')

cat('Before: exists?', dbExistsTable(con, 'results'), '\n')

dbWriteTable(con, 'results', data.frame(x=1:3, y=4:6))
cat('After write: exists?', dbExistsTable(con, 'results'), '\n')

dbRemoveTable(con, 'results')
cat('After remove: exists?', dbExistsTable(con, 'results'), '\n')

dbDisconnect(con)

dbDisconnect() — Chiusura delle connessioni

Chiuda sempre le connessioni al database con dbDisconnect(con) al termine del lavoro. Le connessioni aperte consumano memoria e descrittori di file. Utilizzi on.exit(dbDisconnect(con)) all'interno delle funzioni per garantire la pulizia anche in caso di errore.

library(DBI)
library(RSQLite)

# Safe connection pattern using on.exit()
safe_query <- function(query) {
  con <- dbConnect(RSQLite::SQLite(), ':memory:')
  on.exit(dbDisconnect(con))  # Runs even if error!
  dbWriteTable(con, 'data', data.frame(x=1:5, y=6:10))
  dbGetQuery(con, query)
}

result <- safe_query('SELECT * FROM data WHERE x > 3')
print(result)

Caricamento di mtcars in SQLite

Un esempio pratico: caricare il dataset integrato mtcars in SQLite e poi eseguire query SQL su di esso. Questo dimostra il flusso di lavoro completo di DBI e consente di esercitarsi con SQL da R.

library(DBI)
library(RSQLite)

con <- dbConnect(RSQLite::SQLite(), ':memory:')

# Load mtcars into the database
dbWriteTable(con, 'cars', mtcars, overwrite=TRUE)

# SQL query
result <- dbGetQuery(con, '
  SELECT cyl,
         COUNT(*) AS n,
         ROUND(AVG(mpg), 1) AS avg_mpg,
         ROUND(AVG(hp), 0) AS avg_hp
  FROM cars
  GROUP BY cyl
  ORDER BY cyl
')

print(result)
dbDisconnect(con)

Riepilogo del flusso di lavoro DBI

Il flusso di lavoro DBI segue uno schema coerente indipendentemente dal backend del database: Connessione → Caricamento/Query → Disconnessione. Lo stesso codice funziona con SQLite, PostgreSQL, MySQL e altri database, sostituendo il driver in dbConnect().

library(DBI)
library(RSQLite)

# Complete DBI workflow
con <- dbConnect(RSQLite::SQLite(), ':memory:')

# 1. Create table
dbWriteTable(con, 'orders',
  data.frame(order_id=1:4, customer=c('Alice','Bob','Alice','Carol'),
             amount=c(100,200,150,80)))

# 2. Query
totals <- dbGetQuery(con,
  'SELECT customer, COUNT(*) orders, SUM(amount) total
   FROM orders GROUP BY customer ORDER BY total DESC')
print(totals)

# 3. Clean up
dbDisconnect(con)

Verifica rapida

Qual è la differenza tra dbGetQuery() e dbExecute() in DBI?

Riepilogo: DBI e RSQLite

Punti chiave per DBI e RSQLite:

  • dbConnect(RSQLite::SQLite(), ':memory:') — crea una connessione SQLite in memoria
  • dbWriteTable(con, 'name', df) — carica un data frame in una tabella
  • dbListTables(con) — elenca le tabelle; dbListFields(con, 'tbl') — elenca le colonne
  • dbGetQuery(con, sql) — esegue SELECT e restituisce un data frame
  • dbFetch(rs, n=1000) — recupero a blocchi per grandi risultati
  • dbExecute(con, sql) — esegue INSERT/UPDATE/DELETE/DDL
  • dbDisconnect(con) — chiude sempre la connessione; utilizzi on.exit() nelle funzioni
  • DBI funziona con PostgreSQL, MySQL, BigQuery ecc. sostituendo il driver
library(DBI)
library(RSQLite)

con <- dbConnect(RSQLite::SQLite(), ':memory:')
on.exit(dbDisconnect(con))

dbWriteTable(con, 'scores',
  data.frame(student=c('Alice','Bob','Carol'), score=c(85,92,78)))

dbExecute(con, 'UPDATE scores SET score = score + 5 WHERE score < 80')

dbGetQuery(con, 'SELECT * FROM scores ORDER BY score DESC')

Domande Frequenti

La lezione «Basi di DBI e RSQLite» è gratuita?

Sì — il testo completo di «Basi di DBI e RSQLite» è gratuito qui sul web. Per esercitarvi in modo interattivo (un editor di codice integrato e un tutor IA 24/7) e sbloccare il resto del corso R Academy, passa a CoddyKit PRO. Il corso R Academy include 4 lezioni in totale.

Cosa imparerò in «Basi di DBI e RSQLite»?

Si colleghi ai database SQLite, esegua query e recuperi risultati con DBI. Eserciti R Academy con codice pratico che esegui direttamente nel browser, e un tutor IA 24/7 risponde alle tue domande mentre lavori sulla lezione.

Ho bisogno di esperienza per iniziare R Academy?

Non è richiesta alcuna esperienza precedente. R Academy su CoddyKit è strutturato per principianti e studenti avanzati, quindi puoi iniziare da qui o dall'inizio e procedere al tuo ritmo. Questa è la lezione 1 di 4.

Quanto tempo richiede la lezione «Basi di DBI e RSQLite»?

La maggior parte delle lezioni CoddyKit richiede circa 5–10 minuti. Ogni lezione è breve e interattiva, quindi fai progressi costanti e riprendi esattamente da dove hai lasciato su web e app.

Posso scrivere ed eseguire codice in questa lezione R Academy?

Sì. Ogni lezione R Academy include un editor di codice integrato, quindi scrivi ed esegui codice reale direttamente nel tuo browser e ricevi feedback istantaneo dall'IA — nessuna configurazione locale necessaria.

Tutte le lezioni di questo corso

  1. Basi di DBI e RSQLite
  2. Connessione a PostgreSQL e MySQL
  3. dbplyr: SQL tramite la sintassi dplyr
  4. Query parametrizzate e transazioni
← Torna a R Academy